Get rows from database and insert them into another

Question

I have a table from which I read using SqlCommand variable, executing ExecuteReader on it an the on the returned SqlDataReader object I am interating to get each row, from where I do operations on the string to perform on insertion in the new DB using also a SqlCommand variable and executing ExecuteNonQuery().

My question is if there is any more elegant/efficient way to do this ? Something like getting all the rows in one command and insert them all at once or anything else that is better than the current flow.

UPDATE One important thing is that the tables are in different databases on different servers.

Totally depends on your needs. Sounds like you could potentially do this all in one big SQL statement (`insert Something select Foo from SomethingElse`), in which case it would execute faster if you wrote it up in a proc and just called the proc. But you'd lose a little bit of control over just exactly what you can do with the data. Also depends on how many records you're working with, how fast you actually need it to run, etc. — Joe Enos, Jul 15 '13 at 20:40
@JoeEnos As I stated in the update, the tables or on different servers, so I cannot do only one big `SqlCommand` since they are associated with different connections. — coredump, Jul 15 '13 at 20:42
If you right click the database in SSMS and choose `tasks--> import` you can choose SQL server/database source and destination. Is c# a means and an end or a requirement? — Lotok, Jul 15 '13 at 20:43
You could add a LinkedServer on your copy source server to export data, using `INSERT INTO` statement, onto another server. — dwonisch, Jul 15 '13 at 20:43
Based on your edit, the proc idea is out. For me, the remaining question is whether to keep both connections open and do the select then insert one at a time, vs. retrieving everything upfront and running everything later. Again, pros and cons either way, depending on how many records, how you want to handle when things go wrong, etc. But assuming everything is working ok, I don't see a problem with doing it how you're describing. — Joe Enos, Jul 15 '13 at 20:44
See http://stackoverflow.com/questions/2216254/rollback-for-bulk-copy Sql Bulk Copy is your friend here — Roger Johansson, Jul 15 '13 at 20:55
@coredump Are the tables structure different? It it for a Backup, or for integration between two different systems? — Guilherme de Jesus Santos, Jul 15 '13 at 20:57
@RogerAlsing Thanks. That is the solution. Post the answer and I will accept it. — coredump, Jul 15 '13 at 23:54

score 2 · Answer 1 · answered Jul 15 '13 at 20:56

I would suggest: Try it in one step.

INSERT INTO yourDataBase1.yourTable1 VALUES (Column1, Column2, ...)
SELECT Column1, Column2, ... FROM yourDataBase2.yourTable1

Benefits:

Less traffic over ethernet
Fast compared to other solutions (e.g. stored procedures)

I would do this transaction-based in your C# application (Transmit / Rollback). So if something goes wrong it won't insert just half of your rows.

score 1 · Answer 2 · edited Jul 16 '13 at 05:11

1

You could write a stored procedure or SqlCommand which looks something like

INSERT INTO DB1.dbo.TestTable(FirstName, LastName)
SELECT RemoteTable.FirstName, RemoteTable.LastName FROM
OPENROWSET('SQLOLEDB',
           'Server=x.x.x.x;Trusted_Connection=yes;user_id=xxxx;password=xxxx',
           'SELECT FirstName, LastName FROM DB2.dbo.TestTable' ) as RemoteTable

edited Jul 16 '13 at 05:11

marc_s

732,580
175
1,330
1,459

answered Jul 15 '13 at 20:39

Jay Patel

657
1
6
14

paparazzo · Answer 3 · 2013-07-18T13:41:56.827

If the manipulation is something you need C# .NET for then you have two options

1) build up and insert multiple rows using the syntax:
values (), ()
you can insert more than one row in a single statement - this will make it less chatty
best to keep the total values less than 1000 for each insert

2) use a TVP (Table Value Parameter)

In you can perform the manipulation in TSQL then just an insert.
Or use CLR so you can use you C# in the insert.

Via Linked Server you can traverse servers.
You just use a 4 part name in the insert / select.

score 0 · Answer 4 · answered Jul 16 '13 at 11:55

I'm going to post a mix of two answers Jay Patel is the best method of doing this as you would like to use different servers and databases.

but wrapping it in a try catch and a transaction will be the best idea so i will take Jay Patel's Method and wrap it so there is some fail over for whatever reason it fails.

BEGIN TRAN -- START THE TRANSACTION BUT DONT PHSICALLY PUT THE ROWS IN THE DATABASE UNTIL COMMIT
BEGIN TRY
      INSERT INTO DB1.dbo.TestTable(FirstName, LastName)
    SELECT RemoteTable.FirstName, RemoteTable.LastName FROM
    OPENROWSET('SQLOLEDB',
               'Server=x.x.x.x;Trusted_Connection=yes;user_id=xxxx;password=xxxx',
               'SELECT FirstName, LastName FROM DB2.dbo.TestTable' ) as RemoteTable
END TRY
BEGIN CATCH
ROLLBACK -- IF THE INSERT FAILS DONT INSERT ANYROWS INTO THE DATABASE
END CATCH

COMMIT --USED FOR COMMITING THE ROWS TO THE DATABASE THEY ARE NOT ACTUALLY IN THE DATABASE UNTIL THIS HAS BEEN COMMITED

Get rows from database and insert them into another

4 Answers4