CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
Stephanie Fischer
Stephanie Fischer

Reputation: 15

TSQL: running exec after INSERT

I need to convert old data from multiple tables. I only just found out now, after writing the stored procedure to do this for one table, that it needs to also handle data from a couple other tables. This means that my procedure needs to be changed to properly handle this. I would like to do this dynamically, while keeping away from performance hits. I learned about using exec sp_executesql @SQLStatement to avoid performance issues with dynamic SQL.

However, if I write the below, I get no results:

SET @SQLStatement = 'select CONVERT(XML, ' + @ConvColumn + ') ' + @AdditionalColumns + ' from ' + @ConvTable
INSERT INTO @TempDataConv
exec sp_executesql @SQLStatement

Yet, if I write it explicitly defining the table and columns, this works just fine:

INSERT INTO @TempDataConv
SELECT
    DecPageID,
    PolicyID,
    PolicyNumber,
    CONVERT(XML, DecInfo),
    null
FROM [InternalPortal].[dbo].Decpages

What might be going on? As no results are returned, I am unsure how to figure out the problem. I assume that perhaps I cannot do the below. Is that true? Should I simply write this out in if blocks for the different tables?

 INSERT INTO @TempDataConv
exec sp_executesql @SQLStatement

Upvotes: 0

Views: 83

Answers (1)

Thom A
Thom A

Reputation: 95830

I suspect that you would get more similar performance if you perform the INSERT inside the dynamic statement, not using an INSERT INTO...EXEC.... This would result in something like this:

DECLARE @ConvColumn sysname,
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @AdditionalColumns table (Position int,ColumnName sysname);

SET @ConvColumn = N'DecInfo';

INSERT INTO @AdditionalColumns
VALUES(1,N'DecPageID'),
      (2,N'PolicyID'),
      (3,N'PolicyNumber');

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'INSERT INTO dbo.YourTable ({ColumnsList})' + @CRLF +
           N'SELECT CONVERT(xml, ' + QUOTENAME(@ConvColumn) + N'),' + @CRLF +
           STUFF((SELECT N',' + @CRLF +
                         N'       ' + QUOTENAME(ColumnName)
                  FROM @AdditionalColumns
                  ORDER BY Position ASC
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,3,N'') + @CRLF +
          N'FROM [InternalPortal].[dbo].Decpages;';

--PRINT @SQL; --Your debugging best friend

EXEC sys.sp_executesql @SQL;

One caveat, you won't be able to use a table variable to INSERT into, however, you could create a temporary table, outside the scope of the dynamic statement, and INSERT into that.

Notice, as well, that I make this secure by the use of QUOTENAME. This is why I use a table variable to store the names of the columns, as you would never be able to achieve that with a delimited list (and that would really leave the injection gates wide open). You'll also need to replace {ColumnsList} with the actual columns to be inserted into.

Upvotes: 2

Related Questions