How to Import and Add only New records?

[Griffyn]

Hi all,

I've set up a DTS package that imports data from an ODBC connection into SQL. There's 6 tables who's details can change, so there's a step for each of them that clears the SQL table, then imports the data. Works fine.

Then there's table 7 which is a transaction history file. Only new records are added to the ODBC source, and existing records can never change, so it makes no sense to clear the SQL mirror each time and import everything again - so I set up a unique index on the SQL table so that duplicates would fail. The process still steps through the million or so records, then reports a failure (due to the index), but still imports the new records.

The problem is the package reports a failure. I could set the error count for that step to 9 million or something, but then it won't trap errors I may be interested in.

How should I go about handling this?

Thanks.

 

[MDXer]

use a execute sql taks that calls a stored procedure that is a simple insert into select from statement use a left outer join on the two tables to filter out only new records.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[JayKusch]

If you can ...

Select the max ID, or whatever field you have decided is the unique one in the history table ... preferably an Int,
then use it to select from your other tables.

SO ... here is an example

declare @MaxID Int

SELECT @MaxID = ID FROM MyHistoryTable

INSERT INTO MyHistoryTable

SELECT * FROM MyLiveTable WHERE ID > @@MaxID

Thanks

J. Kusch

 

[MDXer]

The following code snippet is the format I use in the example Column1 and Column2 for the unique record so I use them in the join. The p.IDColumn would be the ID Column in your production table and by filter p.IDColumn is Null you are only returning records from the select portion that don't exist in the Production table.

Insert Into ProdTable
(
Column1,
Column2,
Column3
)
Select
s.Column1,
s.Column2,
s.Column3
From stageTable s
    Left Outer Join prodTable p ON p.Column1 = s.Column1
                                and p.Column2 = s.Column2
where p.IDColumn Is Null

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Griffyn]

Thanks for your replies.

Jaykusch, while my Tran_No field is guaranteed to be unique, the numbers are sequential. This is because my import table contains different transaction 'types', which use different number ranges. Some begin with letters, some don't, etc. so that approach is not suitable for me.

MDXer, the tables are from different sources (one is an ODBC, the other is the SQL server). How do I link tables from different connections?

 

[MDXer]

Import them into a stage table then import them from the stage into your main table. Trucate the Stage table prior to every odbc and sql import from the source systems.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Griffyn]

Surely it would be easier to simply clear the existing table and re-import from scratch? What I'm trying to do is make the process more efficient - so that I'm not having to import all 1.3 million records just to add a couple of thousand to the existing table.