how to copy sql table with primary key

Question

1.00/5 (1 vote)

See more:

I am using Query

select * into table 1 from table2 to copy table but it doesnt copy Primary key from

table2 in table1 as Primary key . It just copy all columns not constrains

Posted 7-Jun-15 22:51pm

Anynomus Anynomus

Add a Solution

Comments

Kornfeld Eliyahu Peter 8-Jun-15 5:28am

There is no single SQL command that can copy also table definitions (and not only table data) - you will have to do a fair amount of work to replicate the table...

Andy Lanng 8-Jun-15 6:06am

It makes sense that is doesn't copy constraints as it would not be able to insert into the pk column for eg.

You can get a query to create the constraints. all the constraint definitions are held in the sys tables.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2015-06-08T00:13:00

Solution 1

I found this and edited it down to show the primary key code.

The same is possible for all constraints but this is the simplest:

SQL

select   CASE WHEN tc.Constraint_Name IS NULL THEN ''
 ELSE 'ALTER TABLE '
  + so.Name + ' ADD CONSTRAINT '
  + tc.Constraint_Name  + ' PRIMARY KEY ' 
  + ' (' + LEFT(j.List, Len(j.List)-1) + ')'
  END
from    sysobjects so

left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')
and tc.TABLE_NAME = @tablename;

As you can see, it is not a simple task :S

Posted 8-Jun-15 0:13am

Andy Lanng

Comments

Anynomus Anynomus 8-Jun-15 7:04am

Thank you very much for your response but there should be any single query which can do what i want . can i use alter query after creating table ? :O

Andy Lanng 8-Jun-15 7:09am

well, No and yes. There is no single 'query' to perform these actions, but you can call the alter after the first query. You can have these in the same script / Stored Procedure, but you have to apply the first code first. Do that by writing the work 'GO' above the Alter query

Anynomus Anynomus 8-Jun-15 7:52am

I am doing this in Visual studio so it is ok ? if i write as follows

Query = "select * into table2 from table1 "
execute querry
query = "alter table2 Primarykey (columnName)"
execute query

Thank you very much for your response :D

Andy Lanng 8-Jun-15 8:11am

Yup - that'll work fine :)
You can actually select the above query to get the alter table query, or you can write it yourself if you know what it is :D

Please accept the solution to mark this question as answered :)