April 16, 2008 at 5:09 pm
I have a stored procedure that inserts records into a table with a primary key fields (clustered). The records I'm trying to insert are new records into this table, but I keep on running into a primary key violation. I'm not sure if there is some way to refresh the primary keys or table. I'm just using a basic command to insert the records into the table.
e.g. of code:
INSERT INTO Table1
SELECT * FROM Table2
Table1 has clustered primary keys.
Field1(int) and Field2(char)
I'm not sure why these new records are not getting inserted into the table. Can someone explain this to me?
April 16, 2008 at 5:15 pm
Primary key violation means that you are trying to insert duplicates of existing values already in table1 (or that table2 has multiple instances of the same field1/field2 combination).
Are you SURE that the field order in the two tables is compatible? If the fields are out of line - there's no telling what field in table2 is beinf inserted into a given column in table1... The extra typing would be well worth it just to be sure.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 17, 2008 at 7:35 am
instead of just doing a select all into the table:
INSERT INTO Table1
SELECT * FROM Table2
You might want to try a not exist in a where clause:
INSERT INTO Table1
SELECT *
FROM Table2 b
WHERE NOT EXISTS (SELECT NULL FROM Table1
WHERE Field1 = b.Field1 AND Field2 = b.Field2)
This will make sure that you are not loading a record that already exists. This, however, will not take care of a situation where you have duplicate new records based on the Field1 and Field2 in Table2. If you have duplicates records in Table2 and every field in each record is the same, then you could use the DISTINCT clause in the SELECT statement to clean it up. If just one field is different between the two records, then the DISTINCT clause will not work. Then you will need to decide how you want to resolve it.
Another thing you might want to look at is doing an update of Table1 based on any matches on Field1 and Field2 in Table2 for all the other fields in the table before doing the INSERT:
UPDATE a
SET Field3 = b.Field3,
Field4 = b.Field4,
etc....
Field15 = b.Field15
FROM Table1 a INNER JOIN Table2 b ON a.Field1 = b.Field1 AND a.Field2 = b.Field2
Dave Novak
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply