insert records with compound keys (migration)

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 records with compound keys (migration)

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2006-12-06 : 18:31:34

Hi,
I have a little bit of a dilema. I'm sure there is a solution, but I'm just not seeing it. I have two databases with the exact same table. I need to transfer the data from the first database to the second. Unfortunately, there are some common records shared between the two tables. I want to only insert those records that are missing.

To help illustrate the issue, I created the below script (this is data between different tables but it is the same concept). I want to insert records id1=1,id2=2 and id1=2,id2=1. Since it is a compound key, I can't just do a NOT IN (...). I need to treat the two id fields together.


CREATE TABLE [dbo].[Table1] (
	[id1] [int] NOT NULL ,
	[id2] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table2] (
	[id1] [int] NOT NULL ,
	[id2] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] ADD 
	CONSTRAINT [PK_Table1] PRIMARY KEY  CLUSTERED 
	(
		[id1],
		[id2]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Table2] ADD 
	CONSTRAINT [PK_Table2] PRIMARY KEY  CLUSTERED 
	(
		[id1],
		[id2]
	)  ON [PRIMARY] 
GO

INSERT INTO Table1 SELECT 1,1
INSERT INTO Table1 SELECT 1,2
INSERT INTO Table1 SELECT 2,1
INSERT INTO Table1 SELECT 2,2

INSERT INTO Table2 SELECT 1,1
INSERT INTO Table2 SELECT 2,2
INSERT INTO Table2 SELECT 3,1

-- this doesn't work, I don't see how to query the records 
-- from table1 that DO NOT exist in table2
insert into table2
select * from table1
where 
	(id1 NOT IN (select id1 from table2)
	AND id2 NOT IN (select id2 from table2))

Nic

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-06 : 20:14:43

Either of these will do it. They should perform the same, but test to see

--using NOT EXISTS
insert into table2
select * from table1
where not exists (select * from table2 where table2.id1 = table1.id1 and table2.id2 = table1.id2)

--using a LEFT OUTER JOIN
insert into table2
select table1.id1, table1.id2
from table1
left outer join table2 on table2.id1 = table1.id1 and table2.id2 = table1.id2
where table2.id1 is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources