JBM2015
asked on
Modifying a SQL 2008R2 table's Primary Key, table has existing data.
Original table design has 4 columns:
FCode (PK, nvarchar(50), not null)
ID (PK, uniqueidentifier, not null)
Allow (bit, not null)
ICode (nvarchar(50), not null)
The client has asked for a modification where we now need to use FCode and ICode and the primary key and not longer need the ID uniqueidentifier as they want to track this at a higher level than originally thought. The problem is, I'm assuming this table may have records already in it that will violate this new primary key.
Is there a way to re-insert as much data as possible from the original table, the data that doesn't exist the new primary key. I'm hoping to save as much existing data as I possibly can.
Thanks,
Jon
FCode (PK, nvarchar(50), not null)
ID (PK, uniqueidentifier, not null)
Allow (bit, not null)
ICode (nvarchar(50), not null)
The client has asked for a modification where we now need to use FCode and ICode and the primary key and not longer need the ID uniqueidentifier as they want to track this at a higher level than originally thought. The problem is, I'm assuming this table may have records already in it that will violate this new primary key.
Is there a way to re-insert as much data as possible from the original table, the data that doesn't exist the new primary key. I'm hoping to save as much existing data as I possibly can.
Thanks,
Jon
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Jim.
The thought on duplicate records (according to the new primary key) would be to use the ICode column to join to the IMaster table based on ICode.Code = IMaster.Code (many rows) and then figure out which of these rows has the highest version number and use that record to know which record to keep and then any other rows for previous versions can be deleted.
The thought on duplicate records (according to the new primary key) would be to use the ICode column to join to the IMaster table based on ICode.Code = IMaster.Code (many rows) and then figure out which of these rows has the highest version number and use that record to know which record to keep and then any other rows for previous versions can be deleted.
not sure what "figure out ... highest version number" requires, but you could do something like this using ROW_NUMBER()
and note that the order by uses DESC
with CTE as (
select ID, FCode, ICode
, row_number() over(partition by FCode, ICode order by ID DESC) as rn
from your_table
)
delete from CTE
where rn > 1;
then alter the PK
and note that the order by uses DESC
with CTE as (
select ID, FCode, ICode
, row_number() over(partition by FCode, ICode order by ID DESC) as rn
from your_table
)
delete from CTE
where rn > 1;
then alter the PK
ASKER
Thanks Paul,
The ICode field in the table we're discussing here would be used to link to the IMaster table and then within the IMaster table that contain many records per ICode, I would need to find the record in IMaster table that matches this ICode and has the highest value in a Version column within the IMaster table and then use that records identity to know which record to keep in the main table we're discussing here as that identity value is the ID field in the table that has the primary key change and the data cleanup needed.
The ICode field in the table we're discussing here would be used to link to the IMaster table and then within the IMaster table that contain many records per ICode, I would need to find the record in IMaster table that matches this ICode and has the highest value in a Version column within the IMaster table and then use that records identity to know which record to keep in the main table we're discussing here as that identity value is the ID field in the table that has the primary key change and the data cleanup needed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just a "by the way"
you didn't provide the name of the table in the question, so we have to use something like "your_table"
always makes it easier for all involved if you provide the table name(s)
you didn't provide the name of the table in the question, so we have to use something like "your_table"
always makes it easier for all involved if you provide the table name(s)
Thanks for the split. Good luck with your project. -Jim
btw if you need to build on Paul's comment on deleting rows check out my article SQL Server Delete Duplicate Rows Solutions
btw if you need to build on Paul's comment on deleting rows check out my article SQL Server Delete Duplicate Rows Solutions
Explain what you intend do to with the offending row(s). UPDATE with unique values, throw into a temp table, delete, sell on eBay, ...