Composite PK issue with IN statement

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 2008 Forums

Transact-SQL (2008)

Composite PK issue with IN statement

Author

Topic

shz
Starting Member

7 Posts

Posted - 2010-12-01 : 14:09:21

Hello,

I am trying to update table t1 using table t2, that is, if a row exists in t2 but not in t1, insert that row into t1. For a single-column PK, it is easy to do it with the following query:

INSERT INTO t1 (pk, col1)
SELECT pk, col1 FROM t2 WHERE pk not in (SELECT pk FROM t1)

Now, the question is: what if the PK is a composite one? Something like:

INSERT INTO t1 (pk1, pk2, col1)
SELECT pk1, pk2, col1 FROM t2 WHERE (pk1, pk2) not in (SELECT pk1, pk2 FROM t1)

Any idea is highly appreciated. Thanks,

shz

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-01 : 14:11:32

[code]INSERT INTO t1 (pk1, pk2, col1)
SELECT pk1, pk2, col1 FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE pk1=t2.pk1 AND pk2 = t2.pk2)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-01 : 14:13:02

also


INSERT INTO t1 (pk1, pk2, col1) 
SELECT t2.pk1, t2.pk2, t2.col1 FROM t2
LEFT JOIN t1
ON t1.pk1 = t2.pk1
AND t1.pk2 = t2.pk2
WHERE t1.pk1 IS NULL
AND t1.pk2 IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-12-01 : 14:29:11

INSERT INTO T1 (pk1, pk2, col1)
SELECT pk1, pk2, col1
FROM (SELECT pk1, pk2, col1
FROM T2
EXCEPT
SELECT pk1, pk2, col1
FROM T1)
AS X(pk1, pk2, col1);

I like the set operators because they are easier to generalize and are at a higher level of abstraction.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

shz
Starting Member

7 Posts

Posted - 2010-12-01 : 15:26:19

Thanks VisaKH and Jcelko. They work great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources