ItemCount is essentially a counter of the records from the select
statement. So, every new INSERT ... SELECT statement gets a new
GroupCount (the next number up) and ItemCount represents the ID of the
items in that new group.
Does that make sense?
- Thanks
"Michael Dykman" <[EMAIL PROTECTED]> 1/12/07 8:13 AM >>>
On 1/11/07, Ed Reed <[EMAIL PROTECTED]> wrote:
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?
I'm not very clear what ItemCount is supposed to represent.. if you
restate it, perhaps I can help
--
- michael dykman
- [EMAIL PROTECTED]
