SELECT INTO

I just came across a stored procedure that does something like this:


DECLARE @stmt NVARCHAR(MAX);
CREATE TABLE #foo (col1 INT, col2 INT, col3 INT);
SELECT @stmt = N'SELECT col1, col2, col3 INTO #foo FROM bar;';
EXEC sp_executesql @stmt;

The incorrect assumption that the developer made was that the first #foo (the one created with CREATE TABLE) was the same as the second #foo (the one referenced in the INTO clause). But that’s not the case. To prove it, try this slight variation:


DECLARE @stmt NVARCHAR(MAX);
CREATE TABLE #foo (name NVARCHAR(2));
SELECT @stmt = N'SELECT TOP 5 name INTO #foo FROM sys.objects; SELECT * FROM #foo;';
EXEC (@stmt);

If the @stmt was really using the first #foo, then what would you see? The first two characters from name from 5 rows from sys.objects, right? No, actually you’d see Msg 8152, “String or binary data would be truncated.”. But that’s beside the point. The point is that it isn’t using that first #foo. It’s creating a second one. And it’s getting away with that because it’s running within EXEC. Take out the use of @stmt and SQL will complain to you when you try the “SELECT … INTO #foo” that #foo already exists.

Also note that the original used sp_executesql, but that my second query used only EXEC. That was to prove that it was EXEC alone, not sp_executesql, that was the differentiator.

Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s