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.