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

Re: Require Solution for this SQL problem

"Nick" wrote in message
news:1189853588.129281.216870@n39g2000hsh.googlegroups.com.. .
> 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
>

Lookup the ROW_NUMBER() function.

--
David Portas

Re: Require Solution for this SQL problem

Nick (nachiket.shirwalkar@gmail.com) writes:
> 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.

BEGIN TRANSACTION

SELECT @RunID = coalesce(MAX(RunID), 0) + 1,
@Id = coalesce(MAX(Id), 0)
FROM TableB (UPDLOCK)

INSERT TableB(RunID, Id, ....)
SELECT @RunID, row_number() OVER(ORDER BY ...), ....
FROM TableA

COMMIT TRANSACTION

The use of row_number requires SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx