a - b = c - 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 2005 Forums

Transact-SQL (2005)

a - b = c

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2010-02-22 : 18:20:37

I have Table1:

id position status
1 teacher active
2 teacher disabled
2 support active

with id and position as primary keys.

I'd like to insert a set of data into this table. My problem is I cannot insert a duplicate record because of the primary keys.

I have 2 sets of records that I get via select statements. One contains all the records and the other just the existing records in Table1. My set of all records look like this:

id position status
1 teacher active
1 support inactive
1 student disabled
2 teacher disabled
2 support active
2 student active

How do I do this via SQL?

all records minus existing records equals records that don't exist in the table (records in red)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-22 : 19:58:55

[CODE]insert into <TargetTable>
select <columns>
from <SourceTable>
left outer join
<TargetTable>
on
<sourceTable.<PrimaryKey> = <TargetTable>.<PrimaryKey>
where
<TargetTable>.<PrimaryKey> is NULL[/CODE]
BTW...
1) The same thing can be done using a NOT EXISTS predicate
2) The case where the primary key does exist is potentially an UPDATE condition depending on your business rules
3) In SQL 2008 the MERGE statement can handle both the INSERT and UPDATE in a single query

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources