CODEWITHSUNDEEP
sql-serverdatabasesql-server-2005copy
John B
John B

Reputation: 20350

How to programmatically copy a table in SQL Server, without the data?

I need to copy a table I already I have in a SQL server database, but I don't need the data contained in the source table. The examples I found only involve copying the data.

Here are the details:

  1. Copy the table structure only, not the data.
  2. Source and target tables are in the same database.
  3. Target table does not exist yet.
  4. Need to do this programmatically
  5. It would be nice to have any related properties of the table/columns be copied as well.

Upvotes: 8

Views: 7320

Answers (6)

marc_s
marc_s

Reputation: 754478

I bet with SMO you can do this no problem:

  • read the structure of your "old" table into in-memory variables
  • use this structure info to create the new table

I quickly found a few interesting articles that show at least part of the solution:

So basically it would boil down to something like this:

Server localServer = new Server("(local)");
Database testDB = localServer.Databases["test"];

Table myTable = testDB.Tables["TestFiles"];
myTable.Refresh();

Table newTable = new Table(testDB, "MyNewTableName");

foreach(Column col in myTable.Columns)
{
    Column newColumn = new Column(newTable, col.Name);

    newColumn.DataType = col.DataType;
    newColumn.Default = col.Default;
    newColumn.Identity = col.Identity;
    newColumn.IdentityIncrement = col.IdentityIncrement;
    newColumn.IdentitySeed = col.IdentitySeed;
    newColumn.Nullable = col.Nullable;

    newTable.Columns.Add(newColumn);
}

newTable.Create();

Of course, there are more properties on the "Column" which you might want to copy over, plus you might also want to copy indices, constraints etc. - extra work.

I'm stumped that there isn't an easier way to duplicate a "Column" object to a new one (something like a .Clone() method) to ease this - maybe it's not a top-priority scenario, I don't know....

Hope this helps!

Marc

Upvotes: 2

Ben M
Ben M

Reputation: 22492

If you're using .NET, you can use Server Management Objects:

var so = new ScriptingOptions();
so.Triggers = true;
so.DriForeignKeys = true;
so.DriDefaults = true;
so.DriAllConstraints = true;
so.DriAllKeys = true;
so.DriIndexes = true;
so.DriUniqueKeys = true;
so.DriPrimaryKey = true;
so.Indexes = true;
so.Default = true;
so.ClusteredIndexes = true;
so.IncludeDatabaseContext = true;
so.TargetServerVersion = SqlServerVersion.Version90;

var server = new Server("ServerName");
var db = server.Databases["DatabaseName"];
var stringColl = db.Tables["Table"].Script(so);

You'd need to replace the names of the table and associated objects (e.g. FK_OldTableName_xxx with FK_NewTableName_xxx) in the generated script:

var sb = new StringBuilder();
foreach(var s in stringColl)
{
    var r = s.Replace("OldTableName", "NewTableName");
    sb.AppendLine(r);
}

And then execute:

db.Execute(sb.ToString());

Note that this is pretty naive code: it will only work if the names of your constraints and keys follow the format: FK_OldTableName_xxx / CK_OldTableName_xxx .. if they have other names, you'd need to beef up the string replacement code, probably using Regexes to look for T-SQL object creation patterns (i.e. CREATE INDEX, FOREIGN KEY .. etc).

Upvotes: 1

msvcyc
msvcyc

Reputation: 2593

select * into new_table from old_table where 1=0

Execute the above command programmatically.

Upvotes: 7

Adrian Godong
Adrian Godong

Reputation: 8911

Do you need to do this automatically (without manual interference)?

Otherwise, you can always use SQL Server Management Studio to generate CREATE scripts for the tables you want to copy and run it on the target database.

Right click on the object you want, pick generate script, and choose your script to generate.

Upvotes: 0

Jeff Hornby
Jeff Hornby

Reputation: 13640

In SQL Management Studio, right click on the table name and Script Table as | CREATE TO ... | New Query editor Window. That will give you a script that you can run under any database.

Upvotes: 1

MusiGenesis
MusiGenesis

Reputation: 75296

Script out the table, change the table name in the script, run the script.

Upvotes: 3

Related Questions