0

I have three tables say t1,t2, t3. Actually,t3 is result of select query with joins from many tables.I have to bulk insert the data from t3 to t1 and t2.First I have to insert some data from t3 into t1 and get the identity value from t1 and insert into t2 along with some data from t3. T1 has the unique combination of internalid and date. The tables' structures are as follows.

Table t1: columns: t1id identity(1,1), internalid, date

Table t2: columns: t2id,t1id,value

table t3: columns: t3id,internalid, date, value

e.g.if t3 has the following data, the data t1 and t2 will have are shown below.

t3:

t3id |internalid| date |value    

1     | 11      | x     |  5

2     | 11      | y     |  4

3     | 13      | x     |  7

4     | 11      | x     |  3

then t1 and t2 should be inserted as

t1:

t1id     | internalid      | date    

1        | 11              | x  

2        | 11              | y

3        | 13              | x

and

t2:

t2id     | t1id     | value

1        | 1        | 5

2        | 2        | 4

3        | 3        | 7

4        | 1        | 3
TFrost
  • 769
  • 2
  • 12
  • 31
xyz
  • 762
  • 7
  • 24
  • why add the burden of the identity column? drop it and use the natural key: you will avoid the bulk import issue completely. – Paolo Sep 08 '15 at 09:50
  • I cannot change the existing structure. – xyz Sep 08 '15 at 09:56
  • 1
    bulk import the 1st table, import the other tables in staging tables and from the staging table perform the joins needed fo copy the data from staging to final with the updated ids. – Paolo Sep 08 '15 at 10:54
  • Or use SSIS instead of bulk inserts. – Tab Alleman Sep 08 '15 at 13:15

0 Answers0