CODEWITHSUNDEEP
sqlsql-serverdatabaseprimary-keyinsert-update
Samter
Samter

Reputation: 131

Inserting rows and updating rows on tables that may or may not have primary keys

I am trying to update a table so that rows that are missing are added and rows that are not up to date are updated using information from another table on a different database as a reference.

However, some tables have primary keys and some do not. If there is a primary key the insert command will not run and if there is not a primary key, rows will duplicate.

Is there a way to have the insert command skip over primary key values that are already there?

I'm using sql server management studio 2005 and here is the code I have so far for a table with a primary key (PKcolumn):

INSERT [testDB].[dbo].[table1]
SELECT * FROM [sourceDB].[dbo].[table1]

UPDATE test
SET 
 test.[PKcolumn] = source.[PKcolumn]
,test.[column2] = source.[column2]
,test.[column3] = source.[column3] 
FROM 
[sourceDB].[dbo].[sourceDB] AS source
INNER JOIN 
[testDB].[dbo].[PKcolumn] AS test
ON source.[PKcolumn] = test.[PKcolumn]

Update works perfectly but Insert will not run at all if there is even one duplicate.

Any suggestions on how to make this code work?

Also, any tips for doing the same thing on a table without a primary key?

Upvotes: 1

Views: 204

Answers (1)

Anthony Grist
Anthony Grist

Reputation: 38345

You'll need to exclude rows that are already present in the table in your INSERT query, using a LEFT OUTER JOIN:

INSERT [testDB].[dbo].[table1]
SELECT * FROM [sourceDB].[dbo].[table1]
LEFT OUTER JOIN [testDB].[dbo].[table1] ON [sourceDB].[dbo].[table1].[PKcolumn] = [testDB].[dbo].[table1].[PKcolumn]
WHERE [testDB].[dbo].[table1].[PKcolumn] IS NULL

For a table without a primary key, I suppose you'd need to join on ALL columns to avoid duplicates.

Upvotes: 2

Related Questions