Geek Answers Handbook

How to programmatically duplicate a SQL Server 2000 table using .NET 2.0?

I want to back up a table storing a copy in the same database with a different name. I want to do this programmatically using .NET 2.0 (preferably C #). Can someone tell me what to do?

+2
source share
5 answers

Just send this request to the server:

SELECT * INTO [BackupTable] FROM [OriginalTable]

This will create a backup table from scratch (an error will be selected if it already exists). For large tables, prepare for this to take some time. This should mimic data types, sorting, and NULLness (NULL or NOT NULL), but not copy indexes, keys, or similar restrictions.

If you need help sending sql queries to a database, this is another problem.

+5

- , INTO SQL:

SELECT * 
INTO NewTableName 
FROM ExistingTableName

.

SqlBulkCopy System.Data.SqlClient. CodeProject, , :

SQL Bulk Copy #.Net

. SQL BulkCopying..NET Framework 2.0 ADO.NET "System.Data.SqlClient": SqlBulkCopy. .

. , Access, Excel, SQL.. , DataTable DataReader, IDataReader. , SQL .

.Net - SQL Server. Bulkcopy Sql . , - . . , .

:

// 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();
+4

SQL Server (SMO). ( ). , . :

// 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();

MSDN.

+1

, # .

SQL table2 (field1 int, field2 varchar (10)) - , )

2 (1, 2) 1, 2 1

, , . .

0

More articles:


All Articles