updating a table - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

updating a table

Author

Topic

cnu29
Starting Member

1 Post

Posted - 2008-09-03 : 15:23:58

hi can some one help me out

I have a table with 100 thousand records, my client has sent a flat file which has updates for the first table and my task is to put the records from the update text file in to the table and also to delete the older records which are been replaced by the update file and also to send the deleted records to another table called history sca some one tell me the procedure to this or is there any script to performthis action

SRINI

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-03 : 16:14:02

is there a pk in each table you can use to relate rows?

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 01:31:13

quote:
Originally posted by cnu29

hi can some one help me out

I have a table with 100 thousand records, my client has sent a flat file which has updates for the first table and my task is to put the records from the update text file in to the table and also to delete the older records which are been replaced by the update file and also to send the deleted records to another table called history sca some one tell me the procedure to this or is there any script to performthis action

SRINI

First thing you should do is to identify set of column values in your file which uniquely identifies a row (called primary key column PK) as suggested by jezermine. once you get this. then you just need to use of below logic. first transfer contents of file onto a staging table. then you will have three conditions

1. records existing in staging table but not in your actual final table. these are new records which need to be inserted to your table. so use


INSERT INTO finaltable
SELECT s.*
FROM StagingTable s
LEFT JOIN finaltable f
ON f.PK=s.PK
WHERE f.PK IS NULL

2.records existing in both tables. there are records that have been modified. such case do an Update

UPDATE f
SET f.field1=s.field1,
f.field2=s.field2,...
FROM FinalTable f
INNER JOIN StagingTable s
ON s.PK=f.PK

3. records existing in final table but not in staging. these are records that were removed. so delete them from final table.

DELETE f
FROM FinalTable f
LEFT JOIN StagingTable s
ON f.PK=s.PK
WHERE s.PK IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources