473,325 Members | 2,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Getting the Auto Generated Column values after Insertion

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(name,name2,name3)
(Select name,name2,name3 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 !
Sep 3 '07 #1
3 7425
Mam139
2
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(name,name2,name3)
(Select name,name2,name3 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(name,name2,name3)
(Select name,name2,name3 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.
Sep 4 '07 #2
guriya
1
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
Sep 16 '08 #3
ck9663
2,878 Expert 2GB
Loop through your form to gather the field names, size, etc...

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

-- CK
Sep 16 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

12
by: Nick | last post by:
Is it possible to have multiple auto increments in one column? Say I have two tables... 1. table 'messageboards' with fields ('id' , 'name') 2. table 'messagethreads' with fields ('id' ,...
6
by: Alpha | last post by:
I retrieve a table with only 2 columns. One is a auto-generated primary key column and the 2nd is a string. When I add a new row to the dataset to be updated back to the database. What should I...
1
by: Prince | last post by:
Hi all, Can anyone tell me how to create auto generated values in a DataTable column? Looking forward for the reply.... Thanx in advance...
0
by: Viorel | last post by:
Working as a beginner with data objects in Visual Studio 2003 and C#, I use the "Generate Dataset" command in order to generate automatically the dataset objects based on data adapters. Generated...
41
by: pb648174 | last post by:
In a multi-user environment, I would like to get a list of Ids generated, similar to: declare @LastId int select @LastId = Max(Id) From TableMania INSERT INTO TableMania (ColumnA, ColumnB)...
2
by: x0054 | last post by:
Ok, this is a stupid problem, I admit. I have a scrip that adds records to a table. The records are for photos. So, after adding a record the scrip also uploads a picture from users computer and...
0
by: hellboss | last post by:
Hi im using asp.net vs05 with sql05 , I Started on creating a Stored Procedure where i wrote some insert statement and i just got the Auto Generated column value by using Scope identity or...
2
by: Zunil | last post by:
Hi All, We are testing one of our component with JDBC + DB2 using db2jcc driver. We afced a strange issue. Scenario is like this. 1. We insert 1 record to a table having IDENTITY column as the...
9
by: KDawg44 | last post by:
Hi, I have PHP function that adds a record to the database. The table has an ID that is AUTO_INCREMENT. Is there anyway to get that ID back when I do any kind of insert? That ID is a foreign...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.