CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
mosquito87
mosquito87

Reputation: 4440

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

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.

Upvotes: 3

Views: 3322

Answers (2)

radar
radar

Reputation: 13425

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

Upvotes: 2

Ross Presser
Ross Presser

Reputation: 6255

  1. 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.
  2. 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.

Upvotes: 0

Related Questions