Get next sequence from table in insert statement to another table

Question

I have 3 tables

Table_A has a bunch of rows
Table_B where rows are going to be inserted with data from Table_A
Table_C holds a number (integer) called code_number

I have a stored procedure (sp_getNextCode) that selects the current code_number from Table_C, creates and returns a varchar code string with this number (like yyyyMMdd + cast(code_number as varchar) or something) and updates the Table_C code_number with the next value (code_number+1)

So far so good.

Now I want to insert a number of rows from Table_A to Table_B WITHOUT THE USE OF CURSOR using a

INSERT INTO TABLE_B 
    SELECT .... FROM TABLE_A

Again so far so good

The problem is that one of the values in the above insert statement has to be the output of the stored procedure sp_getNextCode.

I cannot use the stored procedure in the statement
I cannot create a function with the same code as sp_getNextCode as the functions cannot have INSERT/UPDATE/DELETE

I don't have the option of SQL Server 2012 (which has a sequence) only SQL Server 2008

Is there any way to achieve this or the only way is with cursors (I REALLY want to avoid the cursor cause im talking about thousands of rows that need to be inserted and it takes too long )

Side note: you should not use the sp_ prefix for your stored procedures, since that prefix has been reserved by Microsoft for its own use. Try to avoid it whenever you can — marc_s
– marc_s, Commented Aug 22, 2012 at 12:35
thanks marc_s i know about that i just named it like that for the sake of the example :) — isioutis
– isioutis, Commented Aug 22, 2012 at 12:37

Yván Ecarri · Accepted Answer · 2012-08-22 12:41:25Z

1

Sure, there is a way: Create a stored procedure to read from table_A, inserts ino table_b and updates table_C:

BEGIN TRANSACTION

SELECT @last_value=last_vale FROM  Table_C;

SELECT @records_to_insert = COUNT(*) 
FROM Table_A
WHERE <Conditions>

INSERT INTO TABLE_A
SELECT <Fields>,...., @last_value + ROW_NUMBER() 
OVER(ORDER BY <some ordering criteria>)
FROM TABLE_A
WHERE <Conditions>

UPATE Table_C
SET last_value = @last_value + @records_to_insert +1

COMMIT TRANSACTION

I am ommiting some details as the transformation of the numbers to the formatted code, exception handling and rollback, but I hope you can get the idea.

The trick is to use the ROW_NUMBER() OVER (ORDER BY...) function to obtain a unique number for each row.

http://msdn.microsoft.com/en-us/library/ms186734.aspx

answered Aug 22, 2012 at 12:41

Yván Ecarri

1,76019 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

isioutis Over a year ago

Ok it wasnt 100% what i needed but it helped me solve the problem i had . i only needed to make a function that gets @last_value and ROW_NUMBER() and returns a new varchar()

Collectives™ on Stack Overflow

Get next sequence from table in insert statement to another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related