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 know the form

Select * into new_table from old_table

statement. Bur recently I found 2 posts here and here which use it like

SELECT * FROM NhlPlayer
INSERT INTO PlayerBackups

I find nothing in msdn, is that some new syntax or just a fault? I guess the later because

INSERT INTO PlayerBackups
SELECT * FROM NhlPlayer

would work fine.

share|improve this question
    
Both are wrong syntax. I've -1 in the first and left a comment on the second. –  gbn Sep 7 '11 at 5:41
    
Agreed with @gbn. If you try a SELECT FROM then INSERT INTO, SQL Server takes that as two different entities. In which the INSERT INTO is incorrect and missing pieces. –  Thomas Stringer Sep 7 '11 at 6:47
    
both posts have now been updated by their respective authors.. nice one @gbn –  Andrew Bickerton Sep 7 '11 at 15:10
2  
@Andrew Bickerton: Hurrah! I fixed something wrong on the interwebs xkcd.com/386 –  gbn Sep 7 '11 at 15:28

2 Answers 2

up vote 9 down vote accepted

Both posts are wrong syntax.

I've -1 in the first from SO and left a comment on the second.

Create a table

SELECT * INTO PlayerBackups FROM NhlPlayer

Inserts to an existing table

INSERT PlayerBackups SELECT * FROM PlayerBackups
share|improve this answer
SELECT *
INTO new_table
FROM old_table

The above creates a new table based off of the source's table structure. new_table, in this example, cannot exist or an error will be raised and the statement will not be committed.

INSERT INTO new_table_must_exist
SELECT *
FROM old_table

In this example, it copies data from old_table to new_table_must_exist. Here new_table_must_exist must already exist, and the data mappings have to be compatible.

share|improve this answer

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.