Im using a dtsrun command to import 9 tables into an Access database, most of the tables have about 1000 rows, but one has 20000+ rows and grows everyday. Im Using Access 2003 and SQL Server 2000. on a WinXP Machine
When the database is opened a stored procedure drops all the tables and then imports the csv files using dtsrun. The database is only used for reporting and the data is entered into the csv files using 3rd party systems,
What i want to know is if there is a way to only import new rows, or rows that have been changed into the database, so i dont have to drop the tables everytime the database is opened.
I have seen one way to do this is to use temporary tables and import the data to the temp tables and the compare teh temp tables with the actual tables, and the insert and update the new rows, but this solution still has to import all the rows before hand and will take longer then it takes now
Here is my stored procedure:
Expand|Select|Wrap|Line Numbers
- set ANSI_NULLS ON
- set QUOTED_IDENTIFIER ON
- GO
- ALTER PROCEDURE [dbo].[FillDatabase]
- AS
- SET NOCOUNT ON
- DELETE FROM dbo.Cards
- DELETE FROM dbo.Meter
- DELETE FROM dbo.Products
- DELETE FROM dbo.Settlement
- DELETE FROM dbo.Sites
- DELETE FROM dbo.Stock
- DELETE FROM dbo.Tanks
- DELETE FROM dbo.Transactions
- DELETE FROM dbo.Users
- DECLARE @Result BIT
- EXECUTE @Result = master..xp_cmdshell 'DTSRun /N "PropogateFuelDatabase" /F "C:\Program Files\TransHost\Reports\Reports Database\PropogateFuelDatabase.dts" /A Instance:8="frontpc\fieldwicks" /W "-1"'
- IF ( @Result = 1 )
- BEGIN
- RAISERROR( 'Error translating data', 18, 1 )
- RETURN
- END
- SET NOCOUNT OFF
Terry