Using variable name to run query on multiple tables

Question

what I am trying to do is run a query multiple times over multiple tables, so what I have here is a table of the table names that cycles through setting @tablename to the name of the table on each iteration that I want to run the query on.

As you can see below @tablename is the name of the table I want to run the queries on but how do i run these queries using @tablename as the table name?

CREATE TABLE [BusinessListings].[dbo].[temptablenames]
(id int,
name nvarchar(50),
)

INSERT INTO [BusinessListings].[dbo].[temptablenames] (id, name)
VALUES 
(1,'MongoOrganisationsACT1'),
(2,'MongoOrganisationsNSW1'),
(3,'MongoOrganisationsNT1'),
(4,'MongoOrganisationsQLD1'),
(5,'MongoOrganisationsSA1'),
(6,'MongoOrganisationsTAS1'),
(7,'MongoOrganisationsVIC1'),
(8,'MongoOrganisationsWA1');

DECLARE @tablename sysname,
@id int
SET @id = 1
WHILE (@id < 9)
BEGIN
select @tablename = name from temptablenames where id = @id

select @tablename


        select _key_out, sum(quality_score) as sumscore, count(*) as reccount, (sum(quality_score) / count(*)) as ave
        into tempga0
        from @tablename
        group by _key_out

        select _key_out, count(*) as reccount
        into tempga3
        from @tablename
        where dedupe_result is null
        group by _key_out
        having count(*)>1

        select a._key_out, max(quality_score) as maxdedupetotalscore
        into tempga4
        from
        @tablename a
        join
        tempga3 b
        on a._key_out = B._key_out
        --where isdeleted is null
        group by a._key_out

        --- keep records
        update @tablename
        set dedupe_result = 'Keep'
        from 
        @tablename a
        join
        tempga4 b
        on a._key_out = B._key_out  
        where a.quality_score = b.maxdedupetotalscore
        --and isdeleted is null
        and dedupe_result is null

SET @id = @id + 1 
END
GO

DROP TABLE [BusinessListings].[dbo].[temptablenames]

note: this is only part of the queries that I want run, I just want to figure out how to subsitute the variable in the query as the table name. Also I know this isnt good form but there is a reason I need to do it this way.

updated working code here:

DECLARE @tablename nvarchar(30),
@id int,
@SQLStr nvarchar(1000)
SET @id = 1
WHILE (@id < 9)
BEGIN
select @tablename = name from temptablenames where id = @id

IF OBJECT_ID('tempga0') IS NOT NULL
DROP TABLE tempga0

    set @SQLStr = 'select _key_out, sum(quality_score) as sumscore, count(*) as reccount, (sum(quality_score) / count(*)) as ave
into tempga0
from ' + @tablename + ' group by _key_out'

    exec(@SQLStr)


 SET @id = @id + 1 
END
GO

Jaques · Accepted Answer · 2012-12-19 15:23:06Z

1

Use the Exec command. Write your query in a variable like and execute it

Declare @SQLStr = 'Select * into X from ' + @tablename
exec(@SQLStr)

You just have to be carefull. I see that you are using into statements. You will have to check that the table does not already exist because you will get an exception. You will need to drop the tables, or a better way would be to do this before you start your loop:

CREATE TABLE tempga0 (
_key_out int,
sumscore numeric(18,9),
reccount int,
ave numeric(18,9))

--rest of the tables to be created here...

Create all the tables, and when you start your While loop add a

WHILE (@id < 9)         
BEGIN    
    TRUNCATE TABLE tempga0
    --truncate the rest of the tables

    --Do the rest of your stuff here
END

Hope it helps

edited Dec 19, 2012 at 15:23

answered Mar 13, 2012 at 6:05

Jaques

2,2872 gold badges20 silver badges36 bronze badges

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

2 Comments

Dorf Over a year ago

i keep getting an error when i try this i am just trying to get the first query working here is the error 'The identifier that starts with 'select _key_out, sum(quality_score) as sumscore, count() as reccount, (sum(quality_score) / count()) as ave into tempga0 fro' is too long. Maximum length is 128.' i cant post the code in comment so i updated my question to show the code i am trying

Dorf Over a year ago

Ah I got it working turns out i just needed to change the double quotes to single quotes and i am just using 'IF OBJECT_ID('tempga0') IS NOT NULL DROP TABLE tempga0' to check if table exists ill update working code in my question, thanks Jaques

Collectives™ on Stack Overflow

Using variable name to run query on multiple tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related