question

Leo avatar image
Leo asked

Copy Table with Constraint

Hi,

Anybody know how to copy the table with constraints (including primary key, foreign key, index, etc..) ???

I used the following copy statement and it does copy ONLY the table with records.

Select *
Into T1
From T2

Any idea?

Thanks.

sql-server-2008t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Scripting of constraints is not an easy thing - there are several products available to do it - my schema comparison tool, Red Gate SQL Compare, Apex SQL Diff.

If you want to script out the constraints using T-SQL only, I'm doing a blog series on metadata at the moment.

Your other option is to use Powershell / C# / VB.NET and SMO.

None of them are particularly straightforward.

5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I don't know, SMO through powershell is pretty easy, especially for something like generating scripts for objects.
0 Likes 0 ·
you have a blog??? why didnt I know this?
0 Likes 0 ·
Need to add an article soon or Tom will downgrade my RockStar status, but yeah, I have a blog.
0 Likes 0 ·
@Grant - yeah I know, just the syntax isn't amazingly straightforward - compared to SELECT ... INTO at least!
0 Likes 0 ·
@Grant - I'm already reading yours, I didnt have Matt's on my list...
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

There are two types of command in T-SQL, DDL and DML.

DDL - Data Definition Language is the description of the objects in a database - Tables, Schemas, Procedures and Functions etc
DML - Data Management Language is the script that manipulate and queries the data in database tables.

SELECT * INTO Tab_Dest FROM Tab_Source is a piece of DML code that manages to create a table but only enough of the table to hold the data, it doesnt inspect the source table for any features.

In order to get the new table identical you will need to use a DDL script that creates the table and then use INSERT INTO ... SELECT * FROM ... to transfer the data.

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1, but there are two other types of command too - DCL - Data Control Language - i.e. permissions and TCL - Transaction Control Language...
0 Likes 0 ·
@Matt, fair cop. Was quietly ignoring them for the context of the question and to be honest I *always* forget TCL anyway!!
0 Likes 0 ·
@Fatherjack - I am a fairy and my name is nuff... Fair enough...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.