insert incresement records - 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 2000 Forums

Transact-SQL (2000)

insert incresement records

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2005-04-11 : 16:32:57

How to code to insert only incresement records from table A to table B?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 16:40:12

What are incresement records?

Do you mean keeping table B in sync with table A by only processing Inserted, Updated, or Deleted records from table A?

Be One with the Optimizer
TG

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2005-04-13 : 10:18:37

Table A in one server which users input data. Table B in another server which will import data from Table A every day. I need to code to insert only new data every day from A to B.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-13 : 10:55:20

Have you looked into Replication? That's sort of what it's for.

If you want to do it manually, it depends on the structure of the table(s). Let's look at an ideal case where they have identical structures including a surrogate unique key (RowID) and a DateModified datetime column.

--Get the tables on the same server
DTS TableA to TableB server.

--Inserts:
insert TableB (<collist>)
Select <collist>
from TableA a
left join TableB b ON a.rowid = b.rowid
where b.rowid is NULL

--updates
Update b set b.<col> = a.<col>
from TableB b
JOIN TableA a ON a.rowid = b.rowid
where a.DateModified > b.DateModified

--deletes
delete b
from TableB B
left join TableA a ON a.rowid = b.rowid
where a.rowid is NULL

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources