Insert with values from existing table

Question

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

Just turn identify insert off and let SQL Server generate its own PK? — Dale K
– Dale K, Commented Jun 27, 2024 at 21:13
@AlwaysLearning I took what OP said to say they are attempting to insert duplicate ids into the new table, and they specifically say they need SQL Server to assign its own ID... rather than the inserted one, because the inserted one is causing duplicates... therefore they shouldn't have identity insert on. — Dale K
– Dale K, Commented Jun 27, 2024 at 21:52
@DaleK the op is already controlling IDENTITY_INSERT in the posted code. The problem is that the source query is trying to insert additional data that would violate the primary key constraint of the target table. — AlwaysLearning
– AlwaysLearning, Commented Jun 27, 2024 at 21:54
@AlwaysLearning yes and they say that want SQL to be able to assign it's own primary key value - hence they need identity insert to be off not on. — Dale K
– Dale K, Commented Jun 27, 2024 at 21:55
Well, it's often said "don't use SELECT *." This is another reason why. Op needs to specify all of the columns, except for the identity column, when selecting/inserting the data. — AlwaysLearning
– AlwaysLearning, Commented Jun 27, 2024 at 21:57

shah Taymur · Accepted Answer · 2024-07-05 05:18:05Z

0

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;

edited Jul 5, 2024 at 5:18

answered Jun 28, 2024 at 11:58

shah Taymur

14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dale K Over a year ago

What is steric?

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

tcoady Over a year ago

Thank you for your comment, but not sure what "steric" means can you explain? Also I am using the actual column names in the query I am running. I renamed to column 1, 2 etc for privacy purposes. If you mean steric = this symbol * then yes I have tried replacing with the actual column names but it still throws the duplicate primary key error message as well as using *.

tcoady Over a year ago

Also when replacing the * with the actual column names it will throw this error "Incorrect syntax near ','." with an indicator on the first column name.

shah Taymur Over a year ago

steric = *, and in column names don't add the primary key column in both statement(insert into and select).

Collectives™ on Stack Overflow

Insert with values from existing table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related