HELP!!!! Moving data from one database..table to another

[smileydba]

Ok, My issue is that, I want to move data from the old database..table to my new database. the problem Im running into is that, I need to create a script that compares the two tables from the two databases, and based on that, if there is a match, dont do anything, but if there isent a match, then add that record. Below is the code I already done, but it dosent really want to do much.

declare @IDCnt int (30)
declare @ID int (30)

Select *
From test1..item
Group By itemlookupcode

Set @IDcnt = 'Select Count(itemlookupcode) From test1..item'
Set @ID = 'Select Min(itemlookupcode) From test1..item'

While @IDcnt <> 0
BEGIN
Insert Into test2..item
Select TOP itemlookupcode from test1..item where itemlookupcode = @ID

update From test1..item where itemlookupcode = @ID
Set @IDcnt = Select Count(*) From test1..item END




So basically, I want to move data from the old database..table to the new database..table when there is new data.
If you have a idea, please tell me what I can improve on. Im a newbie on the TSQL. Thank you so much.,


Rob W.

 

[smileydba]

And if someone mentions BCP, please enlighten me on what I need to add to make it work.

Rob W.

 

[amrita418]

If you want to insert all rows missing in the new table from the old table then use something like

insert into NewTable
select a.* 
from Olddb..OldTable a
left outer join NewTable b
on (a.id = b.id)
where b.id is null

PS: Similar thread already answered, check it out.
thread183-1081974

Regards,
AA