[SQL] Migrate Unique Columns – Ensuring Uniquness

If you want to migrate unique values from one table to another table with a smaller size, you need to truncate values. The problem is to make sure the values are still unique. Let’s look at the following example.

 

1. Migration Example

You have the following 2 tables.

You have some data in the “Product1” table.

Suppose you want to migrate data from “Product1” to “Product2”. The problem is that the “Product2” table can only store name of maximum 5 characters.

 

2. Truncating Names Uniquely

You can use the following SQL statements.

  • Use the temp table
  • Use the cursor
CREATE TABLE #Duplicates (Id INT, Value NVARCHAR(5));

DECLARE @orginalId INT;
DECLARE @orginalName NVARCHAR(10);
DECLARE @orginalNameTruncated NVARCHAR(10);

DECLARE my_cursor CURSOR FOR
  SELECT ProductId, ProductName
  FROM Product1
  ORDER BY ProductName;

OPEN my_cursor;

FETCH NEXT FROM my_cursor
INTO @orginalId, @orginalName;

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @orginalNameTruncated = LEFT(@orginalName, 5);

  IF EXISTS(SELECT * FROM #Duplicates WHERE Value = @orginalNameTruncated)
  BEGIN
    DECLARE @counter INT;
    SET @counter = 1;

    WHILE EXISTS(SELECT * FROM #Duplicates WHERE Value = @orginalNameTruncated)
    BEGIN
      SET @orginalNameTruncated = LEFT(@orginalNameTruncated, 3) + CAST(@counter as NVARCHAR(2));
      SET @counter = @counter + 1;
    END
  END

  INSERT INTO #Duplicates (Id, Value)
  VALUES (@orginalId, @orginalNameTruncated);

  FETCH NEXT FROM my_cursor
  INTO @orginalId, @orginalName;

END

CLOSE my_cursor;
DEALLOCATE my_cursor;

INSERT INTO Product2 (ProductId, ProductName)
SELECT Id, Value FROM #Duplicates;

DROP TABLE #Duplicates;

The result will be like this (“Product2” table).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s