How to duplicate a SQL Server 2000 table programat

Question

I want to backup a table saving the copy in the same database with another name. I want to do it programatically using .NET 2.0 (preferably C#). Someone can point me what should I do?

Answer 1

One way to do this would be to simply execute a normal query this way using INTO in SQL:

SELECT * 
INTO NewTableName 
FROM ExistingTableName 

This automatically creates a new table and inserts the rows of the old one.

Another way would be to use SqlBulkCopy from the System.Data.SqlClient namespace. There is a nice CodeProject article explaining how to do this:

SQL Bulk Copy with C#.Net

Programmers usually need to transfer production data for testing or analyzing. The simplest way to copy lots of data from any resources to SQL Server is BulkCopying. .NET Framework 2.0 contains a class in ADO.NET "System.Data.SqlClient" namespace: SqlBulkCopy. The bulk copy operation usually has two separated phases.

In the first phase you get the source data. The source could be various data platforms such as Access, Excel, SQL.. You must get the source data in your code wrapping it in a DataTable, or any DataReader class which implements IDataReader. After that, in the second phase, you must connect the target SQL Database and perform the bulk copy operation.

The bulk copy operation in .Net is a very fast way to copy large amount of data somewhere to SQL Server. The reason for that is the Bulkcopy Sql Server mechanism. Inserting all data row by row, one after the other is a very time and system resources consuming. But the bulkcopy mechanism process all data at once. So the data inserting becomes very fast.

The code is pretty straightforward:

// Establishing connection

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(); 
cb.DataSource = "SQLProduction"; 
cb.InitialCatalog = "Sales"; 
cb.IntegratedSecurity = true;
SqlConnection cnn = new SqlConnection(cb.ConnectionString);  

// Getting source data

SqlCommand cmd = new SqlCommand("SELECT * FROM PendingOrders",cnn); 
cnn.Open(); 
SqlDataReader rdr = cmd.ExecuteReader(); 

// Initializing an SqlBulkCopy object

SqlBulkCopy sbc = new SqlBulkCopy("server=.;database=ProductionTest;" +
                                  "Integrated Security=SSPI"); 

// Copying data to destination
sbc.DestinationTableName = "Temp"; 
sbc.WriteToServer(rdr); 

// Closing connection and the others

sbc.Close(); 
rdr.Close(); 
cnn.Close(); 

Answer 2

Depending on how many records in the table this could be a very bad idea to do from C# and the user interface.

For a small table use the following SQL Create table table2 (field1 int, field2 varchar(10)) --use the actual field names and datatypes of course)

insert into table2 (field1, field2) select field1, field2 from table1

I suggest the create table to create it once and then the insert so that you can add records to the table multiple times. Select into only will work once.

Answer 3

Just send this query to the server:

SELECT * INTO [BackupTable] FROM [OriginalTable]

This will create the backup table from scratch (an error will be thrown if it already exists). For large tables be prepared for it to take a while. This should mimic datatypes, collation, and NULLness (NULL or NOT NULL), but will not copy indexes, keys, or similar constraints.

If you need help sending sql queries to the database, that's a different issue.

Answer 4

You could use the SQL Server Management Objects (SMO). You could make a copy of a database (data and schema). There are a lot of options you can set. The following example copies the entire database:

// Connect to the server
Server server = new Server(".");

// Get the database to copy
Database db = server.Databases["MyDatabase"];

// Set options
Transfer transfer = new Transfer(db);
transfer.CopyAllObjects = true;
transfer.DropDestinationObjectsFirst = true;
transfer.CopySchema = true;
transfer.CopyData = true;
transfer.DestinationServer = ".";
transfer.DestinationDatabase = "MyBackupDatabase";
transfer.Options.IncludeIfNotExists = true;


// Transfer Schema and Data
transfer.TransferData();

You can find the documentation of the Transfer Class on MSDN.

Answer 5

At the very least, you could do "SELECT * INTO NEWTable FROM OldTable".

Do you want to create all the indexes/constraints etc?

EDIT: Adding to splattne's comments, you will have to get the handle to Table instance of the table you wish to copy. Use the Script method to get the script it will generate. Modify the script string to replace old names with new names & run it on the DB.

EDIT2: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.table.table.aspx

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.tableviewtabletypebase.script.aspx