0

I am trying to create a script in SQL Server 2017 that copies the values from one table to another in different schemas using the Stored Procedure of sp_MSForEachTable. So far I have been able to get the strings and when I print it it launches the query that I expect, but it throws problems like

'CREATE TRIGGER' must be the first statement in a query batch

and

Incorrect syntax near 'GO'.

A basic code structure is as follows:

EXEC sp_MSForEachTable 

@precommand = 'use bd1',

@command1 = 
'
DECLARE @tabla VARCHAR (MAX) = SUBSTRING(''?'', CHARINDEX(''.'', ''?'')+1, LEN(''?''));
SET @tabla = REPLACE(@tabla,''['','''');
SET @tabla = REPLACE(@tabla,'']'','''');
IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N''TR_cloneField_''+@tabla+'''' AND [type] = ''TR'')
BEGIN
      DROP TRIGGER [prueba_bd].[TR_cloneField_''+@tabla+'']
END
',

@command2 = '
DECLARE @tabla VARCHAR (MAX) = SUBSTRING(''?'', CHARINDEX(''.'', ''?'')+1, LEN(''?''));
SET @tabla = REPLACE(@tabla,''['','''');
SET @tabla = REPLACE(@tabla,'']'','''');
PRINT(@tabla);
DECLARE @sql VARCHAR (MAX);
CREATE TRIGGER [TR_cloneField_''+@tabla+'']
ON ?
AFTER INSERT
AS
    BEGIN
        SET NOCOUNT ON
        SET @sql = ''
            INSERT INTO schema2.''+@tabla+''
                    (
                        [field1] ,
                        [field2] ,
                    )
            SELECT * FROM ?'';
        EXEC(@sql)
    END
GO
',

@whereand = 'and upper(schema_name(schema_id)) = ''schema2'''
GO

I appreciate any help

EDIT: Thanks Bill.

Yes, that was the problem. Being a T-SQL I could not put the Go. So what I did was put the entire Trigger command into a dynamic T-SQL, and I only run it on each cycle.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Anthony Godoy
  • 21
  • 5
  • You can't use `GO` in dynamic SQL, as pointed out [here](https://stackoverflow.com/a/14473306/12859753), for instance. Try a cursor, and build & execute dynamic sql specific to each table within it. – Bill Jetzer Jul 09 '20 at 15:03
  • Yes, that was the problem. Being a T-sql I could not put the Go. So what I did was put the entire Trigger command into a dynamic T-Sql, and I only run it on each cycle. Thank you very much Bill. – Anthony Godoy Jul 09 '20 at 15:41

0 Answers0