Use SELECT results as a variable in a loop

Question

I've searched here and elsewhere, and haven't found an answer yet. Hope I didn't miss it.

Using SQL Server Management Studio 2008 R2.

I have n specific databases on my server (there are other DBs as well, but I'm only interested in some of them)

Each of these databases has a table within it, which all have the same name. The only difference is the DB name. I want to aggregate these tables together to make one big table on a different database (different to the other DBs).

I can get the db names from the results of a query.

N is unknown.

Is a loop the way to go about this?

I was thinking something along the lines of the following pseudocode:

Set @dbnames = SELECT DISTINCT dbname FROM MyServer.dbo.MyTable

For each @name in @dbnames
    INSERT INTO ADifferentDB.dbo.MyOtherTable
    SELECT * FROM @name.dbo.table
Next name

(Clearly I'm new to using SQL variable as well, as you can see)

I'm not a SQL Server expert (I use pgsql), but you'll want to look at [`EXECUTE()`](http://msdn.microsoft.com/en-us/library/ms188332.aspx). Since the source table name is going to vary, you'll have to create each `INSERT` query on the fly as a string, and execute it using `EXECUTE()`. — cdhowie, Sep 28 '12 at 18:02

daniloquio · Accepted Answer · 2012-09-28T18:16:28.450

Your first problem is about iterating the databases: you cand do that with a cursor

Then you have another problem, executing a query where part of it is variable (database's name). You can do that with execute function.

All that is something similar to this:

DECLARE @query VARCHAR(max)
DECLARE @dbname VARCHAR(100)
DECLARE my_db_cursor CURSOR
            FOR SELECT DISTINCT dbname FROM MyServer.dbo.MyTable
OPEN my_db_cursor 
FETCH NEXT FROM my_db_cursor 
INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @query = 'INSERT INTO ADifferentDB.dbo.MyOtherTable
                   SELECT * FROM ' + @dbname + '.dbo.table'
    EXECUTE(@query)  

    FETCH NEXT FROM my_db_cursor 
    INTO @dbname
END
CLOSE my_db_cursor 
DEALLOCATE my_db_cursor

@tkrex Glad it was useful. Remember Cursors are very pricey in terms of resources; you should be careful about using them. They are OK for one-time operations like this case though. You can take a look at this if you want http://stackoverflow.com/questions/743183/what-is-wrong-with-cursors — daniloquio, Sep 28 '12 at 19:13

score 1 · Answer 2 · answered Sep 28 '12 at 18:01

1

what you want to do is define a CURSOR for row-level operation. here is some doc

answered Sep 28 '12 at 18:01

amphibient

29,770
54
146
240

I had never even heard of cursors before! (Reveals how much of a SQL noob i am, i suppose.) This looks incredibly useful. Thanks for the link. – tkrex Sep 28 '12 at 18:33

score 1 · Answer 3 · answered Sep 28 '12 at 18:17

I would suggest using sp_MSForEachDB:

EXEC sp_MSForEachDB '
-- Include only the databases you care about.
IF NOT EXISTS (
    SELECT *
    FROM MySever.dbo.MyTable
    WHERE dbname = ''?''
)
    -- Exit if the database is not in your table.
    RETURN

-- Otherwise, perform your insert.
INSERT INTO ADifferentDB.dbo.MyOtherTable
SELECT * FROM ?.dbo.table
'

In this case, ? is a token that is replaced with each database on the server.

Use SELECT results as a variable in a loop

3 Answers3