Require Solution for this SQL problem

Hi,

I have two tables TableA and TableB. I have a stored procedure which runs every morning and based on some logic dumps rows from TableA to TableB. In Table B there are two additional colums ID and RunID. ID is a normal sequence applied for all rows. But the RunId should be constant for a run of stored proc.

So for e.g. say structure of Table A and Table B

Table A                    Table B

col1                         ID RunID  col1

Now when I run stored proc I want rows copied as below

TableA                      TableB

col1                           ID RunID    col1
row1                          1   1           row1
row2                          2   1           row2

The next day when stored prc runs I want data as

TableA                      TableB

col1                           ID RunID    col1
row1                          1   1           row1
row2                          2   1           row2
row1                          3   2           row1
row2                          4   2           row2

So for every run of stored proc each day I want the Run ID incremented only by one and ID is normal sequence which increments for allrows inserted.

Please help.

Nick Received on Sat Sep 15 2007 - 05:46:45 CDT