insert/update only new records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert/update only new records

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2010-03-19 : 03:39:43

I have a issue regarding datawarehouse insert/update records when changes are made to existing records or completely new records.

Table1
id int identity(1,1)
saveDate datetime default(getDate())
col1 varchar(100)

Table2
id int (pk)
saveDate datetime
col1 varchar(100)

New record: When new records are inserted into Table1 it gets an id and saveDate automatically.

Update record: When changes are made to existing records the saveDate is changed.

Every night a scheduled job should run (SSIS) that only insert new and updated records in a different database looking exactly as the previous. I other words: I do not want to truncate Table2 and transfer all records only new or updated.

How do I manage this using SSIS 2005?

Today the job takes approx 5 hours truncating and inserts all records which is a waste of time and resources.

Thank in advance.
//Maevr

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-19 : 09:30:43

First create a transfer data task that transfer all new/changed data to some kind of temporary table on the DWH-server:
SELECT * FROM table1 WHERE savedate > GETDATE() -1

Then on the dwh-server (the easy way):
DELETE table2 WHERE ID IN (SELECT ID FROM mytmpTable1)
INSERT INTO table2 SELECT * FROM mytmpTable1

The slightly harder way:
INSERT INTO table2 SELECT * FROM mytmpTable1 WHERE ID NOT IN (SELECT ID FROM table2)
UPDATE tbl2 SET Col1 = tbl1.Col1, ... FROM table2 inner join mytmpTable1 tbl1 on tbl2ID = tbl1.ID

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 10:15:25

I think:


SELECT * FROM MyRemoteTable WHERE savedate > (SELECT MAX(savedate) FROM MyLocalTable)

is preferable because if the job did not run yesterday (server down for two days being fixed

) then it will "resume" from whatever was the most recent record transferred "last time"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 11:24:06

exactly. As Kristen suggested grab the max value from destination table each time and populate a variable created in SSIS. then for data extraction task (DFT) look for ones in Table1 with date >variablevalue. Then do a lookup with destination on PK (id=id). for success case do a update Table2 using OLEDB command and for failure use redirect row option and add a OLEDB destination to do insert to Table2
Also please note that creating a default constraint will work only for inserts so update you either need to pass date value directly or write an after trigger to get new value of date on each update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 11:41:47

Errrmmmm ... another point to note: This is not going to delete rows from TABLE1 that are no longer present in the remote database.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 11:44:00

quote:
Originally posted by Kristen

Errrmmmm ... another point to note: This is not going to delete rows from TABLE1 that are no longer present in the remote database.

Yeah...but it seems OP's not interested in that as I cant see delete specified anywhere

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources