Import one csv file with data for multiple tables in MS SQL server

Question

I have the following tables (simplified for question):

Data:

Id
Serialnumber
Value
AddressId (ForeignKey)

Addresses:

Id
Street
ZipCode
City
...

Now I have a csv file which contains data, unfortunately for both tables:

Serialnumber,Value,Street,ZipCode,City, ...
12345,1140,Fakestreet 3,12345,New York
5678,830,Fakestreet 87,12345,New York

Is there any way to import csv files like that to Microsoft SQL server (2008)? The server itself has to insert the address first, then use the primary key of the Addresses table as foreign key in the Data table.

Use sql server import wizard to import data into sql server first in a staging table then write t-sql queries to insert data into final destination tables. — M.Ali
– M.Ali, Commented Oct 16, 2014 at 17:32

radar · Accepted Answer · 2014-10-16 17:35:04Z

2

You need to import the CSV file into a staging Table like below and then process it accordingly.

IF (OBJECT_ID('StagingData') IS NULL)
    CREATE TABLE StagingData
    (  
       SerialNumber int,
       Value int,
       Street varchar(256),
       ZipCode int,
       City varchar(256)

    )

TRUNCATE table StagingData

BULK
INSERT StagingData
FROM 'c:\csvtest.txt'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

answered Oct 16, 2014 at 17:35

radar

13.4k2 gold badges27 silver badges33 bronze badges

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

Comments

Ross Presser · Accepted Answer · 2014-10-16 17:31:14Z

0

If you have the space, I would definitely import the whole CSV to a staging table, then use SQL statements to insert to the destination tables. The staging table could be a #temp table or in a separate database created specifically for staging.
If not, then you could set up a dummy table with all the fields, and define your INSERT from the CSV to the dummy table. Then use an INSTEAD trigger that inserts into the actual destination tables rather than the dummy table. Remember that the trigger is going to receive multiple inserts in the same batch, and so it needs to handle multiple inserts. So capture the Address primary keys to a @table variable.

answered Oct 16, 2014 at 17:31

Ross Presser

6,3082 gold badges38 silver badges73 bronze badges

Collectives™ on Stack Overflow

Import one csv file with data for multiple tables in MS SQL server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest