bulk insert with file format with manual values for null fields

Question

I would like to create a stored procedure that imports a txt file into an existing table using a format file and also update certain fields that will be null in the import. The import file will just update one column and I need to manually assign values for the other columns.

bulk insert QueryData from 'E:\TrackInputLogs\NMSLog.txt' 
with(FORMATFILE = 'E:\TrackInputLogs\NMSFormat.fmt',  
ROWTERMINATOR='\n')  

I need to update the following columns as follows at the same time

dateimported = getdate() 
idkey = asdfg 
importmethod = manual 

oh by the way, this is an existing table and I do not want to delete current contents but also the import will update the fields where the idkey matches the values associated with the file contents.

Answer 1

Hello ,

Usually, this kind is met in the SQL Server Tools Forum ( with the use of bcp ).But BULK INSERT is mainly found in the TRANSACT-SQL Forum. It is why i propose you that a moderator moves your thread towards the TRANSACT-SQL Forum. I could do it, but i prefer to wait for the agreement of the original poster  (OP), except when the OP is in an emergency state.

We are waiting for your feedback to try to help you more efficiently.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.