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
– Joe Enos, Commented Jul 15, 2013 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
– coredump, Commented Jul 15, 2013 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
– Lotok, Commented Jul 15, 2013 at 20:43
You could add a LinkedServer on your copy source server to export data, using INSERT INTO statement, onto another server. — dwonisch
– dwonisch, Commented Jul 15, 2013 at 20:43
See stackoverflow.com/questions/2216254/rollback-for-bulk-copy Sql Bulk Copy is your friend here — Roger Johansson
– Roger Johansson, Commented Jul 15, 2013 at 20:55

Fabian Bigler · Accepted Answer · 2013-07-15 20:56:15Z

2

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.

answered Jul 15, 2013 at 20:56

Fabian Bigler

10.9k6 gold badges52 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2013-07-16 05:11:45Z

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, 2013 at 5:11

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 15, 2013 at 20:39

Jay Patel

6471 gold badge6 silver badges14 bronze badges

Comments

Luke Franklin · Accepted Answer · 2013-07-16 11:55:56Z

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

paparazzo · Accepted Answer · 2013-07-18 13:41:56Z

0

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.

edited Jul 18, 2013 at 13:41

answered Jul 15, 2013 at 20:43

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Collectives™ on Stack Overflow

Get rows from database and insert them into another

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related