CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Pr0no
Pr0no

Reputation: 4099

Fill dynamic array with values from an array

I have to execute a similar query on many tables. Instead of writing an n amount of almost similar similar queries, I would like to use a dynamic query. Pseudo:

array = (
    'table_a' => 'value_a',
    'table_b' => 'value_b',
    'table_c' => 'value_c'
);

foreach (array as table => value)
    exec(
        'select ' + @value + ' into #' + @table + ' from ' + @table
    );
end

Is something like this possible in SQL Server 2008 RE? Any help would be greatly appreciated!

Upvotes: 1

Views: 161

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6771

You can do this in SQL Server using something like the following script:

CREATE TABLE #temp (id INT IDENTITY(1,1), tablename VARCHAR(50))
INSERT INTO #temp
        ( tablename )
VALUES  ( 'table_a' ),('table_b'),('table_c')

DECLARE @sql NVARCHAR(MAX)
DECLARE @tblcount INT, @i INT = 1
SELECT @tblcount = MAX(id) FROM #temp

WHILE @i <= @tblcount
    BEGIN
        SELECT @sql = 'SELECT t.somefield, t.otherfield as ' + tablename + ' INTO #some_temptable_' + tablename + ' FROM #mytable t where SomeField like ''1''' FROM #temp WHERE id = @i
        EXEC sp_executesql @sql
        --SELECT @sql
        SELECT @i = @i + 1
    END

DROP TABLE #temp

Upvotes: 2

Related Questions