Question Import large text file every day

[speshulk92683]

I have a requirement that I import a fairly large text file (~1 mil records with about 15 columns) every day. I am currently using a Data Table to set it up and then use a Truncate and then Bulk Copy, but I am trying to improve upon the process to make it run faster. I am running SQL 2008 and heard there was some sort of Merge ability, but I could not find any information on how to set that up. Does anyone know anything about this or how to set it up?

The data I receive every day is always all old records with a few more that were processed for that day and some of the old data in the file could have been updated or deleted as well.

Edit: If the merge isn't a valid thing for me to set up, I would love some suggestions on how to set up this import process to make it run faster and keep my indexes in place.

 

[speshulk92683]

Sorry, I thought the Merge was something inside of VB.NET. By moving me here, I am to assume it's a SQL only thing. Haha

 

[Herman]

Just diff the two tables. First, read the file in a datatable, and do the same for your database table. Then LEFT JOIN the tables, using a hash of the rows to match them. Only keep the rows that are different, then insert or update them back into your original table.

For example:

SELECT * FROM OldTable;
SELECT * FROM NewTable;

SELECT *
FROM ( SELECT ID, CHECKSUM(*) AS RowChecksum FROM NewTable ) nt
LEFT JOIN ( SELECT ID, CHECKSUM(*) AS RowChecksum FROM OldTable ) ot ON nt.RowChecksum = ot.RowChecksum
WHERE ot.RowChecksum IS NULL;

Keep in mind CHECKSUM(*) is not infaillible if you have big rows or a lot of them. It uses CRC32. It would be preferable to generate a MD5 hash with HASHBYTES instead.

 

[JohnH]

Sorry, I thought the Merge was something inside of VB.NET. By moving me here, I am to assume it's a SQL only thing. Haha

I think you misunderstand these forums. ALL forums here are for VB.Net related discussions exclusively, that is why this place is called VB.Net Forums. The Database section contains sub-forums for database related VB.Net discussions, and the SQL Server sub-forum for example is the VB.Net forum for discussions pertaining to SQL Server.

 

[speshulk92683]

Thank you all for the clarifications. I now see how to use that merge ability and understand the purpose of the forums. Was a big help!