I'm using SqlBulkCopy class in C# to copy data from one SQL Server database to another in a fast way. The databases are in different servers and their datatables don't have any PK, so the process gets more complicated.
The problem is that the query I'm using to select data from the original database gets duplicate rows and SqlBulkCopy cannot avoid insert duplicate records in destination database.
I cannot use SELECT * because it throws an OutOfMemoryException, so I do SELECT TOP X * and load that data into a DataTable. In each Datatable I can remove the duplicate records using C#, but then when I select the next TOP X, the first row selected may be equal to the last one that was in the previous DataTable and has been already inserted into the destination database. The DataTable variable is always the same, it is reloaded!
I want to avoid duplicate records to be inserted without create PK because it's not applicable to my case. I really need to use SqlBulkCopy because a fast copy is a system requirement. Any suggestion? Thank you in advance!
Don't use C#.
You can right click your origin database in SSMS and choose "Tasks" and then "Generate Scripts". Choose the table you want and use the wizard to generate your insert scripts. Then run these on your second database.
If this action need to be repeated you could set up a Linked Server between your two SQL Server instances, and then write an insert statement from one to the other in a Stored Procedure. You can then run this stored procedure whenever you need, or call it from C#.
If you want it to run regularly you could set up a Job on the database.