By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,874 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,874 IT Pros & Developers. It's quick & easy.

Help Using DTS to import multiple tables into Access Database

P: 38
I need some help.

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
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. GO
  4. ALTER  PROCEDURE [dbo].[FillDatabase]
  5. AS
  6.     SET NOCOUNT ON
  7.         DELETE FROM dbo.Cards
  8.         DELETE FROM dbo.Meter
  9.         DELETE FROM dbo.Products
  10.         DELETE FROM dbo.Settlement
  11.         DELETE FROM dbo.Sites
  12.         DELETE FROM dbo.Stock
  13.         DELETE FROM dbo.Tanks
  14.         DELETE FROM dbo.Transactions
  15.         DELETE FROM dbo.Users
  16.  
  17.         DECLARE @Result BIT
  18.  
  19.         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"'
  20.         IF ( @Result = 1 )
  21.             BEGIN
  22.                 RAISERROR( 'Error translating data', 18, 1 )
  23.  
  24.                 RETURN
  25.             END
  26.     SET NOCOUNT OFF
  27.  
Cheers

Terry
Mar 28 '07 #1
Share this Question
Share on Google+
1 Reply


iburyak
Expert 100+
P: 1,017
You said:

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
You can link Access to server tables using ODBC this way you dont have to import anything just compare data and insert\update\delete differences.
Mar 28 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.