CODEWITHSUNDEEP
sqlsql-serverinsertsql-insertbulkinsert
tcoady
tcoady

Reputation: 13

Insert with values from existing table

I need to move some values based on expiration date, but unfortunately because the tables are not one to one in number of records I receive an error message when my query is run. I need SQL to be able to assign it's own primary key value because I believe that is where the conflict is coming from.

The error message is:

Msg 2627, Level 14, State 1, Line 3 Violation of PRIMARY KEY constraint 'PK_ClerkDogHistory'. Cannot insert duplicate key in object 'dbo.ClerkDogHistory'. The duplicate key value is (10595).

SET IDENTITY_INSERT [Table_records_are_being_moved_TO] ON
BEGIN TRANSACTION;
INSERT INTO [Table_records_are_being_moved_TO] (column1, column2,column3...)
SELECT *
FROM [Table_records_are_being_moved_FROM]
WHERE [expiration_date] between '2013-01-01' and '2013-12-31';

DELETE FROM [Table_records_are_being_moved_FROM]
WHERE [expiration_date] between '2013-01-01' and '2013-12-31';

COMMIT;
SET IDENTITY_INSERT [Table_records_are_being_moved_TO] OFF

Upvotes: -1

Views: 87

Answers (1)

shah Taymur
shah Taymur

Reputation: 11

Please use column names instead of *, also remove IDENTITY_INSERT ON, OFF clause. and don't put the Identity column in your query. Your final query something look like this.

BEGIN TRANSACTION;
INSERT INTO [Table_records_are_being_moved_TO] (column1, column2,column3...)

SELECT column1, column2,column3... FROM [Table_records_are_being_moved_FROM] WHERE [expiration_date] between '2013-01-01' and '2013-12-31';

DELETE FROM [Table_records_are_being_moved_FROM] WHERE [expiration_date] between '2013-01-01' and '2013-12-31';

COMMIT;

Upvotes: 0

Related Questions