Link to home
Create AccountLog in
Avatar of Rubicon2009
Rubicon2009

asked on

T-SQL : How to run a subquery for each row in a insert statement ?

Hi !

I'm having trouble with a SQL query. I'm using MSSQL Server 2008

Here's the query :

INSERT INTO table
SELECT
NEWID() AS ID
,GETDATE() AS Modified
,'10436837-7AA9-4012-BE66-CA33D0FA084D' AS ModifiedByID
,'78C70790-BC9B-48DC-963B-164205D74655' AS AttributeID
,ID AS UserID
,(SELECT Top(1) value FROM UserAttributes WHERE AttributeID = '78C70790-BC9B-48DC-963B-164205D74655' AND Value > '5000' ORDER BY Value DESC) + 1 AS Value

FROM @emp1
GO

I would like that the sub-query in my insert statement be ran for each row inserted (this query go check that last assign employee number and add 1 for the next employee)

Is there a way to do this ?

Thanks

-Martin
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

I see no logic that will prevent your subselect from returning the same record every time.  
ASKER CERTIFIED SOLUTION
Avatar of LCSandman8301
LCSandman8301
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Rubicon2009
Rubicon2009

ASKER

@LCSandman8301

Thank You !
The solution was :

SELECT
      NEWID() AS ID
      ,GETDATE() AS Modified
      ,'10436837-7AA9-4012-BE66-CA33D0FA084D' AS ModifiedByID
      ,'78C70790-BC9B-48DC-963B-164205D74655' AS AttributeID
      ,ID AS UserID
      ,@VarLastEmpNumb + row_number() OVER(ORDER BY ID) AS Value