1. Make SerialNo PK and CardID unique and generate a typed dataset for it
2.Fill the table with data from the database if there is data
3. Generate the rows in a loop and add them to the DataTable. If the CardID is duplicated catch the error and try again until you have all your 30k cards generated. (in this way you'll not have any duplicates)
4.Get an array of data rows (DataRow[]) from the table that contains the newly inserted rows.
5.Use SQLBulkCopy to insert them into the database. Here is a sample of how to work with it:
Your biggest performance hit will be when you generate the cards. Try to tweak that part until you get an acceptable result. But by using SQLBulkCopy the whole process will complete faster.
Please click 'Mark as Answer' if my reply has assisted you
raduenuca
All-Star
24675 Points
4250 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 01:04 PM|LINK
I suggest you do the following:
1. Make SerialNo PK and CardID unique and generate a typed dataset for it
2.Fill the table with data from the database if there is data
3. Generate the rows in a loop and add them to the DataTable. If the CardID is duplicated catch the error and try again until you have all your 30k cards generated. (in this way you'll not have any duplicates)
4.Get an array of data rows (DataRow[]) from the table that contains the newly inserted rows.
5.Use SQLBulkCopy to insert them into the database. Here is a sample of how to work with it:
http://www.codeproject.com/KB/database/TransferUsingSQLBulkCopy.aspx
Your biggest performance hit will be when you generate the cards. Try to tweak that part until you get an acceptable result. But by using SQLBulkCopy the whole process will complete faster.
Radu Enuca | Blog