MSSQL-Creating a table as duplicate of another table

I was very much frustrated with the simple but syntactical change between Oracle and MS-SQL Server. I was trying to just create a table with the normal Oracle syntax in MS-SQL Server:

CREATE TABLE test AS SELECT * FROM existing_table;

I got an error from MS-SQL and then I had to call up some experts on MS-SQL to understand how it is done in MS-SQL.

The syntax for creating such a duplicate table of an existing one in MS-SQL is as follows:

SELECT *
INTO {new_table}
FROM {existing_table}
WHERE {if_clause if any};

Thus our example can be translated to as:

SELECT *
INTO test
FROM existing_table;

This short article is to just keep up the difference between MS-SQL and Oracle. Oracle professionals may sometimes need to work in MS-SQL, and this kind of tips on MS-SQL comes handy in times of trouble.
Posted by Labels: ,

10 comments :

  1. UnknownMarch 21, 2009 at 12:12 AM

    How might I use wildcards to duplicate BUNCH of tables at once? Example:

    SELECT *
    INTO phpbbbax_*
    FROM phpbb_*

    ReplyDelete
  2. Anantha NarayananMarch 21, 2009 at 12:41 PM

    Try this

    USE YourDBName
    GO
    SELECT *
    FROM sys.Tables
    where <>
    GO

    ReplyDelete
  3. UnknownDecember 1, 2009 at 11:19 AM

    Select * into new_table_name from existing_table_name where .......

    is working without any problem. To have only the table structure, the where clause can be used in such way that no records will be generated.

    Sunil Nair

    ReplyDelete
  4. giljunieDecember 14, 2010 at 2:01 AM

    This is a great tool, but I'm trying to combine 2 tables by an ident ID.

    select * into blocks_combined_new
    from (
    select * from locations_new l
    inner join
    blocks_new b
    on b.locid = l.locid)

    This throws this error:

    >[Error] Script lines: 1-7 --------------------------
    Line 7: Incorrect syntax near ')'.

    Help would be greatly appreciated!

    ReplyDelete
  5. giljunieDecember 14, 2010 at 3:36 AM

    Nevermind, I figured it out.

    ReplyDelete
  6. Anantha NarayananDecember 14, 2010 at 2:27 PM

    Sorry for not able to help you. But Glad that you solved the problem and commented here.

    ReplyDelete
  7. AnonymousApril 10, 2011 at 8:20 AM

    Hi Giljuinie,

    Try this. Put 'A' after the bracket.

    select * into blocks_combined_new
    from (
    select * from locations_new l
    inner join
    blocks_new b
    on b.locid = l.locid) A

    ReplyDelete
  8. AnonymousMarch 8, 2012 at 4:58 PM

    select * into New_Table from Old_Table

    suresh

    ReplyDelete
  9. AnonymousSeptember 14, 2012 at 2:46 AM

    What if i want to create a table with all the constraints and indexes ?

    ReplyDelete
  10. Anantha NarayananSeptember 14, 2012 at 11:31 AM

    Sorry, I will not be able to help you more in MS SQL, as I barely know it.

    ReplyDelete

Subscribe to: Post Comments ( Atom )