Insert CSV file to already existing table within SQL Server

Question

I have a .csv file that I must update to my already existing table within SQL Server. Is there a query other than a BULK INSERT that I can use for this?

Here is the database and table: Backup Database - dbo.Backup$ table

Here is the sample data that is already in the dbo.Backup$ table

  Location  Name
  ga        John
  pa        Sally

This is the .csv file data: (test1.csv)

  Location Name
  ca       Jan
  ky       Bill

Desired output:

  Location  Name
  ga        John
  pa        Sally
  ca        Jan
  ky        Bill

The .csv file has the same columns. I just need to update my existing dbo.Backup$ table with the new .csv file. I am thinking I can perform a BULK INSERT command to do this, however, the column names keep importing as well.

This is what I am doing:

BULK INSERT test
   FROM 'C:\Test\test1.csv'
WITH
   (
   rowterminator='\n',
   fieldterminator=','
   )

use FIRSTROW = 2

Squirrel
– Squirrel

2020-09-23 05:49:05 +00:00
Commented Sep 23, 2020 at 5:49 — Squirrel
– Squirrel, Commented Sep 23, 2020 at 5:49
Ok thank you I think this works

Lynn
– Lynn

2020-09-23 06:12:50 +00:00
Commented Sep 23, 2020 at 6:12 — Lynn
– Lynn, Commented Sep 23, 2020 at 6:12

Preben Huybrechts · Accepted Answer · 2020-09-23 05:02:22Z

1

From the documentation

Skipping headers is not supported by the BULK INSERT statement.

What you can do to bypass this:

Create a temp table with the same structure,
BULK INSERT into the temp table
Remove the first row of the temp table
SELECT INTO the "real" table from the temp table.

Or if this is an action you need to do on a regular basis, build an SSIS package to load the CSV into the table.

answered Sep 23, 2020 at 5:02

Preben Huybrechts

6,1972 gold badges30 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lynn Over a year ago

I think you can do FIRSTROW = 2

Lynn · Accepted Answer · 2021-02-06 01:03:24Z

0

You can do OpenRowSet:

INSERT INTO dbo.Backup$
        
SELECT * from OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; HDR=YES; 
Database=C:\User_Name\file_you_wish_to_insert.xlsx',[Sheet1$]);

answered Feb 6, 2021 at 1:03

Lynn

4,4286 gold badges35 silver badges67 bronze badges

Collectives™ on Stack Overflow

Insert CSV file to already existing table within SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related