I am Creating a new table with Primary Key Constraints and a Non Clustered Index in that table.
I know, I would like to create another table with same structure and values as well keys and indexes also.
create table Dummy (id integer ,name varchar(20),salary integer
Constraint PK_Con_id primary key(id))
insert into Dummy values(11,'AAA',1000);
insert into Dummy values(12,'BBB',2000);
insert into Dummy values(13,'CCC',3000);
insert into Dummy values(14,'DDD',4000);
select * from Dummy;
create nonclustered index IX_Name
on Dummy(Name)
Now I am creating Dmy table but Keys and Constraints not reflect in Dmy table in SQL Server 2008 R2.
SELECT *
INTO Dmy
FROM Dummy
Best Answer
SELECT INTO is not going to do this for you, because while it maintains the column names and data types, it doesn't keep a lot of the other aspects of the table, such as constraints, indexes, etc. The only thing it really keeps outside of the columns is the IDENTITY property if one of the columns has it.
Right-click the original table in Object Explorer, and select Script Table As > Create To > New Query Window. You should have a CREATE TABLE script for the original table, including all the constraints, etc. Note that you may have to set some of these settings (Tools > Options > SQL Server Object Explorer > Scripting) to true in order to get all of the table attributes you want, so this may take some trial and error:
Now, just hand-modify that script to specify the new table name, and make sure to adjust all of the constraint and index names such that they are unique (how much work this will be will depend on whether you have an established naming convention or if you just take what SQL Server will throw at you).