locked
Saving primary key constraint when creating new table using SELECT INTO statement - SQL Server 2008 RRS feed

  • Question

  • Using SQL Server 2008:

    We have a main database - MAIN.   For monthly data extracts, I create new tables with relevant data in another database called EXTRACT.

    I use SELECT INTO statements to create the tables in the EXTRACT schema.  How do I preserve the primary key constraints in the EXTRACT tables?  Do I need to write separate queries to set them?

     

    Wednesday, April 13, 2011 2:56 PM

Answers

  • >>Do I need to write separate queries to set them?

    Yes. If you are using SELECT INTO

    SELECT <columnlist>

    INTO EXTRACT.TableName

    FROM MAIN.TableName

     

    GO

     

    ALTER TABLE EXTRACT.TableName ADD PRIMARY KEY(ColumnName)

    another option is to script the table you want to create with constraints and then use INSERT INTO ...SELECT
    • Marked as answer by KJian_ Tuesday, April 19, 2011 6:54 AM
    Wednesday, April 13, 2011 3:03 PM

All replies

  • Using SQL Server 2008:

    We have a main database - MAIN.   For monthly data extracts, I create new tables with relevant data in another database called EXTRACT.

    I use SELECT INTO statements to create the tables in the EXTRACT database.  How do I preserve the primary key constraints in the EXTRACT tables?  Do I need to write separate queries to set them?

     

    Wednesday, April 13, 2011 3:00 PM
  • >>Do I need to write separate queries to set them?

    Yes. If you are using SELECT INTO

    SELECT <columnlist>

    INTO EXTRACT.TableName

    FROM MAIN.TableName

     

    GO

     

    ALTER TABLE EXTRACT.TableName ADD PRIMARY KEY(ColumnName)

    another option is to script the table you want to create with constraints and then use INSERT INTO ...SELECT
    • Marked as answer by KJian_ Tuesday, April 19, 2011 6:54 AM
    Wednesday, April 13, 2011 3:03 PM
  • you can also use SSMS to script the source table and do some "search and replace"
    Wednesday, April 13, 2011 3:17 PM