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.
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';