insert new rows based on PK with 3 fields

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)

insert new rows based on PK with 3 fields

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-02-10 : 10:30:49

Greetings

For my table x(field1, field2, field3) I have a clustered PK absed on three columns (Columns are not fields as Dr Celko says). When inserting new rows how do I check for to see if a record with that PK exist in order to a NOT IN () or some other check methods.

I am looking into MERGE as well but can;t wrap my head around it yet.

Thanks

If you don't have the passion to help people, you have no passion

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-10 : 10:38:01

if you declared these Columns (not fields) as Primary key then you dont need to check them explicitly as Primary keys are always UNIQUE. and even if you try to insert same record again you will get an error ... (as Dr Celko said )

Cheers
MIK

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-02-10 : 10:41:02

if you want to do the insert excluding any rows that already exist without getting the error then:
One (old style but good) way is to include a left outer join to tableX in the select statement you are using as the source of your insert. Join on all three of you PK columns then add a WHERE criteria for "field1 is null".

Be One with the Optimizer
TG

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-02-10 : 18:55:49

nice TG

Thanks very much!!!

If you don't have the passion to help people, you have no passion

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-11 : 04:45:28

C'mon man...use MERGE! It's not as hard as you would think:

MERGE myTable AS T
USING (@Col1, @Col2, @Col3, @Col4) AS S (Col1, Col2, Col3, Col4)
	ON (T.Col1 = S.Col1 
		AND T.Col2 = S.Col2 
		AND T.Col3 = S.Col3) 
WHEN NOT MATCHED 
    THEN INSERT(Col1, Col2, Col3) 
    VALUES(S.Col1, S.Col2, s.Col3)
WHEN MATCHED 
    THEN UPDATE SET T.Col4 = S.Col4

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-02-11 : 14:02:41

yes sir!!! looking into MERGE as we speak. just migrated to sql 2008 :)

thanks

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources