How do I copy a DISTINCT set of column data into a new table and write back to the first table the I-CodePudding

I need to copy all unique values out of a table column into another table and then update a value in the original table to point at the new row. Below is an incredibly simplified example with dummy data and non-relevant columns removed.

From:

Table1

ID	Item_Name
1	Item A
2	Item B
3	Item A
4	Item C

To:

Table1

ID	Item_Name_ID
1	1
2	2
3	1
4	3

Table 2 (New)

Item_Name_ID	Item_Name
1	Item A
2	Item B
3	Item C

I know how to copy the unique data out of Table1 and insert into Table2, but what is the best way to get it to write back the Table2 automatically created Item_Name_ID value into the matching new Item_Name_ID column in Table1 - to link the tables.

Thanks

CodePudding user response：

You can use a script like this:

No need to assign the IDs, just let the IDENTITY column create them

CREATE TABLE table2 (
  Item_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  Item_Name VARCHAR(6) NOT NULL
);

INSERT INTO table2 (Item_Name)
SELECT DISTINCT Item_Name
FROM table1
WHERE Item_Name IS NOT NULL;

-- add FK column
ALTER TABLE table1 ADD Item_ID int NULL;

UPDATE t2
SET Item_ID = t1.Item_ID
FROM table2 t2
JOIN table1 t1 ON t1.Item_Name = t2.Item_Name;

-- drop old column
ALTER TABLE table1
  DROP Item_Name;

-- only if Item_Name has no nulls
ALTER TABLE table1
  ADD Item_ID int NOT NULL;

-- always have an index on a foreign key as the leading column
CREATE INDEX NONCLUSTERED IX_Item_ID ON table1 (Item_ID) INCLUDE (OtherColumnsHere);
  
-- add FK constraint
ALTER TABLE table1
  ADD CONSTRAINT FK_table1_Item_ID (Item_ID)
    REFERENCES table2 (Item_ID);

CodePudding user response：

Adding a column to Table 1.

ALTER TABLE table1 ADD Item_ID integer DEFAULT NULL;

Create identifiers by name and save them to a new column.

WITH items_ids AS (
  SELECT ID, DENSE_RANK() OVER (ORDER BY Item_Name) AS Item_ID_Calc
  FROM table1
)
UPDATE items_ids
SET Item_ID = Item_ID_Calc;

Creating a new table with unique identifiers.

CREATE TABLE table2 (
  Item_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  Item_Name VARCHAR(6) NOT NULL
);

SET IDENTITY_INSERT table2 ON;

INSERT INTO table2 (Item_ID, Item_Name)
SELECT DISTINCT Item_ID, Item_Name
FROM table1;

SET IDENTITY_INSERT table2 OFF;

or a simplified way:

SELECT DISTINCT Item_ID, Item_Name
INTO table2
FROM table1;

Deleting a column with names in Table 1, setting NOT NULL values for the identifier, and creating a foreign key.

ALTER TABLE table1 ALTER COLUMN Item_ID integer NOT NULL;
ALTER TABLE table1 DROP COLUMN Item_Name;
ALTER TABLE table1 
  ADD CONSTRAINT FK_table1__table2 FOREIGN KEY (Item_ID) REFERENCES table2(Item_ID);

db<>fiddle