4

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.

Improve this question
4
  • Both are wrong syntax. I've -1 in the first and left a comment on the second.
    – gbn
    Commented Sep 7, 2011 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
    Commented Sep 7, 2011 at 6:47
  • both posts have now been updated by their respective authors.. nice one @gbn
    – Andrew Bickerton
    Commented Sep 7, 2011 at 15:10
  • 2
    @Andrew Bickerton: Hurrah! I fixed something wrong on the interwebs xkcd.com/386
    – gbn
    Commented Sep 7, 2011 at 15:28

2 Answers 2

Reset to default
9

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
Improve this answer
3 
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.

Improve this answer

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.