Generating unique identifiers as a set based query

Question

I am moving a bunch of code over from entirely cursor based to set based and generating this has been doing my head in. We create a 6 character shortcode (unique) for each company inserted into the database and I (want) to achieve this outside of a cursor.

Example of where I am at so far:

CREATE TABLE #customers (name VARCHAR(50), shortname VARCHAR(10))
INSERT INTO #customers VALUES
('Michael Smith', 'Michae')
,('Michael Douglas', 'Mich_1')
,('Michael Yang', 'Mich_2')

CREATE TABLE #newcustomers (name VARCHAR(50), shortname VARCHAR(10) NULL)
INSERT INTO #newcustomers (name) VALUES
('Michael Black')
,('Michael White')

SELECT * FROM #customers
SELECT * FROM #newcustomers

DECLARE @shortname VARCHAR(10)
DECLARE @iteration INT = 0

WHILE EXISTS(SELECT shortname FROM #customers WHERE shortname = @shortname)
BEGIN
    SELECT @shortname = LEFT(name, 6) FROM #newcustomers

    UPDATE #newcustomers SET shortname = @shortname

    SET @shortname = LEFT(@shortname, 4) + '_' + @iteration

    SET @iteration = @iteration + 1
END

Hopefully the example is sufficient in identifying where I am trying to get to, any suggestions or examples would be very helpful. My example does not work.

Answer 1

Try this

Your table as mock-up

CREATE TABLE #customers (ID INT IDENTITY, name VARCHAR(50), shortname VARCHAR(10))
INSERT INTO #customers VALUES
('Michael Smith', 'Michae')
,('Michael Douglas', 'Mich_1')
,('Michael Yang', 'Mich_3')
,('Testman', 'Testma')
,('Testman1', 'Test_1');

CREATE TABLE #newcustomers (ID INT IDENTITY,name VARCHAR(50), shortname VARCHAR(10) NULL)
INSERT INTO #newcustomers (name) VALUES
('Michael Black')
,('Michael White')
,('Testman2')
,('Someone new');

--This CTE will combine all existing names

WITH AllNames AS
(
    SELECT '1_old' AS datasource,ID,name,shortname FROM #customers
    UNION ALL SELECT '2_new',ID,name,shortname FROM #newcustomers
)

--This CTE will use the combined list and calculate the right "index"

,ShortNames AS
(
    SELECT c.*
          ,A.First6
          ,ROW_NUMBER() OVER(PARTITION BY A.First6 ORDER BY datasource,ID) AS NrTotal
          ,ROW_NUMBER() OVER(PARTITION BY datasource,A.First6 ORDER BY datasource,ID) AS Nr
          ,CASE WHEN ISNUMERIC(SUBSTRING(shortname+'      ',6,10))=1 
                THEN CAST(SUBSTRING(shortname+'      ',6,10) AS INT) ELSE 0 END AS ExistIndex
    FROM AllNames AS c
    CROSS APPLY(SELECT LEFT(name + '      ',6)) AS A(First6)
)

--All new with NrTotal=1 get the 6 letters as is, all other get the index

SELECT *
      ,CASE WHEN datasource='1_old' THEN shortname ELSE
        CASE WHEN datasource='2_new' AND NrTotal=1 THEN First6 
             ELSE LEFT(First6,4) + '_' + CAST(Nr + (SELECT ISNULL(MAX(x.ExistIndex),1)
                                                      FROM ShortNames AS x 
                                                      WHERE x.First6=ShortNames.First6) AS VARCHAR(5)) 
        END
       END
FROM ShortNames

GO
DROP TABLE #customers;
DROP TABLE #newcustomers;

The result

+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+
| datasource | ID | name            | shortname | First6 | NrTotal | Nr | ExistIndex | (Kein Spaltenname) |
+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+
| 1_old      | 1  | Michael Smith   | Michae    | Michae | 1       | 1  | 0          | Michae             |
+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+
| 1_old      | 2  | Michael Douglas | Mich_1    | Michae | 2       | 2  | 1          | Mich_1             |
+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+
| 1_old      | 3  | Michael Yang    | Mich_3    | Michae | 3       | 3  | 3          | Mich_3             |
+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+
| 1_old      | 4  | Testman         | Testma    | Testma | 1       | 1  | 0          | Testma             |
+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+
| 1_old      | 5  | Testman1        | Test_1    | Testma | 2       | 2  | 1          | Test_1             |
+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+
| 2_new      | 1  | Michael Black   | NULL      | Michae | 4       | 1  | 0          | Mich_4             |
+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+
| 2_new      | 2  | Michael White   | NULL      | Michae | 5       | 2  | 0          | Mich_5             |
+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+
| 2_new      | 4  | Someone new     | NULL      | Someon | 1       | 1  | 0          | Someon             |
+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+
| 2_new      | 3  | Testman2        | NULL      | Testma | 3       | 1  | 0          | Test_2             |
+------------+----+-----------------+-----------+--------+---------+----+------------+--------------------+