1

I have about 200 tables in a schema.

I need to replicate these tables in a new backup schema with an automatic procedure.

I would like to create a procedure to dynamically recreate all the Tables in a Schema (potentially dynamic number of tables and columns) on a different schema.

I can cycle all the tables and create the SELECT * INTO dbo_b.TABLE FROM dbo.TABLE statement, but I get the error:

Column 'AMBIENTE' has a data type that cannot participate in a columnstore index.

I created a view that simply SELECT * FROM TABLE, and tried to perform the SELECT * INTO dbo_b.TABLE from dbo.VIEW but I got the same issue.

It works only if I create the dbo_b.Table and INSERT INTO it: so I would need to generate a script to automatically cycle all the tables in my schema and generate a script to create the tables in the new schema.

It's not a one time job, it should run every day so I cannot do it manually.

Daniel Widdis
  • 8,424
  • 13
  • 41
  • 63
Lorenzo Vigano
  • 21
  • 4
  • Which dbms are you using? (Different products have different tools.) – jarlh Sep 30 '21 at 16:27
  • Synapse through SQL Server management Studio 2019 – Lorenzo Vigano Sep 30 '21 at 16:30
  • Have you searched Stack Overflow? eg [generate-sql-create-scripts-for-existing-tables-with-query](https://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query) – Stu Sep 30 '21 at 16:32
  • Tried this by gave me an error "Incorrect syntax near FOR" – Lorenzo Vigano Sep 30 '21 at 16:34
  • Can't help you with that as I'm sure the parser is correct! However I've used those scripts successfully to migrate hundreds of tables from one database to another. – Stu Sep 30 '21 at 16:35
  • 1
    Perhaps you and power83 can collaborate on addressing the [same question](https://stackoverflow.com/questions/69395725/sel-server-select-into-msg-35343). "Backup" implies to me that you need a source control system and a development process that will capture whatever schema changes you make over time. The – SMor Sep 30 '21 at 16:49
  • You can try SELECT * INTO dbo_b.TABLE FROM dbo.TABLE WITH ( HEAP ); – Wouter Oct 01 '21 at 09:12

1 Answers1

1

Seams we get the same issue.

You can try to loop on all table and create table in the new schema in this way:

    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'YYYY' AND TABLE_SCHEMA = 'XXXX') 
 drop table [ZZZZ].[YYYY]  

CREATE TABLE [ZZZZ].[YYYY] 
WITH ( DISTRIBUTION = ROUND_ROBIN
,    HEAP ) as  
( SELECT * FROM XXXX.YYYY )

Let me know. BR

power83
  • 69
  • 8