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.

  • 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
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, privacy policy and cookie policy

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