• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

use @@IDENTITY variable in a new query

Hi all I have the code below
How can I use the value of the last insert ('F')
as an insert into a new table
CREATE TABLE AAMyTable (
  [GUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
  abc     char(1) NOT NULL
 )
 INSERT INTO AAMyTable (abc) VALUES ('D')
 INSERT INTO AAMyTable (abc) VALUES ('E')
 INSERT INTO AAMyTable (abc) VALUES ('F')
 PRINT  @@IDENTITY

Open in new window


Insert into BBMyTable (xyz)(@@IDENTITY) given that the xyz column data type is uniqueidentifier null

Thanks in Advance
0
ZURINET
Asked:
ZURINET
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Declare an int variable, then after every single-row insert set the SCOPE_IDENTITY() to that variable.
CREATE TABLE #foo (id int identity(1,1), msg varchar(25), previous_id int)

Declare @id int 

INSERT INTO #foo (msg, previous_id) VALUES ('Hows', 0) 
SELECT @id = SCOPE_IDENTITY()

INSERT INTO #foo (msg, previous_id) VALUES ('it', @id) 
SELECT @id = SCOPE_IDENTITY()

INSERT INTO #foo (msg, previous_id) VALUES ('goin', @id) 
SELECT @id = SCOPE_IDENTITY()

INSERT INTO #foo (msg, previous_id) VALUES ('eh?', @id) 
SELECT @id = SCOPE_IDENTITY()

SELECT * FROM #foo

Open in new window

0
 
ZURINETAuthor Commented:
Hi Horn

Thanks for your input

I need to pass this value to a variable.
SELECT @id = SCOPE_IDENTITY()

@id  = uniqueidentifier
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>  [GUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
Based on the above I don't see a Default Value so that it knows to populate GUID on an insert.   Perhaps ... DEFAULT NEWID()

>Insert into BBMyTable (xyz)(@@IDENTITY) given that the xyz column data type is
@@IDENTITY and SCOPE_IDENTITY() return numeric(18,0), not GUIDs, so that's not going to work.
0
 
ZURINETAuthor Commented:
Hi Jim Horn

Thanks for your input..
I will just create a new GUID and reference it before the first insert
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now