Insert query - 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)

Insert query

Author

Topic

donnapep
Starting Member

15 Posts

Posted - 2008-01-08 : 11:27:20

Hello,

I need to write a query that will insert records into a table if they do not already exist. Here are the relevant columns of that table:

UpdateJobId, PK
UpdateId, FK
DisplayApplianceId, FK
...

UpdateJobId is really an artificial key to make joining tables easier. UpdateId and DisplayApplianceId, taken together, are enough to uniquely identify the record, and this is the only information that I know when I am updating the table. I do not know the UpdateJobId.

So, having said that, how do I go about inserting only those records that have a particular UpdateId and DisplayApplianceId that do not already exist in the table? If I only needed to check against the value of one field, I could use NOT IN with a subquery, but I need to check against the value of multiple fields, which I am not sure how to do.

Thx.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 11:32:52

If inserting values directly:-

IF NOT EXISTS (SELECT * FROM YourTable WHERE UpdateId=@UpdateId AND
DisplayApplianceId=@DisplayApplianceId)
BEGIN
INSERT INTO YourTable <cols> VALUES <colvalues>

If inserting from another table:-

INSERT INTO YourTable <cols>
SELECT <values>
FROM OtherTables o
LEFT OUTER JOIN YourTable y
ON y.UpdateId=o.UpdateId
AND y.DisplayApplianceId=o.DisplayApplianceId
WHERE y.UpdateId IS NULL

donnapep
Starting Member

15 Posts

Posted - 2008-01-08 : 11:35:50

Thanks.

So there is no way to process all records at one time? I will have to process each individual record in order to extract the UpdateId and DisplayApplianceId values to test against?

kumarich1
Yak Posting Veteran

99 Posts

Posted - 2008-01-08 : 11:41:22

quote:
Originally posted by donnapep

Thanks.

So there is no way to process all records at one time? I will have to process each individual record in order to extract the UpdateId and DisplayApplianceId values to test against?

Try this
First identify set of new data which does not exist in database already and insert this into temp table and later insert temp table data into destination table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 11:48:01

Are you taking values from a table? then use script i gave earlier

INSERT INTO YourTable <cols>
SELECT <values>
FROM OtherTable o
LEFT OUTER JOIN YourTable y
ON y.UpdateId=o.UpdateId
AND y.DisplayApplianceId=o.DisplayApplianceId
WHERE y.UpdateId IS NULL

donnapep
Starting Member

15 Posts

Posted - 2008-01-08 : 12:03:50

The derived table approach seems to work.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources