Help with INSERT & Subquery

Folks:

Looking for some help with a complex INSERT command...

3 tables:

   table1 has master data
   table2 is an exact duplicate of table1

      column1 is a simple primary key, not NULL, unique index
      column1 is simply a sequential number (VARCHAR 15)
   table3 has 1 row, 1 column which holds the next available primary
      key for table2

We need to copy certain rows & columns from table1 into table2. Here's what I have so far:

INSERT INTO table2 (column1, column2, ..., column9)

   SELECT NeedHelpHere, column_b, column_c, ... column_i

      FROM table1
      WHERE column_b LIKE 'LiteralValue%'

For NeedHelpHere, my first thought was to SELECT MAX(column1)+1 FROM table2, then UPDATE table3 SET column = (SELECT MAX(column1)+1 FROM table2). But since the commit isn't done until after the entire INSERT has run, I got unique index violations.

Any Suggestions?

TIA,
--Phil

PS: the customer won't allow us to use any triggers, stored procs, or user-defined functions.

PPS: "I am endeavoring to construct a mnemonic memory circuit using stone knives and bear skins." -- Spock

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Aug 08 2000 - 00:00:00 CDT