BULK INSERT/UPDATE statement syntax error?

Question

I am trying to bulk insert or update the data from a csv file to a pre-existing table in a database in SQL and I keep getting the error "Incorrect syntax near keyword 'BULK'" and I've tried everything I can find on google.

Here is the code I am working with:

CREATE VIEW [temp_table] AS
SELECT column_1, column_2...
FROM table;

BULK UPDATE [temp_table]
FROM 'C:\FileName.csv'
WITH(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);

I have tried both UPDATE and INSERT.

I have tried importing the data directly but that just came up with a load of permission errors that I cannot get round.

I tried doing it directly from the csv file but it runs into the issue that the id column is on the database but not on the csv and the id column automatically generates numbers.

Any advise is appreciated.

`BULK UPDATE` is not a T-SQL statement. Consider importing into a staging table. — Dan Guzman, Jul 06 '23 at 12:21
There's no BULK UPDATE. BULK INSERT is a *very* special type of operation that uses [minimal logging](https://learn.microsoft.com/en-us/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-ver16) - as rows are added, entire data pages are logged to the transaction log, not individual rows. This is possible when rows are inserted into an empty table or appended at the end of a table with data. — Panagiotis Kanavos, Jul 06 '23 at 12:27
UPDATE on the other hand modifies rows in the middle of an existing table, so each individual row change has to be logged. A `BULK UPDATE` command wouldn't offer any benefits. The alternative is to load the data into an empty staging table with `BULK INSERT` and then UPDATE the target using the staging table as source — Panagiotis Kanavos, Jul 06 '23 at 12:31
A quick read of the docs would have told you this doesn't work, there is no such command — Charlieface, Jul 06 '23 at 14:19

score 1 · Accepted Answer · edited Jul 08 '23 at 00:39

I prefer myself to use the BULK INSERT instead of UPDATE.

Could you try this?

Bulk insert data into a temp table:

CREATE TABLE #temp (column_1 type, column_2 type, ...);
BULK INSERT #temp FROM 'C:\\FileName.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

Update the target table using a JOIN on matching columns:

UPDATE t SET t.column_1 = temp.column_1, t.column_2 = temp.column_2 FROM table t JOIN #temp ON t.id = temp.id;

Also, if you have any permission issues, you should check with the database administrator to provide you with the necessary permissions.

BULK INSERT/UPDATE statement syntax error?

1 Answers1