5

I have an original table MessageQueue with six columns. When I try to do a SELECT INTO that table it creates a new (local) table called <network domain\user name>.MessageQueue with only three columns when I use the following code. What am I doing wrong?

declare 
    @TempTable table (idx smallint Primary Key IDENTITY(1,1), OneId int, OtherId int)

declare 
    @OneId int,
    @OtherId int,
    @date datetime = dbo.GetFloorDate(getdate()),
    @i int = 1

insert @TempTable select Id, OtherId from One where @date = (select dbo.GetSomeDate (Id))

select MessageId = 9999, OneId, OtherId into MessageQueue from @TempTable

select * from MessageQueue
2
  • 1
    If you get into the habit of specifying the schema prefix (e.g. dbo.MessageQueue) you are less likely to do this kind of thing inadvertently (you would have received an error message that the object already exists). Bit of background and dialog: sqlblog.org/2019/09/12/… Aug 19, 2011 at 16:35
  • Thanx Aaron, I'll keep that in mind. Apart from the default schema dbo. we also use audit. since April this year. It havn't crossed my mind since yesterday that you can get into this kind of trouble. This could be a real issue, since we have hundreds of functions and 500 or so stored procedures. I guess the best technique is to revise functions and procedures when I need to change them anyway. Nice blog, its now bookmarked :) Aug 20, 2011 at 5:50

1 Answer 1

11

Yes, SELECT..INTO creates a new table

You'd need this to add rows to an existing table

INSERT MessageQueue (MessageQueue, OneId, OtherId) 
SELECT MessageId = 9999, OneId, OtherId 
   from @TempTable
1
  • Thanx @gbn, you just saved me a couple of hours of confusion! Aug 19, 2011 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.