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 (ImportID INT IDENTITY, 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' WITH (BATCHSIZE = 1000);
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.