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

Question

_{Wednesday, April 13, 2011 2:56 PM}

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?

 

All replies (3)

_{Wednesday, April 13, 2011 3:03 PM ✅Answered | 1 vote}

>>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

---

_{Wednesday, April 13, 2011 3:00 PM}

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:17 PM}

you can also use SSMS to script the source table and do some "search and replace"