SQL Server 2014 or 2016 is it possible when using BULK INSERT from a flat file to also include some type of UPDATE statement during the same process?

Question

Hello, sorry if this dumb/strange question... in SQL Server 2014 or 2016 is it possible when using BULK INSERT from a flat file to also include some type of UPDATE statement during the same process?

Basically is it possible to populate target table within one step or would it require two separate steps like BULK INSERT first and then another step afterwards that runs the UPDATE statement?

Thanks in advance.

Answer 1

Hi @techresearch7777777 ,

Quote a reply from an old similar thread SQL Server Bulk Insert Update from CSV

Not directly, no. You need to BULK INSERT into a staging table and then UPDATE the existing records and INSERT the missing records. I would try with a local temp table (i.e. #TableName) first.

Technically speaking, you could probably do either of the following (both of which use OPENROWSET):

•　Skip the staging table and use OPENROWSET(BULK...) for the UPDATE and then INSERT queries. This would have to be tested, though, to see if the cost of reading the file twice is worth the savings of not having to read it into a temp table (which just writes it back to disk). It is possible that using the staging table might still be better since the first query, the UPDATE, might auto-create some statistics that would benefit the second query, the INSERT, since that query will need either a LEFT JOIN or a WHERE NOT EXISTS.

•　MERGE along with OPENROWSET(BULK...), but MERGE has some "issues" so I wouldn't try this.

Hope this could help you.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Thanks everyone for all of your great informative helpful replies.

Answer 3

Rather than using BULK INSERT, you can use

OPENROWSET(BULK) which you can use a table source in an UPDATE statement. That would be something like this:

   UPDATE tbl  
   SET    col1 = B.val1,  
          col2 = B.val2,  
          ...  
   FROM  tbl t  
   JOIN OPENROWSET (BULK 'C:\temp\data.data', FORMAT = 'C:\temp\data.fmt') AS B ON t.keycol = B.idcol  

You need to have a format file, and the column names are taken from the format file.

As whether this is a good idea... Maybe, if you know what you're doing. It's certainly easier to troubleshoot if you load the data into an intermediate table first.

Answer 4

It requires 2 commands/steps.

You will be happier if you always import flat files into a "stage" table formatted the same as the source file with all varchar fields and then use MERGE or INSERT/UPDATE to update the target table exactly how you want. BULK INSERT has many limitations and possible errors with data conversion, which can be resolved easily in TSQL.