CODEWITHSUNDEEP
sql-server
mHelpMe
mHelpMe

Reputation: 6668

looping through a list of table names and executing a statement

I am trying to get the list of names of tables in a database that satisfy some constraint.

With this list of names I then want to copy the data over into a table in another database.

I think my loop below will work apart from one thing. I am not sure how to set the variable @tblName?

For example say below is the list of table names

 1. tblA
 2. tblB
 3. tblZ

On my first loop I just want to set @tblName to tblA.

My code

declare @numTbls int
declare @count int = 1
declare @tblName nvarchar(100)

set @numTbls = (select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'  and TABLE_NAME like '%somePattern%')

while @count <= @numTbls

    begin

    set @tblName = (select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME like '%somePattern%')

     exec ('insert into DATABASE_B.dbo.' + @tblName + ' select * from DATABASE_A.dbo.' + @tblName)

     set @count = @count + 1
 end

Upvotes: 1

Views: 65

Answers (2)

Squirrel
Squirrel

Reputation: 24763

You don't really required a loop to do this

DECLARE @SQL    NVARCHAR(MAX)

SELECT  @SQL    = ISNULL(@SQL, '') 
                + 'INSERT INTO DATABASE_B.dbo.' + QUOTENAME(TABLE_NAME) 
                + ' SELECT * FROM DATABASE_A.dbo.' + QUOTENAME(TABLE_NAME) + ';' + char(13)
FROM    INFORMATION_SCHEMA.TABLES 
WHERE   TABLE_TYPE = 'BASE TABLE'  
AND     TABLE_NAME like '%somePattern%'

PRINT   @SQL
EXEC    sp_executesql @SQL

Note : this is assuming that schema for the table in both database are identical and in same column sequence

Upvotes: 2

Pawel Czapski
Pawel Czapski

Reputation: 1864

Below should resolve your issue, (comments in the code). Add comment if something is not clear.

declare @numTbls int
declare @count int = 1
declare @tblName nvarchar(100)

-- create table var
declare @MY_TABLE table (ID int identity(1,1), TABLE_NAME nvarchar(100))

-- add your data to this table
insert into @MY_TABLE
select TABLE_NAME 
from INFORMATION_SCHEMA.TABLES 
where TABLE_TYPE = 'BASE TABLE' 
    and TABLE_NAME like '%somePattern%'

-- find max on this table
set @numTbls = (select count(*) from @MY_TABLE)

-- loop data
while @count <= @numTbls
    begin
    set @tblName = (select TABLE_NAME from @MY_TABLE where ID = @count)

    exec ('insert into DATABASE_B.dbo.' + @tblName + ' select * from DATABASE_A.dbo.' + @tblName)

    set @count = @count + 1
 end

Upvotes: 1

Related Questions