I need some help creating an INSERT * SELECT statement that supplies a record
counter in the returned items. Here's the task,
I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, Field3. t2 is
Field1, Field2, Field3.
t1 has a multiple field unique key called Request that has GroupCount and
ItemCount. I would like to create an INSERT * SELECT statement that takes the
three fields from t2 and puts them into t1 and at the same time automatically
fills the GroupCount and ItemCount field. My data in t1 should look like this.
+----------+-----------+--------------+--------------+--------------+
|GroupCount| ItemCount | Field1 | Field2 | Field3 |
+----------+-----------+--------------+--------------+--------------+
| 1 | 1 | data from t2 | data from t2 | data from t2 |
| 1 | 2 | data from t2 | data from t2 | data from t2 |
| 1 | 3 | data from t2 | data from t2 | data from t2 |
| 2 | 1 | data from t2 | data from t2 | data from t2 |
| 2 | 2 | data from t2 | data from t2 | data from t2 |
| 3 | 1 | data from t2 | data from t2 | data from t2 |
| 3 | 2 | data from t2 | data from t2 | data from t2 |
| 3 | 3 | data from t2 | data from t2 | data from t2 |
| 3 | 4 | data from t2 | data from t2 | data from t2 |
| 3 | 5 | data from t2 | data from t2 | data from t2 |
| 3 | 6 | data from t2 | data from t2 | data from t2 |
| 4 | 1 | data from t2 | data from t2 | data from t2 |
| 4 | 2 | data from t2 | data from t2 | data from t2 |
| 4 | 3 | data from t2 | data from t2 | data from t2 |
| 4 | 4 | data from t2 | data from t2 | data from t2 |
| 4 | 5 | data from t2 | data from t2 | data from t2 |
| 4 | 6 | data from t2 | data from t2 | data from t2 |
| 4 | 7 | data from t2 | data from t2 | data from t2 |
| 5 | 1 | data from t2 | data from t2 | data from t2 |
| 5 | 2 | data from t2 | data from t2 | data from t2 |
| 6 | 1 | data from t2 | data from t2 | data from t2 |
| 6 | 2 | data from t2 | data from t2 | data from t2 |
| 6 | 3 | data from t2 | data from t2 | data from t2 |
+----------+-----------+--------------+--------------+--------------+
I can figure most of it out with the exception of the ItemCount value. What I
have so far is this,
INSERT INTO t1 (GroupCount, ItemCount, Field1, Field2, Field3) SELECT (SELECT
Max(GroupCount)+1 From t1), ?????, Field1, Field2, Field3 FROM t2;
The part with the ????? is what I can't figure out.
Any ideas?
Thanks
