Summary
I have a requirement to modify the content of Database based on some input .txt file that modify thousands of records in database, for each being a business transaction (daily around 50 transaction will performed).
My application will read that .txt file and perform the modification to data in SQL Server database.
The current application that imports the data from DB and perform the data modification in memory (DataTable) and later after that push back to database it does so using SqlBulkCopy into a SQL Server 2008 database table.
Does anyone know of a way to use SqlBulkCopy while preventing duplicate rows, without a primary key? Or any suggestion for a different way to do this?
Already implemented and dropped for performance issues.
Before this I was using SQL statements was generated automated for data modifications but it's really slow, so I thought of loading complete database table into a DataTable (C#) memory perform look up and modifications and accept the changes to that memory ...
One more approach to implement, give me some feedback about my new approach please right me if I am wrong ..
Steps
- load to database table into C#
DataTable(fillDataTableusingSqlDataAdapter) - Once
DataTableis in memory, perform data modifications on it - Load again the base table in database and compare in memory prepare the non-existing records and finally perform insert.
- push the
DataTableto memory using Bulk insert
I cant have Primary key!!!!
Please give me any suggestions for my workflow. and whether i am in right approach to deal my problem?.
