CODEWITHSUNDEEP
c#.netsql-server
Arnet11
Arnet11

Reputation: 79

Copy data from a table and update it

I have a table tblSource in SourceDB(SQL Server DB) and tblTarget in TargetDB(SQL Server DB). Data from tblSource has to be moved to tblTarget. tblSource has bit field to indicate which data is moved to tblTarget, so when row is copied to tbltarget this flag has to be set. I need to do it in C#, still suggestions in T-SQL are welcomed. My question is what all are possible solution and which will be best approach?

Upvotes: 0

Views: 135

Answers (4)

arun.passioniway
arun.passioniway

Reputation: 133

Merge will work for you if in SQL Server 2008. OUTPUT will work for you with SQL Server 2005+.

You need to Update the record to set your bit flag and OUTPUT INSERTED.* into your destination table.

You can consider outputting selected records only if you are planning to insert selected records to your destination table.

This is good in terms of performance as this technique will require SQL Server to traverse the record only once.

Check these links for how OUTPUT is used.

http://msdn.microsoft.com/en-us/library/ms177564.aspx && http://blog.sqlauthority.com/2007/10/01/sql-server-2005-output-clause-example-and-explanation-with-insert-update-delete/

Upvotes: 1

klennepette
klennepette

Reputation: 3196

Is there any reason why a simple INSERT is not an option?

INSERT INTO tblTarget (destcol1, destcol2) 
SELECT (sourcecol1, sourcecol2) FROM tblSource

Upvotes: 0

jcollum
jcollum

Reputation: 46613

I would use SQLBulkCopy class for this. I've used it in the past and had good luck with it. It's plenty fast and easy to use. There's plenty of sample code at that link to get you started.

Upvotes: 0

Paul Grimshaw
Paul Grimshaw

Reputation: 21044

You could use the TSQL MERGE statement, which would remove the need to keep a flag on each row.

This could be executed from C# if need be, or wrapped in a stored procedure. If they are in separate server instances, you can create Linked server.

Upvotes: 0

Related Questions