0

I use this code:

select @SQLString=N'select ....(long select query).....'
    + char(10)  + '; drop table _item_list '

and afterwards

insert @tbl EXEC dbo.sp_executesql @SQLString

It seems to be working, but can someone give me some clear info on this? I was expecting the executesql thing to have to be "faithful", as in related in its entirety, to the insert statement preceding it. It seems however that it has no problem feeding the select to the insert, and doing the drop table independently. Also, is the ; before the drop necessary?

George Menoutis
  • 6,894
  • 3
  • 19
  • 43

1 Answers1

0

You have many ways to populate data to a table:

  1. INSERT INTO TABLE VALUES
  2. SELECT INTO FROM
  3. INSERT INTO SELECT
  4. INSERT INTO EXEC <-- what you are talking about

And the ; is not very necessary, just to separate batch, the result will be the same, but for some may need to consider scope, such as batch or session, then it matters.

UPDATE: That is fine with irrelevant statements afterward of the select, even there are more than one select in your USP, but you have to make sure all the returned results (the select statement) need to be compatible with the table definition that you are referring to.

LONG
  • 4,490
  • 2
  • 17
  • 35
  • So let me state the question in a more clear way: in case of the insert into exec, as long as the FIRST statement of the exec is a select or values statement, then the exec string may have any number of irrelevant statements afterward? – George Menoutis Jul 13 '17 at 14:27