November 1, 2007 at 2:49 pm
Sorry if this has been covered before. I have lots of Oracle experience, but I am a newby to SQL Server.
I have a table that is being populated from a stored procedure. The table has a required field that the stored procedure may not have any data for and the column is part of a unique key. What I want to do is to use the primary key of the row being inserted. Is there a way to get the identity before I insert and use that value for both the primary key and my column?
In Oracle I could select the value from the sequence and then use that for both columns if necessary. Is there something similar in SQL Server? Or can I use a trigger and get the value of the id and use that if my colulmn is null? i.e.
IF :NEW.myColumn IS NULL THEN
:NEW.myColumn = :NEW.myPrimaryKey;
END IF;
Thanks for any ideas!
November 2, 2007 at 5:13 am
Use sql to get the next id
eg as part of your update statement
insert into table 1 ( (PK column 1) , column 2, etc
Values (
(Select TOP 1 (PK_COLUMN) from Table 2 ORDER BY PK_COLUMN DESC) + 1 As PK_TO_INSERT,
Column 2 Value
)
November 2, 2007 at 5:39 am
If you wish to enter an identity you can check it through @@identity which being a global variable will give you the next identity value
Also you can set a default value for that particular column ... Only if that suits your design and put value in the variable only if you have data coming from the stored procedure
Hope this helps ...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply