Generate backup on new schema iterating over certain tables – T-SQL

June 26, 2023

I got a list of table names from sys.tables that looks like this:

SELECT name
FROM sys.tables
WHERE name LIKE 'ETL%'

I want to move to a new schema (kinda like a backup) and I was thinking of using something like the following query being @table_name the current item from the list:

SELECT *
INTO [historic].[@table_name]
FROM @table_name

However I cannot seem to sort out how I can make this kind of iterator.

>Solution :

You’re probably only going to perform this once, you can use the following example query to construct the SQL statements needed, you can copy and paste into an SSMS window to use.

select Concat(
  'select * ',
  'into [historic].', QuoteName(name),
  ' from ', QuoteName(name), ';'
)
from sys.tables
where name like 'ETL%' and Schema_Name(schema_id) = 'dbo';