Splitting data into two tables, in one go

Question

I am using SQL Server 2008 and have a problem which I don't know how to solve without using many temporary tables and an unreliable join.

Table 1 contains 6 columns of data, which is then split into two tables. Col1 to Col3 goes into Table 2, and Col4 to Col6 goes into Table 3. Getting the data into Table 2 and Table 3 is the easy part. However T2ID in Table 3 is a foreign key to the ID in Table 2.

Performance is key so I don't want to use variables and/or iterate through the data row by row, ideally I just want one insert which does the lot.

I've tried using a Link Table but the data in Table 2 and Table 3 is not unique which makes joining unreliable.

Any suggestions?

Create Table T1 (
  ID INT IDENTITY(1,1),
  Col1 VARCHAR(10),
  Col2 VARCHAR(10),
  Col3 VARCHAR(10),
  Col4 VARCHAR(10),
  Col5 VARCHAR(10),
  Col6 VARCHAR(10)
)

Create Table T2 (
  ID INT IDENTITY(1,1),
  Col1 VARCHAR(10),
  Col2 VARCHAR(10),
  Col3 VARCHAR(10)
)

Create Table T3 (
  ID INT IDENTITY(1,1),
  T2ID INT,
  Col4 VARCHAR(10),
  Col5 VARCHAR(10),
  Col6 VARCHAR(10)
)

I can't change T2 or T3. T1 is not a staging Table, and I can't alter that either. There are other packages also writing to T1, T2, and T3. Although it is possible to schedule them at different times should I need to.

Answer 1

If you use MERGE to insert the data into T2, you can generate a mapping table between T1.ID and T2.ID:

DECLARE @Mapping TABLE
(
  T1ID int,
  T2ID int
);
MERGE INTO
  dbo.T2 AS tgt
USING
  dbo.T1 AS src
ON
  1 = 0
WHEN NOT MATCHED THEN
  INSERT (    Col1,     Col2,     Col3)
  VALUES (src.Col1, src.Col2, src.Col3)
OUTPUT
  src.ID, inserted.ID INTO @Mapping (T1ID, T2ID)
;

Unlike INSERT, which would let you reference the inserted table's columns only in the OUTPUT clause, the MERGE statement allows you to reference the source table's columns as well. That is key to this solution, because that is how you associate the source IDs with the target IDs.

Once you have the mapping table, you can just use it in a join when inserting into T3. A plain INSERT ... SELECT will do this time:

INSERT INTO
  dbo.T3 (T2ID, Col4, Col5, Col6)
SELECT
  m.T2ID,
  t.Col4,
  t.Col5,
  t.Col6
FROM
  dbo.T1 AS t
  INNER JOIN @Mapping AS m ON t.ID = m.T1ID
;

Wrap both statements in one transaction to make your split operation atomic.

Discussion about the MERGE mapping method can be found in this thread:

• Using merge..output to get mapping between source.id and target.id

Answer 2

Semi-brilliant idea here. This process should work, but feels kludgy.

DECLARE @t2CurrID int, @t1MinID int

BEGIN TRANSACTION

SELECT @t2CurrID = IDENT_CURRENT(T2)

SELECT @t1MinID = min(t1.ID) FROM T1 WHERE [Criteria used to decide what gets copied across]

INSERT INTO T2 (
    Col1, 
    Col2, 
    Col3
    )
SELECT 
    Col1,
    Col2,
    Col3
FROM T1
WHERE [Criteria used to decide what gets copied across]
ORDER BY T1.ID

INSERT INTO T3 (
    T2ID,
    Col4,
    Col5,
    Col6
    )
SELECT 
    Row_Number() Over (Order By T1.ID) + @t2CurrID as T2ID,
    Col4,
    Col5,
    Col6
FROM T1
WHERE [Criteria used to decide what gets copied across]
ORDER BY T1.ID

COMMIT TRANSACTION

How the process works: The IDENT_CURRENT(T2) command gets the last identity value inserted into T2. We then insert into T2, ordered by T1.ID. Finally, we insert into T3, with the value for T2ID calculated from the last identity inserted into T2 Prior to our insert, and a Row_Number() based on the order we inserted the data into T2.

So, if the last ID value was 100, the first new row in T2 would get ID 101, and the calculated T2ID in T3 would be (100 + 1) = 101. For the 100th row added, the new T2.ID would be 200, and the T2ID in T3 would be (100 + 100) = 200, etc.

The biggest question is the interaction between IDENT_CURRENT and the transaction if other processes are writing to the table. However, if the transaction gets the correct value and locks the table as I hope it would, then the process should give you a correct sequencing.

If you needed to lock the table for the duration of the transaction, you could always issue:

SELECT TOP 1 * from T2 WITH (TABLOCK)

Answer 3

It has been my experience when splitting data into two tables, that SSIS is the best. Let me explain. You cannot use TSQL to move from a main import table to the 2 tables, this will run slower than SSIS because TSQL is logged in the transaction log. You will have to use only SSIS functions and there is one that can split data to two separate tables. The reason that SSIS will run faster is because it is running under MsDtsSrvr.exe, not under the sqlservr.exe.