Copy data from one table to another table -different server.

Posted Date: 10 Aug 2012 Posted By:: sankar Member Level: Silver Member Rank: 1696 Points: 5 Responses: 2

Hi All,
I hava a table in sql server 2000 db , recently I cloned this db to another
server and started as production db. after two days only we came to know one of the table has missed its data. I dontknow how it has missed all the data for this particular table. now this table is having arround 200 records whihc is created last two days. I want the data should be copied from the old table (it is having 500000 records and located in another server) into new table (200 records). this table is having a column ID as Identity column .

insert into method will take more time...is there any way to do as quick as possible.

Thanks & Regards,
Sankar.A

Responses

#683857 Author: Laxmikant Member Level: Gold Member Rank: 168 Date: 10/Aug/2012 Rating: Points: 4
There are many ways to do it 1. Insert statement 2. SSIS packages 3. Use sqlbulkcopy if your table schema is same SQLBULKCopy is the easiest. you can use this like private static void ProductsCopy() { string strConnection = @"Server=localhost;Database=Northwind;Trusted_Connection=true"; // get the source data using (SqlConnection srcConnection = new SqlConnection(strConnection)) { SqlCommand srcCommand = new SqlCommand("SELECT * FROM Products"); srcCommand.Connection = srcConnection; srcConnection.Open(); SqlDataReader reader = srcCommand.ExecuteReader(); // open the destination data using (SqlConnection destConnection = new SqlConnection(strConnection)) { // open the connection destConnection.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection.ConnectionString)) { bulkCopy.BatchSize = 500; bulkCopy.DestinationTableName = "Products_New"; bulkCopy.WriteToServer(reader); } } reader.Close(); } }

#683922 Author: Ravindran Member Level: Diamond Member Rank: 3 Date: 10/Aug/2012 Rating: Points: 3
Hi, You can do it using bulk copy option to get data from new server and insert into that deleted table easily if you don't want to write code then use SSMS tools pack tool to get insert statement of any number of record in small time. Just run that insert statement in deleted table to insert into it. I have explained in details use of that tool in my below article please check this http://www.dotnetspider.com/resources/42982-How-create-insert-statement-script-for-SQL.aspx Regards N.Ravindran Your Hard work never fails

Post Reply

You must Sign In to post a response.

	Next : Unable to execute the package through sql server agent job
	Previous : How to update new row and sum of all the records in SQL SERVER
	Return to Discussion Forum
	Post New Message
	Category:

Related Messages

Follow us on Twitter: https://twitter.com/dotnetspider

Active Members
Today

Last 7 Days

more...

Awards & Gifts

Email subscription