Copying data from staging table to multiple tables

Question

In SQL Server 2016 I have a staging table [S] with 8 columns. I want to copy that data to 3 tables [A], [B] and [C]. [A] has FK to [B] and [B] has FK to [C]. Columns 1-2 are to go to [c], column 3 to [a] and columns 4-8 to [B].

Can anyone tell me how I can do this? Efficiency is not paramount as only 5.5Krows.

Any help much appreciated.

edit:

Should have made it clearer:

• Tables a,b and c exist and have PK and FK's in place.
• c has a unique constraint on a concatination of col1 and col2.
• a,b, and c use the IDENTITY PK
• Data to be imported has been cleansed of duplicates.

Answer 1

So effectively you're splitting up a row from S over 3 tables? Why not use primary key from table S instead of "chained" key you want to use?

You'd still be able to join the data the way you want, you would also be able to join tables A and C without using B.

p.s. If you don't have a PK in S, or is in a shape you don't want to copy, you can still use a surrogate key in one table and reference it from other two.

here's a solution for table C, you can adjust it for other tables:

CREATE TABLE c (
    ID int PRIMARY KEY
  , col1 int
  , col2 int
)
INSERT INTO c(id,col1,col2)
SELECT
    ROW_NUMBER() OVER(ORDER BY s.PrimaryKey)
  , s.col1
  , s.col2
FROM s

or

INSERT INTO c(id,col1,col2)
SELECT
    s.PrimaryKey
  , s.col1
  , s.col2
FROM s

or (too implicit for my taste)

SELECT
    IDENTITY(int,1,1) AS ID
  , col1
  , col2
INTO c
FROM s
ORDER BY s.PrimaryKey

edit: If you're trying to get rid of redundancies then you could use DENSE_RANK() like this (add/remove rank for keys you need):

SELECT DISTINCT
    DENSE_RANK() OVER(ORDER BY col1) AS PK_a
  , s1.col1
INTO a
FROM s1

SELECT DISTINCT    
  DENSE_RANK() OVER(ORDER BY col2,col3) AS PK_b
  , DENSE_RANK() OVER(ORDER BY col1) AS FK_a
  , s1.col2
  , s1.col3
INTO b
FROM s1

SELECT DISTINCT
    DENSE_RANK() OVER(ORDER BY col4) AS PK_c
  , DENSE_RANK() OVER(ORDER BY col1) AS FK_a
  , DENSE_RANK() OVER(ORDER BY col2, col3) AS FK_b
  , col4
INTO c
FROM s1

Answer 2

I ended up using Entity Framework 6 BulkInsert tool. I found it easier to work with the model classes I already had defined. I just read all objects from staging into a list of staging objects before iterating over them and constructing a,b and c and finally saving them all in a single call. 5.5K rows in under 10secs.

Thanks for your assistance @Bistabil