Copy Table with Constraint


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?


more ▼

asked May 07, 2010 at 11:23 AM in Default

avatar image

1.6k 55 59 62

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered May 07, 2010 at 11:37 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

I don't know, SMO through powershell is pretty easy, especially for something like generating scripts for objects.

May 07, 2010 at 11:40 AM Grant Fritchey ♦♦

you have a blog??? why didnt I know this?

May 07, 2010 at 11:43 AM Fatherjack ♦♦

Need to add an article soon or Tom will downgrade my RockStar status, but yeah, I have a blog.

May 07, 2010 at 11:52 AM Grant Fritchey ♦♦

@Grant - yeah I know, just the syntax isn't amazingly straightforward - compared to SELECT ... INTO at least!

May 07, 2010 at 11:52 AM Matt Whitfield ♦♦

@Grant - I'm already reading yours, I didnt have Matt's on my list...

May 07, 2010 at 12:01 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 07, 2010 at 11:41 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

+1, but there are two other types of command too - DCL - Data Control Language - i.e. permissions and TCL - Transaction Control Language...

May 07, 2010 at 11:54 AM Matt Whitfield ♦♦

@Matt, fair cop. Was quietly ignoring them for the context of the question and to be honest I always forget TCL anyway!!

May 07, 2010 at 12:03 PM Fatherjack ♦♦

@Fatherjack - I am a fairy and my name is nuff... Fair enough...

May 07, 2010 at 06:33 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 07, 2010 at 11:23 AM

Seen: 3947 times

Last Updated: May 07, 2010 at 11:47 AM

Copyright 2018 Redgate Software. Privacy Policy