Dynamic SQL - creating a temp table with a name that includes a random number

Question

I am trying to create a temp table with a random number.   The code runs with no errors, but the table is not created.

Declare @SQL varchar(255);
Declare @randomNumber varchar(10);

Set @randomNumber = Cast(ROUND(RAND() * 100000,0) as varchar(10));

set @SQL = 'CREATE TABLE #tmp' + @randomNumber + '( 
	 [STOCKNUMBER] [varchar](50) NOT NULL,
	 Constraint Pk_' + @randomNumber + ' Primary Key (STOCKNUMBER)
WITH (IGNORE_DUP_KEY = ON) 
)
ON [PRIMARY]';

execute (@SQL);

Thanks for any ideas !!

Tom

---

MisterT99

Answer 1

A dynamic SQL statement works in its own execution environment, like a stored procedure. At the tremination of a stored procedure all the local temporary tables (#...) are deleted, because this is the semantics of local temporatry tables. The same applies to the execution of a dynamic SQL statement.

If you want to communicate a temporary data table between a script and a dynamic SQL statement, you have to use global temporary table with ##, and the same applies to a stored procedure and the invoking script. This is the semantic of global temporaty tables.

Declare @SQL varchar(255);
Declare @randomNumber varchar(10);

Set @randomNumber = Cast(ROUND(RAND() * 100000,0) as varchar(10));

set @SQL = 'CREATE TABLE ##tmp' + @randomNumber + '( 
[STOCKNUMBER] [varchar](50) NOT NULL,
Constraint Pk_' + @randomNumber + ' Primary Key (STOCKNUMBER)
WITH (IGNORE_DUP_KEY = ON) 
)
ON [PRIMARY]';
print @SQL
execute (@SQL);
go
select * from ##tmp92866

---

Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Prajesh,

Thank you for your excellent help !!

Your explaination was very clear !!

Best wishes,

Tom

---

MisterT99

Answer 3

>> I am trying to create a temp table with a random number. The code runs with no errors, but the table is not created. <<

What your code seems to be trying to do is fake an 1950's magnetic  scratch tape in violation of RDBMS principles and common sense! Good SQL programmers hate dynamic SQL. It is a confession of failure to design a correct schema, so that any future random user is presumed to be a better programmer than you are. 

Please read any book -- and I mean any book – on data modeling. What are you trying to do? 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 4

I have been sent a flat file from another company.  The file has duplicate stock numbers.

I am trying to remove the duplicate stock numbers by loading the records into a table with the option IGNORE_DUP_KEY = ON.

Previously I had code which using a local tmp table.  The code worked, but if there is another process running the same code , which is possible, I would get an error on the Primary key saying the object was already in the database.

So, it seemed logical to create a random table name with a random Primary Key name to prevent the error.

Bottom line is that this is really a data conversion program.

---

MisterT99

Answer 5

>> I have been sent a flat file from another company. The file has duplicate stock numbers. I am trying to remove the duplicate stock numbers by loading the records into a table with the option IGNORE_DUP_KEY = ON. .. Bottom line is that this is really a data conversion program. <<

It would be nice to use an ETL tool to clean up the data, but we can use a SELECT DISTINCT to filter the dups, then MERGE the new data into the target. You can also capture source rows that are already in the target.  Here is the skeleton: 


MERGE INTO Target (..)
USING SELECT DISTINCT ..
       FROM Source
ON Target.stock_nbr = Source.stock_nbr
WHEN NOT MATCHED
THEN INSERT ..; 

This is one statement; the trick is loading the source. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 6

CELKO - Many thanks for the idea !!

I will create some code based on your skeleton.

Many Thanks !!

Best wishes,

Tom

---

MisterT99

Answer 7

>> I am trying to create a temp table with a random number. The code runs with no errors, but the table is not created. <<

What your code seems to be trying to do is fake an 1950's magnetic  scratch tape in violation of RDBMS principles and common sense! Good SQL programmers hate dynamic SQL. It is a confession of failure to design a correct schema, so that any future random user is presumed to be a better programmer than you are. 

Please read any book -- and I mean any book – on data modeling. What are you trying to do? 

The use of "scratch" tapes worked just fine way back when and they work just fine now. Even SQL Server will create "scratch" tables in the background for many different reasons. "Scratch" tables are just fine and can be an essential element in performance by allowing for "Divide'n'Conquer" methods (another '50's concept that still works just fine) instead of insanely sized "all in one" queries.

---

--Jeff Moden

Answer 8

Previously I had code which using a local tmp table.  The code worked, but if there is another process running the same code , which is possible, I would get an error on the Primary key saying the object was already in the database.

No, a local temp table is local to the process. SQL Server already adds that random number to the temp table name behind the covers, so you do not need to add it yourself. If you got errors because of two processes running simultaneousely it was due to something else.

---

Erland Sommarskog, SQL Server MVP, [e-mail]