SQL: using variables in loop to create new table

Question

I have a problem that I don't know how to solve in SQL. Or two problems.

Create a Loop that would use table1 to update variable
Use the variable in the name of source temp table and the name of final out output table.

Table1 has just one column

+--------+--+
| Header |  |
+--------+--+
| Name1  |  |
| Name2  |  |
| Name3  |  |
| Name4  |  |
| ...    |  |
+--------+--+

So I want to Loop through those rows and use them as variable @variable. Something like:

IF OBJECT_ID('table_@variable','U') IS NOT NULL
DROP TABLE table_@variable

SELECT *
INTO table_@variable
FROM #@variable

So the first itinerary of the loop would be:

IF OBJECT_ID('table_Name1','U') IS NOT NULL
DROP TABLE table_Name1

SELECT *
INTO table_Name1
FROM #Name1

The second:

IF OBJECT_ID('table_Name2','U') IS NOT NULL
DROP TABLE table_Name2

SELECT *
INTO table_Name2
FROM #Name2

And so on as long there are rows in table1

I hope I explained it well enough.

Thank you for your help. Matt

p.s. I am using Microsoft SQL Server Management Studio to run my queries.

Any time youre creating table-per-entity (table1, table2, tableX) you're probably doing it wrong! You're re-inventing a database within a database. (Of course, there are exceptions to every rule) — Jamiec
– Jamiec, Commented Dec 2, 2015 at 11:57

Joe Taras · Accepted Answer · 2015-12-02 12:06:11Z

1

You must use dynamic SQL to accomplish your goal.

Try this:

DECLARE @suffix varchar(100)
DECLARE #crs INSENSITIVE CURSOR FOR
SELECT header FROM yourtable
FOR READ ONLY
OPEN #crs
FETCH NEXT FROM #crs INTO @suffix
WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE @str nvarchar(1000)
    SET @str = N'IF OBJECT_ID(''table_' + @suffix + ''',''U'') IS NOT NULL
    DROP TABLE table_' + @suffix + '

    SELECT * INTO table_' + @suffix + ' FROM #' + @suffix
    EXECUTE sp_executesql @str
    FETCH NEXT FROM #crs INTO @suffix
END
CLOSE #crs
DEALLOCATE #crs

edited Dec 2, 2015 at 12:06

answered Dec 2, 2015 at 11:47

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jamiec Over a year ago

You still have table_@variable in your dynamic sql, this needs to be concatenated like the other instances of the same.

Joe Taras Over a year ago

@Jamiec: Yes, it's possible use a table variable. I prefer my notation for backward compatibility because we don't know which version of Sql Server uses our OP

BeanFrog Over a year ago

Jamiec means that the line of code DROP TABLE table_@variable should actually be DROP TABLE table_' + @suffix

Mateusz Konopelski Over a year ago

@JoeTaras This just works perfect and I will be able to use it in more of my staff. Thank you very much!! :)

Joe Taras Over a year ago

@MateuszKonopelski: Have a nice day ;)

|

Stuart J Cuthbertson · Accepted Answer · 2015-12-02 13:48:35Z

As another answer says, you need to use dynamic SQL to achieve this. Dynamic SQL just means you build a SQL query inside a string-typed variable (usually a varchar), and then execute the contents of the variable at the end.

However, whilst you need dynamic SQL, you don't need any kind of loop (or cursor) to achieve this in your situation.

Instead of a loop, a more concise way to build the dynamic query string you need is using T-SQL's string concatenation operator, +=, like this:

declare @SQL varchar(max)=''    -- Create a variable to hold our dynamic SQL

select @SQL += 
'IF OBJECT_ID(''table_' + Header + ''',''U'') IS NOT NULL
    DROP TABLE table_' + Header + '

SELECT * INTO table_' + Header + ' FROM #' + Header + '

'
from Table1 -- @SQL will be added to for every row in this table

exec(@SQL)  -- Now run the contents of the variable as a SQL query itself

This is highly likely to be much more performant than a loop- or cursor-based approach, although as ever, your mileage may vary.

Collectives™ on Stack Overflow

SQL: using variables in loop to create new table

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related