Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tash
Starting Member
12 Posts |
 Posted - 2008-07-28 : 14:48:32
|
I have a table1:[ItemID] [ItemName] [TransactionID] where ItemID - bigint ItemName - varchar(100) TransactionID - varchar(100)ItemID is primary key in this table, it starts from 1, then 2,3 ....the problem is that values in column [ItemName] repeat, the same thing with 3rd column, so I can have for example these rows:1 Item1 5002 Item2 5003 Item1 200 I need to do the following things:1) create table2 from table1 where I will have Unique ItemID (primary key) and corresponding Unique ItemName.It is not difficult to create this table using SELECT DISTINCT ItemName....and then insert it into new table with ID's starting from 1,2,3...etc.BUT I need to create another table3 too.In this table I need to obtain Inverted Index where I should haveItemID and corresponding list of TransactionID's where this ItemID appears.And the main thing that this ItemID in table3 must be foreign key(FK) linking table3 with table2How can I do this? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-29 : 04:18:36
|
| You can use MAX(ItemID) to insert one record per ItemName to table2 and then have a trigger on table2 which will take inserted itemid and put it to table3 along with other details from table1. |
 |
|
|
|
|
|
Inverted Index