none
executing statement saved in a table

    Question

  • hi I created a tempTable in a script. the table contains Insert Statements I want to execute. How can I do this.

    Here the end of my current script:

    INSERT INTO @S(SQLStatement)
     SELECT 'SELECT * INTO '+ @TargetDB + '.dbo.' + TableName + ' FROM '  + TableName + ' WHERE ' + @ColName + ' = ' + CONVERT(varchar(5), @mybin1) + @ColValue + CONVERT(varchar(5), @mybin1) + ' OR ' + @ColName + ' = ' + CONVERT(varchar(5), @mybin1) + @ColValue2 + CONVERT(varchar(5), @mybin1) FROM @T
    

     HOW can I now iterate over the @S and execute the statements?
    Sunday, July 21, 2013 7:18 PM

Answers

  •  HOW can I now iterate over the @S and execute the statements?

    One method is to concatenate the rows into a single script and execute:

    DECLARE @SQLStatement nvarchar(MAX) = N'';
    SELECT @SQLStatement = @SQLStatement + SQLStatement + N';' FROM @S;
    EXEC (@SQLStatement);
    Alternatively, you could declare a cursor and execute each statement in a loop.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 21, 2013 7:26 PM

All replies

  •  HOW can I now iterate over the @S and execute the statements?

    One method is to concatenate the rows into a single script and execute:

    DECLARE @SQLStatement nvarchar(MAX) = N'';
    SELECT @SQLStatement = @SQLStatement + SQLStatement + N';' FROM @S;
    EXEC (@SQLStatement);
    Alternatively, you could declare a cursor and execute each statement in a loop.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 21, 2013 7:26 PM
  • ok so I would prefer the Cursor stuff because there are more than 1500 Statements to execute.

    Thx a lot....now I can try to find out hot this Cursor Thing works

    Sunday, July 21, 2013 7:39 PM
  • ok so I would prefer the Cursor stuff because there are more than 1500 Statements to execute.

    Thx a lot....now I can try to find out hot this Cursor Thing works

    Here's a cursor example:

    DECLARE @SQLStatement nvarchar(MAX);
    DECLARE SqlStatements CURSOR LOCAL STATIC FOR
    	SELECT SQLStatement FROM @S;
    OPEN SqlStatements;
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM SqlStatements INTO @SqlStatement;
    	IF @@FETCH_STATUS = -1 BREAK;
    	EXEC (@SQLStatement);
    END;
    CLOSE SqlStatements;
    DEALLOCATE SqlStatements;
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 21, 2013 7:57 PM