Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
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.com/blogs/aaron_bertrand/archive/2009/10/11/… –  Aaron Bertrand Aug 19 '11 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 '11 at 5:50

1 Answer 1

up vote 9 down vote accepted

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
share|improve this answer
    
Thanx @gbn, you just saved me a couple of hours of confusion! –  Benny Skogberg Aug 19 '11 at 9:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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