beginner: how to get the value in a record just created
-
hello
please excuse my begginner skills in t-sql
my question is the following:
suppose i created a record in a table, then i want to take a value from a field and assign it to another value in another table, i really dunno how to write that :s
my code:
INSERT
INTO NmsRecipient([iAddrQuality],[iEmailFormat],[iPartitionId],[iRecipientId]) VALUES (1,1,0,1)
then i want to do another insert in another table that uses NmsRecipient.iRecipientId just filled before:
INSERT INTO [NmsSubscription] ([iConfirmationId],[iRecipientId]) VALUES (1,?????)
how to tell it that i just want the value of NmsRecipient.iRecipientId that was created above??
Another somehow begginner question, when should i put brackets [] for tables names and fields?
Thanks for helping!
-
DECLARE @iRecipientID int
INSERT INTO NmsRecipient([iAddrQuality],[iEmailFormat],[iPartitionId],[iRecipientId]) VALUES (1,1,0,1)
SET @iRecipientID = SCOPE_IDENTITY()
OR
SET @iRecipientID = @@IDENTITY
OR
SET @iRecipientID = IDENT_CURRENT('NmsRecipient')
INSERT INTO [NmsSubscription] ([iConfirmationId],[iRecipientId]) VALUES (1,@iRecipientID)
Check BOL for more details about these functions.
-
Ok, tx!
But what if the field i want to get the value is not an identity?
is
Select
max(iRecipientId) from NmsRecipient
a good idea? Sometimes the value might not be the maximum ....
-
Not sure what you a really asking for, need better understanding of what you are trying to do.
If [iRecipientId] is not IDENTITY then you know the value anyway
Where does iRecipientId come from?
The only scenario I can think of is when using a proc
CREATE PROCEDURE usp_myproc
@iAddrQuality int,
@iEmailFormat int,
@iPartitionId int,
@iRecipientId int
AS
INSERT INTO NmsRecipient
(
iAddrQuality,
iEmailFormat,
iPartitionId,
iRecipientId
)
VALUES
(
@iAddrQuality,
@iEmailFormat,
@iPartitionId,
@iRecipientId
)
INSERT INTO NmsSubscription
(
iConfirmationId,
iRecipientId
)
VALUES
(
1,
@iRecipientId
)
GO
As for [] I only use them
1. With object names that are reserved (I avoid reserved words but sometimes unavoidable)
2. When column naming e.g. OriginalName AS [NewName]
But then there are others better versed in best practices than me
Far away is close at hand in the images of elsewhere.
Anon. -
ok great!
tx again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply