sqlsql-serverbulkinsertbulkupdate

BULK INSERT/UPDATE statement syntax error?

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.

Solution

  • 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.