CODEWITHSUNDEEP
sql-serverssis
goofyui
goofyui

Reputation: 3492

How to Import data to a SQL Server table which has relation id column?

I have business records in my staging table. I have to import the records from the staging table to business table. Business table has a Category Id column.

In the staging table, I have business categories such as Restaurants, Grocery, Hotels, Shopping centers etc..

Is there a way to insert the records in business table with the correct Category Id?

Upvotes: 0

Views: 195

Answers (2)

Akeel
Akeel

Reputation: 121

You can drop the foreign key before the insert and reapply it after the insert.

You might also have an identity column in your table, so you will need to run the following if you want the identity values to be exactly the same.

SET IDENTITY_INSERT dbo.business ON

Do the insert and then set IDENTITY_INSERT off again: 

SET IDENTITY_INSERT dbo.business OFF

Upvotes: 1

Joanna
Joanna

Reputation: 54

You can JOIN the staging table with the Business table.

INSERT INTO(s.Column1, s.Column2, b.Category_ID)
    (SELECT s.Column1, s.Column2, b.Category_ID
       FROM StagingTable s
       JOIN BusinessTable b
         ON s.Category = b.Category_DESC)

Upvotes: 1

Related Questions