[RESOLVED] sql server 2000 - help with insert from one table to another

[wild_bill]

Maybe something like this

set nocount on

declare @zip table (zip char(5), city varchar(100), zip_ik int)
insert into @zip(zip,city,zip_ik) values('49862','Munising',0)
insert into @zip(zip,city,zip_ik) values('49862','Christmas',1)
insert into @zip(zip,city,zip_ik) values('49862','Forest Lake',2)

declare @ldc_zip table (ldc_zip_ik int, ldc_ik int, zip char(5), zip_ik int)

insert into @ldc_zip (ldc_zip_ik,ldc_ik, zip, zip_ik) Values(0,0,'49862',null)

--update existing ldc_zip
update ldc
set zip_ik = zip.zip_ik
from @ldc_zip ldc
inner join @zip zip on ldc.zip = zip.zip

--insert missing cities
insert into @ldc_zip (ldc_zip_ik,ldc_ik, zip, zip_ik)
select	ldc.ldc_zip_ik,
		ldc.ldc_ik,
		ldc.zip,
		zip.zip_ik
from @ldc_zip ldc
inner join @zip zip on ldc.zip = zip.zip
where zip.zip_ik not in (select zip_ik from @ldc_zip)

select *
from @ldc_zip

[MMock]

Maybe something like this...

Thanks, I may use some of this, but some of it I can't. I don't want to rebuild/modify the zip table except to add the PK (that's done)...but probably the insert of the missing cities I can use.