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
Improve this question
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/…
    – Aaron Bertrand
    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 :)
    – Benny Skogberg
    Aug 20, 2011 at 5:50

1 Answer 1

Reset to default
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
Improve this answer
1
  • Thanx @gbn, you just saved me a couple of hours of confusion!
    – Benny Skogberg
    Aug 19, 2011 at 9:02

Your Answer

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

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