SQL Select and Update record during Insert

Question

Trying to SELECT and UPDATE a record inside a SELECT statement.
The Initial SELECT:

INSERT INTO dbo.ATable(VALUE1, VALUE2)
SELECT B.VALUE1, dbo.FUNCTION(B.VALUE1)
FROM dbo.BTable B

The FUNCTION:

SELECT @nextNum = C.VALUE2
FROM dbo.CTABLE C
WHERE C.VALUE1= @VALUE1
RETURN @nextNum

What I am trying to do:

Insert a record into Table1.
Get data from Table2.
Also get a value for VALUE2 from another Table(CTable).

Problem:

I need to increment VALUE2 each time this function executes so multiple calls to that record have a unique value.

I can't have a stored proc from inside the function, I can't have a stored proc inside the SELECT, and I can't call UPDATE from inside the FUNCTION.

Am I out of options? Do I need a different implementation?

Are you trying to Insert one row into table `A` or many rows? — ypercubeᵀᴹ, Feb 28 '12 at 15:10
If I read this right, you want to use sequences. You might take a look at [How would you implement sequences in Microsoft SQL Server?](http://stackoverflow.com/questions/282943/how-would-you-implement-sequences-in-microsoft-sql-server) — Nikola Markovinović, Feb 28 '12 at 15:20

score 0 · Answer 1 · answered Feb 28 '12 at 15:13

0

INSERT INTO dbo.ATable(VALUE1, VALUE2)
SELECT B.VALUE1, C.VALUE2
FROM dbo.BTable B 
JOIN dbo.CTable C
ON B.VALUE1 = C.VALUE1

answered Feb 28 '12 at 15:13

Sam DeHaan

10,246
2
40
48

SQL Select and Update record during Insert

1 Answers1