Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    7

    Unanswered: Run query on multiple tables

    Hi I would like to run the same query on multiple tables.

    so say I have a list of tables

    @tableList = a|b|c|d

    and then i have my query looping through the tables

    for (@table in tableList)
    {
    update from @table
    set = ''
    }


    Is there a simple way to do this in an mssql query, if so how do I get to loop through the query switching the table name?

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you can use sp_msforeachtable for this. google it.

  3. #3
    Join Date
    Jan 2012
    Posts
    7
    Is there a way i can set sp_msforeachtable to only go through selected tables that i want it to instead of every table?

  4. #4
    Join Date
    Jan 2012
    Posts
    7
    I am trying something a bit different to that what I currently have is this;

    @tablename iterates through the table name i want to use but i cant get it to substitute for example "update @tablename"

    Code:
    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 nvarchar(50),
    @id int
    SET @id = 1
    WHILE (@id < 9)
    BEGIN
    select @tablename = name from temptablenames where id = @id
    
    --queries with @tablename go here eg, update @tablename
    
     SET @id = @id + 1 
    END
    GO
    
    DROP TABLE [BusinessListings].[dbo].[temptablenames]
    Last edited by Dorf Dorf; 03-09-12 at 01:57.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should get you started:
    Code:
    DECLARE @tbl    sysname,
            @sql    nvarchar(4000),
            @params nvarchar(4000),
            @count  int
    
    SET @tbl = N'#parent_child'
    
    SELECT * from #parent_child
       SELECT @sql =
       N' UPDATE dbo.' + quotename(@tbl) +
       N' SET Parent = @NewValue' + 
       N' WHERE Parent = @OldValue'
       SELECT @params = N'@OldValue CHAR(1), ' +
                        N'@NewValue CHAR(1) '
       EXEC sp_executesql @sql, @params, 'A', 'Z'
    
    SELECT * from #parent_child
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •