How to create a log table for when does the last import has been done and also for Query for split the records in to 1000 while importing. to avoid the bulk insert For example : If the data contains 10000 records means i want to import 1000,.... records one by one and finally 10000 should be updated after the import fully completed


Create a log table for your use such as:

CREATE TABLE dbo.ImportLogging
 ImportFileName NVARCHAR(200),
 ImportBeginTime DATETIME,
 ImportEndTime DATETIME);

That assumes that you want to save the file name, begin time, and end time. That is up to you, of course. I would log the ImportBeginTime at the start of the import, then add the ImportEndTime at the end. That way you will have some tracking even if the import fails.

And, assuming that you are importing from a file, I would actually recommend using BULK INSERT because you can set the batch size and let BULK INSERT handle the issue for you. Much better than writing it yourself. E.g.

BULK INSERT dbname.schemaname.tablename
FROM 'G:\Import\Importfile.txt'

This will cause the BULK INSERT to process 1000 rows per transaction, commit the transaction, then start the next transaction until the file is processed. Of course, you can change the 1000 to any number you choose.

If you are importing data from another database (either on the same server or across a linked server) then you may need to write some looping code to insert 1000 rows (or 1 row, if that is what you want) at a time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.