How do I pull data from my old server to my SQL server?

Question

I want to write a code that transfers data from on server to my SQL Server. Before I put the data in, I want to delete the current data. Then put the data from one to the other. How do I do that. This is snippets from the code I have so far.

string SQL = ConfigurationManager.ConnectionStrings["SQLServer"].ToString();
string OLD = ConfigurationManager.ConnectionStrings["Server"].ToString();

SqlConnection SQLconn = new SqlConnection(SQL);
string SQLstatement = "DELETE * FROM Data";
SqlCommand SQLcomm = new SqlCommand(SQLstatement, SQLconn);
SQLconn.Open();

OdbcConnection conn = new OdbcConnection(OLD);
string statement = "SELECT * FROM BILL.TRANSACTIONS ";
statement += "WHERE (TRANSACTION='NEW') ";            
OdbcCommand comm = new OdbcCommand(statement, conn);
comm.CommandTimeout = 0;
conn.Open();


SqlDataReader myDataReader = SQLcomm.ExecuteReader();
while (myDataReader.Read())
{
    //...            
}
SQLconn.Close();
SQLconn.Dispose();

Answer 1

If you only need to import the data once, and you have a lot of data, why not use the "BULK INSERT" command? Link

Answer 2

T-SQl allows you to insert data from a select query. It would look something like this:

insert into Foo
select * from Bar;

As long as the field types align this will work - otherwise you will have to massage the data from Bar to fit the fields from Foo.

Answer 3

Depending on which version of SQL Server you are using, the standard solution here is to use either DTS (2000 and before) or SSIS (2005 and on). You can turn it into an executable if you need to, schedule it straight from SQL Server, or run it manually. Both tools are fairly robust (although SSIS much more so) with methods to clear existing data, rollback in case of errors, transform data if necessary, write out exceptions, etc.

Answer 4

If at all possible I'd try and do it all in SQL Server. You can create a linked server to your other database server. Then simply use T-SQL to copy the data across - it would look something like...

INSERT INTO new_server_table (field1, field2)
SELECT x, y
FROM mylinkedserver.myolddatabase.myoldtable

If you need to do this on a regular basis or clear out the data first you can do this as part of a scheduled task using the SQL Agent.

Answer 5

When you need to do this once, take a look at the database publishing wizard (just google) and generate a script which does everything.