Partitions & Identity Columns
Question
When I switch the data to the main table partition, it is ignoring the Identity specification column on and duplicating the values in the Identity column.
I did a search on Books on Line and all I could see is Performing partition switching can introduce duplicate values in IDENTITY columns of the target table, and gaps in the values of IDENTITY columns in the source table. Use DBCC_CHECKIDENT to check the identity values of your tables and correct the values if you want. But it doesn't tell me how to use DBCC_CHECKIDENT.
Answer
When you are inserting data into your empty table, reseed that table before you start with the max+1 value from the main table, a quick query should work that out.
Insert your data and then push it into your main table, no issues.
Scenario
My table partition is based on the date. I've created my Temp tables dynamically based on the date. And before inserting the records into Temp table I've retrieved the (max + 1) value of the Identity column from the main table and reseeded the Identity of the Temp table with DBCC CHECKIDENT
Thanks
Sutha
When I switch the data to the main table partition, it is ignoring the Identity specification column on and duplicating the values in the Identity column.
I did a search on Books on Line and all I could see is Performing partition switching can introduce duplicate values in IDENTITY columns of the target table, and gaps in the values of IDENTITY columns in the source table. Use DBCC_CHECKIDENT to check the identity values of your tables and correct the values if you want. But it doesn't tell me how to use DBCC_CHECKIDENT.
Answer
When you are inserting data into your empty table, reseed that table before you start with the max+1 value from the main table, a quick query should work that out.
Insert your data and then push it into your main table, no issues.
Scenario
My table partition is based on the date. I've created my Temp tables dynamically based on the date. And before inserting the records into Temp table I've retrieved the (max + 1) value of the Identity column from the main table and reseeded the Identity of the Temp table with DBCC CHECKIDENT
Thanks
Sutha
0 Comments:
Post a Comment
<< Home