CODEWITHSUNDEEP
c#.netsql-serverdatabase
coredump
coredump

Reputation: 3117

Get rows from database and insert them into another

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.

Upvotes: 0

Views: 2583

Answers (4)

paparazzo
paparazzo

Reputation: 45106

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.

Upvotes: 0

Luke Franklin
Luke Franklin

Reputation: 355

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

Upvotes: 0

Jay Patel
Jay Patel

Reputation: 647

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

Upvotes: 1

Fabian Bigler
Fabian Bigler

Reputation: 10915

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.

Upvotes: 2

Related Questions