Skip to content
Advertisement

Insert CSV file to already existing table within SQL Server

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:Testtest1.csv'
WITH
   (
   rowterminator='n',
   fieldterminator=','
   )

Advertisement

Answer

You can do OpenRowSet:

INSERT INTO dbo.Backup$
        
SELECT * from OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; HDR=YES; 
Database=C:User_Namefile_you_wish_to_insert.xlsx',[Sheet1$]);
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement