Getting the Auto Generated Column values after Insertion

[hellboss]

Hi Everyone ! im using SQL SERVER 2005,
My problem is, i am inserting a set of values into a table using a subquery, the problem occurs when i tried to get the auto generated column in the table which iam trying to insert.

ex:

create procedure Sample
@ID
Insert into sampleTable1(na me,name2,name3)
(Select name,name2,name 3 from sampleTable2 where SID=@SID)

Here i try to insert a set of values into the sampleTable1 By passing a parametter values(@ID), After inserting i tried to get the autoGenerated column in the sampleTable1

I tried with the Scope Identity, but i think this wil not work out in a large network,
@@Identity as IID

I need a alternative solution for this
Kindly provide me with a valid solution

Thanks in advance !

[Mam139]

Hi Everyone ! im using SQL SERVER 2005,
My problem is, i am inserting a set of values into a table using a subquery, the problem occurs when i tried to get the auto generated column in the table which iam trying to insert.

ex:

create procedure Sample
@ID
Insert into sampleTable1(na me,name2,name3)
(Select name,name2,name 3 from sampleTable2 where SID=@SID)

Here i try to insert a set of values into the sampleTable1 By passing a parametter values(@ID), After inserting i tried to get the autoGenerated column in the sampleTable1

I tried with the Scope Identity, but i think this wil not work out in a large network,
@@Identity as IID

I need a alternative solution for this
Kindly provide me with a valid solution

Thanks in advance !

Hi,

Do You need to find the last auto generated number for your sampletab1???
If so here is the Solution.
declare one output variable in your procedure, after the insert statement get the scope_identity( ) and assign it to output variable.
ex:

create procedure Sample
@SID INT,
@OutVar Int OUTPUT
AS
Begin
Insert into sampleTable1(na me,name2,name3)
(Select name,name2,name 3 from sampleTable2 where SID=@SID)

Select @outVar = Scope_Identity( )

END

Then execute it and get the last auto generated number.

ex:
Declare @val int
Exec Sample 1, @Val OUTPUT
SELECT @VAL

Hope it works for u.

Thank u.

[guriya]

hi
i want to enter values (numbers) of fields on form (of vb6/vb.net).. and want that a table automaticaly creates in sql according to the fields those have been entered...
hows it possible ??
please help me out ....?
palkiicartooon@ yahoo.com

[ck9663]

Loop through your form to gather the field names, size, etc...

Then put it in a variable...Then do a dynamic query.

-- CK