Run table names pulled from sys.tables SQL Server 2008R2

Question

I need to identify tables that were created today by an interface, which I was able to do by using following query:

Note: The interface changes table names on daily basis.

SELECT [name] AS [TableName]
FROM sys.tables
WHERE NAME LIKE '_XYZExport_%'
    AND CAST(create_date AS DATE) = CAST(GETDATE() AS DATE)
ORDER BY NAME

What I need: Once the table names are pulled, I need dump its data into a new table. How can this be done easily?

Example:

Following tables returned from my queries:

1. _XYZExport_B02
2. _XYZExport_B12
3. _XYZExport_B22

I want to take these returned tables and insert their data into an existing Archive table using Union All.

Any help would be great!

Answer 1

You are on the right track with your "cursor" tag. I would recommend creating an insert statement and executing it each cursor loop.

DECLARE @TableName sysname
DECLARE @SQLInsert VARCHAR(100)

DECLARE TableNamesCursor CURSOR FAST_FORWARD READ_ONLY FOR 

SELECT [name] AS [TableName]
FROM sys.tables
WHERE NAME LIKE '_XYZExport_%'
    AND CAST(create_date AS DATE) = CAST(GETDATE() AS DATE)
ORDER BY NAME

OPEN TableNamesCursor

FETCH NEXT FROM TableNamesCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQLInsert = 'INSERT INTO ArchiveTable SELECT * FROM ' + @TableName
    EXEC sp_executesql @SQLInsert

    FETCH NEXT FROM TableNamesCursor INTO @TableName
END

CLOSE TableNamesCursor
DEALLOCATE TableNamesCursor

Hope that gets you going.

Noel