2

I have a T-SQL routine that copies user information from one table 'Radius' to another 'Tags'. However, as the rows are transfered, I would also like to include a unique randomly generated code in the INSERT (3 chars long). The code is generated by the WHILE loop below. Any way to do this?

INSERT Tags (UserID, JobID, Code) 
SELECT UserID, @JobID,  ?????
FROM Radius

Unique random code generator:

WHILE EXISTS (SELECT * FROM Tags WHERE Code = @code)
BEGIN

select @code=@code+char(n) from
(
select top 3 number as n from master..spt_values 
where type='p' and number between 48 and 57 or number between 65 and 90
order by newid()
) 
END

CLARIFICATION: The reason for doing this is that I want to keep the random code generation logic at the level of the SQL stack. Implementing this in the app code would require me to check the db everytime a potential random code is generated to see if it is unique. As the number of code records increases so will the number of calls to the db as probability increases that there will be more duplicate codes generated before a unique one is generated.

Jason
  • 1,129
  • 1
  • 9
  • 20

3 Answers3

5

Part One, Generate a table with all possible values

DECLARE @i int

CREATE TABLE #AllChars(value CHAR(1))

SET @i=48

WHILE @i<=57
BEGIN
    INSERT INTO #Allchars(value) VALUES(CHAR(@i))
    SET @i=@i+1
END

SET @i=65

WHILE @i<=90
BEGIN
    INSERT INTO #Allchars(value) VALUES(CHAR(@i))
    SET @i=@i+1
END

CREATE TABLE AllCodes(value CHAR(3),
          CONSTRAINT PK_AllChars PRIMARY KEY CLUSTERED(value))

INSERT INTO AllCodes(value)
SELECT AllChars1.Value+AllChars2.Value+AllChars3.Value
FROM #AllChars AS AllChars1,#AllChars AS AllChars2,#AllChars AS AllChars3

This is a one off operation and takes around 1 second to run on SQL Azure. Now that you have all possible values in a table any future inserts become, something along the lines of 

SELECT
RadiusTable.UserID,
RadiusTable.JobID,
IDTable.Value
FROM 
(
  SELECT ROW_NUMBER() OVER (ORDER BY UserID,JobID) As RadiusRow,
  UserID,JobID
  FROM Radius
) AS RadiusTable INNER JOIN  

(
    SELECT ROW_NUMBER() OVER (ORDER BY newID()) As IDRow,
    Value
    FROM AllCodes
) AS IDTable ON RadiusTable.RadiusRow = IDTable.IDRow

Before going with any of these schemes you had better be certain that you are not going to have more than 46656 rows in your table otherwise you will run out of unique ID Values.

David Steele
  • 3,433
  • 21
  • 23
  • Wow, David. Very nice job. Indeed why not just populate the table since the permutations would lead to > 46656 rows. Otherwise, I think any other method would be pushing SQL to do things it wasn't meant to do. – Jason Jul 19 '11 at 07:46
  • Glad you like it. It was an interesting problem to help me wake up. Don't forget to pick an answer ;) – David Steele Jul 19 '11 at 07:56
  • Ups. Problem: I implemented and ran the code. Unfortuantely, the SELECT TOP 1... statement generates the same code for every row inserted. – Jason Jul 19 '11 at 07:59
  • 2
    Can you use Row_number in both selects and link them for the insert? – Jason Goemaat Jul 19 '11 at 08:14
  • +1 Jason, you got there before me. I was squirreling around in SSMS while you shouted out the answer. See edit! – David Steele Jul 19 '11 at 08:33
  • I was only familiar with the ROW_NUMBER() construct in the context of paging. Never used it like this before... – Jason Jul 19 '11 at 08:46
  • Nice. If you wanted to, you could add a where to the IDTable query to exclude values in your new Tags table to allow you to insert different records in the future. Also if you added a column to AllCodes with a GUID column and ordered the ROW_NUMBER() by that you would get random but reproducable results for testing or inserting the same values in the future. – Jason Goemaat Jul 19 '11 at 08:47
  • The actual code is more complex as the "Radius table" in the original example is actually a SELECT statement that is already INNER JOINED to another table. I did this to make the example more transparent. However, with this new solution in hand this could get messy. ;) – Jason Jul 19 '11 at 08:52
  • No should be ok. Just add your inner join to the first derived table to bring out the additional fields. Alternatively you could use a cursor and insert rows one by one in a loop. – David Steele Jul 19 '11 at 08:58
4

I do not know if this is possible and suitable for your situation, but to me it seems that a scalar-valued function would be a solution.

Alexander K
  • 111
  • 2
  • 7
  • Tried that. Unfortunately no. The order by newid() line is not allowed in a function as it is non-deterministic. – Jason Jul 19 '11 at 06:23
1

Well, let me start over then.

This seems kind of ugly but it might work: newid() inside sql server function

The accepted answer that is.

Ah, been there done that too. The problem with this is that I am using T-SQL Stored Procedures that are called by Asp.net Where would I put the CREATE VIEW statement? I can't add it to the function file.

Community
  • 1
  • 1
Tory Netherton
  • 753
  • 5
  • 23
  • You may be able to do this more efficiently with a table valued function. It could be a real performance boon if you're working with many records. This is the simple way though. – Tory Netherton Jul 19 '11 at 06:17
  • Telarian, thanks but I tried this already. The 'order by newid()' line is not allowed in a function as it is non-deterministic. Otherwise this would indeed work. As in SELECT dbo.CodeGenerator() – Jason Jul 19 '11 at 06:25
  • ah... and you're just using newid() to give you a random order? Does RAND() work? – Tory Netherton Jul 19 '11 at 06:30
  • Hi Telarian, correct just newid(). And unfortunately RAND() also creates undeterminism and is therefore not accepted in a UDF. Also, I edited my original question a bit to clarify why I am doing this in T-SQL. – Jason Jul 19 '11 at 06:33
  • The CREATE VIEW solution would work if I knew a way to include it in a function file. Is this possible in T-SQL? So far no luck. It seems that the rules in the normal SQL query language are a bit more lax. I have tried to save the 'create view getNewID as select newid() as new_id' statement in both stored proc and function files, in neither case does the compiler let me even save the statement to the file. The error I get is: 'Incorrect syntax near the keyword 'View''. – Jason Jul 19 '11 at 07:09
  • It is possible to create a new view inside a stored procedure using dynamic tsql. http://dotnetfish.blogspot.com/2008/04/create-view-using-stored-procedure.html It isn't pretty though. David's solution makes a lot more sense to me. – Tory Netherton Jul 19 '11 at 07:34
  • Problem is that then the 'order by' will not recognize the uninitialized var/function, ie. code won't compile. – Jason Jul 19 '11 at 08:19
  • What does the ORDER BY NewId() do? – Jason Goemaat Jul 19 '11 at 08:21
  • It is used to select a random row. – Jason Jul 19 '11 at 08:28
  • Ah, I think the view is a one-time set-up to let you use the value in a function, your function or procedure doesn't have to create it... – Jason Goemaat Jul 19 '11 at 08:36
  • Thanks Jason. Any idea on how to create it? I only see a visual designer but no place to but a CREATE VIEW statement. – Jason Jul 19 '11 at 08:44