Re-using Identity achieved after INSERT query

Hi all,
I'm using an INSERT query to add records to a table with a defined
Identity column.
The query looks like this:
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
9,1);
SELECT SCOPE_IDENTITY();
The return value is Text_ID, and I'm using the it for another insert
query:
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
SELECT SCOPE_IDENTITY();
Is there a way to combine the two queries into a single query ?
Something like this:
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
9,1);
SELECT SCOPE_IDENTITY() = Text_ID;
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
SELECT SCOPE_IDENTITY();
Thanks to all.You can use variable for this.
Declare @.TextID int
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',9,1);
SELECT @.TextID = SCOPE_IDENTITY();
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,@.TextID ,1);
...
MC
"Avital" <avital.chissick@.gmail.com> wrote in message
news:1193566518.353471.43280@.v3g2000hsg.googlegroups.com...
> Hi all,
> I'm using an INSERT query to add records to a table with a defined
> Identity column.
> The query looks like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY();
> The return value is Text_ID, and I'm using the it for another insert
> query:
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Is there a way to combine the two queries into a single query ?
> Something like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY() = Text_ID;
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Thanks to all.
>|||I think you just need to use a variable:
DECLARE @.TextId INT
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
9,1);
SELECT @.TextId = SCOPE_IDENTITY();
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,@.Text_ID,1);
SELECT SCOPE_IDENTITY();
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Avital" <avital.chissick@.gmail.com> wrote in message
news:1193566518.353471.43280@.v3g2000hsg.googlegroups.com...
> Hi all,
> I'm using an INSERT query to add records to a table with a defined
> Identity column.
> The query looks like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY();
> The return value is Text_ID, and I'm using the it for another insert
> query:
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Is there a way to combine the two queries into a single query ?
> Something like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY() = Text_ID;
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Thanks to all.
>