sorry, entered too soon... Brad,
Is there any table in the database that has the code values as distinct values in a column? If so, *INSERT INTO temptable SELECT thatcolumn FROM thattable WHERE thatcolumn IN ( &vtype )* would be part of the job. And, presumably, there's another table that has the other kinds of values from .vPN? What you might use is a Cartesian product: Select rows from both tables at once, and *do not* link the two tables at all. You end up multiplying the rows in one table by the rows in the other table for the insert: INSERT INTO temptable (UnitPart, UnitType) + SELECT p1.UnitPart, t2.UnitType + FROM PartsListTable p1, TypesListTable t2 + WHERE p1.UnitPart IN ( &vPN ) + AND t2.UnitType IN ( &vType ) Cartesian products are dangerous if you have large tables or don't qualify the rows in one table or the other, but they can allow for some very efficient SQL. Bill On Wed, May 12, 2010 at 12:14 PM, Brad Davidson < [email protected]> wrote: > I'm Looking for an alternative to a series of SSUB extracts within a > WHILE loop. > > I have 2 variable text strings resulting from a couple of SELECT INTO > statements, which lists UnitPart and UnitType, such as: > > UnitPart: vpn = ('Unit1,Unit2,Unit3') > > UnitType: vtype = ('F,NDT,SM,C,PL') > > I want to populate a temporary table/view with unique unit & type > combinations, such as: > > UnitPart UnitType > > unit1 F > > unit1 NDT > > unit1 SM > > unit1 C > > unit1 PL > > unit2 F > > unit2 NDT > > unit2 SM > > unit2 C > > unit2 PL > > unit3 . . . . etc... > > > > What method would you employ to build this temporary table? The resultant > table values will be used to replace via UPDATE or INSERT values in the > permanent table... > > > > Thank you for your thoughts on this! > > > > Brad Davidson >
