Insert statement with random generated number

Vote count:

0

What I'm trying to accomplish is to assign a 5 digit number to a row in the table and insert that value with column value to a different table.

For example, this query returns all the rows that I would like to assign a 5 digit random ID:

SELECT DISTINCT(ExternalAgentId) FROM lOGS_V WHERE EXTERNALAGENTID <> ''

Currently it's returning 4600 rows. NOTE* ExternalAgentID is varchar(50)

What I need to do is assign a random 5 digit number between 10001 and 39999. Once I generate the number insert it to the table with ExternalAgentId from previous query to another table.

Here's the approach I took:

DECLARE @randAgentID int;
DECLARE @AgentID int;
DECLARE @MIN INT;
DECLARE @MAX INT;

SET @MIN = 10001
SET @MAX = 39999

SELECT @randAgentID = ROUND(((@MAX - @MIN -1) * RAND() + @MIN), 0)

SELECT @randAgentID

SELECT @AgentID = InternalAgentID FROM VendorAgentIDs where InternalAgentID = @randAgentID

IF @AgentID is null
   BEGIN
      INSERT INTO VendorAgentIDs (VendorID, TRIAgentID) values (SELECT DISTINCT(ExternalAgentId) FROM TRI_PORTAL.DBO.SCREENPOPLOGS_V WHERE EXTERNALAGENTID <> '', @randAgentID)
END

ELSE
   BEGIN
      SELECT @randAgentID = ROUND(((@MAX - @MIN -1) * RAND() + @MIN), 0)
      INSERT INTO VendorAgentIDs (VendorID, TRIAgentID) values (SELECT DISTINCT(ExternalAgentId) FROM TRI_PORTAL.DBO.SCREENPOPLOGS_V WHERE EXTERNALAGENTID <> '', @randAgentID)
END

It's generating random 5 digit number correctly. However, I'm having two issues:

1. 
   

   I have a syntax error in my insert statements.

   
   

   
   

   ...values (SELECT DISTINCT(ExternalAgentId)...

   
   

   
   


2. 
   

   If the randAgentID exists in the table, it goes to the ELSE statement. I know the chances are low, but what if the next randAgentID also exists in the table? How can I prevent that?

   
   

The table VendorAgentIDs has three columns.

• ID (autoincrement)


• VendorID (varchar(50))


• InternalAgentID (PK, int)

Any suggestions on how I can resolve the above two issues?

Thanks

asked 2 mins ago

Sam

180

Insert statement with random generated number