Hi!
I have a question that might sound obvious to some of you but (obviously) not to me.
I have a stored procedure that adds a new record to a table that has an [Identity] field (TableID).
I want to return the value of the TableID of the newly created record. Sound simple ?
Thanks.
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies View RelatedI have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?
TIA,
Barkingdog
I have a table for Units and Dollars based on 2 date keys ; snapshot date_key and delivery date_key (which are date key from date dimension)
This table do not update daily and if I want to have the information based on the lastest snapshot date_key how should I write a query for this case?
What I did was:
Code Snippet
SELECT snapshot_date_key,delivery_date_key, units, dollars
FROM dbo.tbl_fact
WHERE snapshot_date_key IN(SELECT DATE_KEY FROM tbl_DIM_Date
WHERE (FULL_DATE = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))))
But this query is only available when snapshot date key exists in the table based on today.
If table is not updated today then I want to get the lastest snapshot_date_key from the fact table.
Max function with sanpshot_date_key does not work due to the delivery_date_key.
And kinds of advice would be greatly appreciated.
Thanks.
How do i return a Identity through a stored procedure?
View 18 Replies View RelatedSorry I'm new in this,Add a record using T-SQL and Connection.ExecuteHow can i insert the identity in a VB-variableDim objConn As ADODB.ConnectionSet objConn = New ADODB.ConnectionobjConn = CurrentProject.ConnectionobjConn.OpenobjConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')SELECT @@IDENTITY as 'NewID'"Msgbox NewID triggers an error !Filip
View 5 Replies View RelatedWhat I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this
image_23.jpg (23 being the @@identity) resize it and save it to specified directory
I cant seem to get the identity to return to my script.
This is my SP
CREATE PROCEDURE SP_Insertad
(
@catid int,
@subcatid int,
@areaid int,
@uid int,
@adtitle varchar(255),
@addescription varchar(1000)
)
AS
Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)
select @@identity
return
GO
I tested in query analyser, and it works fine, so It must be my code. this is my function
Sub Insert_pic(sender as object, e as eventargs)
Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))
SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)
SQLCommander.Commandtype = Commandtype.StoredProcedure
SQLCommander.Parameters.add("@adtitle", adtitle)
SQLCommander.Parameters.add("@addescription", addescription)
SQLCommander.Parameters.add("@catid", catid)
SQLCommander.Parameters.add("@subcatid", subcatid)
SQLCommander.Parameters.add("@uid", uid)
SQLCommander.Parameters.add("@areaid", areaid)
'// this section not working right, it wont write return id
Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue
response.write(SQLCommander.Parameters("ReturnValue").Value)
SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()
End sub
Can anybody see anything I missing? I appreciate any imput
I would like to use ADO in a new project but I need to find out how to return the identity field value to ADO out of a stored procedure. I have not done alot with ADO or with SQL 7.0 so if anyone has an example of the SQL and the ADO code that I would need I would greatly appriate it.
Thanks
Hello,
I'm using C# to access sql server.
When I execute an insert command, I want to get the value of the column ID(ID is an identity column in my table defination) . Is there any method I can use?
Thanks.
Hi ,
My Requirment goes like this. In a table say X , i have two column called Customer_number and date_received. What i need to find is, lastest two enteries for the customer number. That is top 2 records for each customer_number.
the query out put is some thing like this,
Cust Number Date_received
1234 06/06/2005 10AM
1234 06/06/2005 08AM
1235 06/06/2005 10AM
1235 06/05/2005 10AM
1236 06/05/2005 11PM
1236 06/04/2005 06AM
How can i achieve the above functionality.
I hope i was clear in my explanation.
Regards,
Chetan
I have a table named PERSON and a field named PERSON_ID. How can I set this field to Autonumber? I know I have to use the IDENTITY command, but when I view this field in "design" view, all the IDENTITY options are grayed out.
How can I set this field with the IDENTITY properties?
Thanks
I have a stored procedure where I am passing in several strings which are concatenated to form an Insert SQL statement. I then return the identity value from the insert - problem is, the identity value is coming back NULL.
Here is the sp. Any ideas?
CREATE PROCEDURE sp_ExecuteInsert
@pIdentity int OUTPUT,
@pSQL1 varchar(255),
@pSQL2 varchar(255) = NULL,
@pSQL3 varchar(255) = NULL,
@pSQL4 varchar(255) = NULL,
@pSQL5 varchar(255) = NULL,
@pSQL6 varchar(255) = NULL,
@pSQL7 varchar(255) = NULL,
@pSQL8 varchar(255) = NULL
AS
EXECUTE (@pSQL1 + @pSQL2 + @pSQL3 + @pSQL4 + @pSQL5 + @pSQL6 + @pSQL7 + @pSQL8)
SELECT @pIdentity = @@IDENTITY
GO
FYI, the real problem I am trying to solve is simply to return the identity after an insert. I could write a simple insert sp that returns the identity, but my code is written to handle generic situations and build an INSERT statement as needed. So if there are any other ideas of executing an insert statement and getting the identity, please let me know.
Thanks...
If I selected the data below, how could I write a query to only keep the latest record. I added a "FileDate" column to label the last record (FileDate 200807)I want to display.
LeaseIDOpID Year MonthMCFFileDate
208801407982006 107660200807
208801407982006 107680200801
208801407982006 107784200704
hello,
I'm new to store procedure in slq 2000.
I want to create a sproc in my sql 2000 sevrer,I have a table named "Supplier",
now I want to return my identiy field value when I use insert into sql command (insert into "Supplier") successful.
So how to create this store procedure ?
much thanks
I have a stored proc that will insert a new row into a table
with the values of the parameters you pass in. I need it to
return the value of the ID that's generated by an Identity
column once the row has been written and that value has
been generated. If I just do a SELECT Max(), I could
accidentally grab a row written by someone else, right?
My current sp looks like this:
CREATE PROCEDURE sp_SaveNewLabel
-- @LabelID int output
@LabelType int
, @Logo int
, @Field01 char(30)
AS
INSERT INTO tbLabel
(LabelType
, Logo
, Field01)
VALUES
(@LabelType
, @Logo
, @PrintCC
, @Field01)
How do I grab the new LabelID (the column is int, Identity) and return
it from the stored proc. Any help would be greatly appreciated...
Zack
I need a script that returns the last identity value generated for each table in a database that has an identity column.
I am using SQL Server 2005.
Thanks.
How do I pull only the latest shipdate record from a multiple record key?
I tried the following, but it doesn't pull only the lastest date record:
Select catnum, Max(shipdate) as shipdate from table_a group by catnum, shipdate
Thanks in advance.
I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work.
Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.
CREATE PROCEDURE addMagicRecTest
(
@theSequence int,
@theSubject int,
@theFirstName nvarchar(50)=null
@theLastName nvarchar(75)=null
)
AS
INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@theSequence,@theSubject,@theFirstName,@theLastName)
SELECT @@identity AS finalNum
magicDataConnect = ConfigurationSettings.AppSettings("myDataConnect")
Response.Write(magicDataConnect)
magicCommand = New SqlDataAdapter("addMagicRecTest", magicDataConnect)
magicCommand.ConnectionType = CommandType.StoredProcedure
magicCommand.SelectCommand.CommandType = CommandType.StoredProcedure
' Sequence ID for request
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSequence", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@theSequence").Value = "41833"
' Subject for new Wac Ticket
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSubject", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@theSubject").Value = "1064"
' First Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theFirstName", SqlDbType.NVarChar, 50))
magicCommand.SelectCommand.Parameters("@theFirstName").Value = orderFirstName
' Last Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theLastName", SqlDbType.NVarChar, 75))
magicCommand.SelectCommand.Parameters("@theLastName").Value = orderLastName
DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString
End If
I need finalMagicNum2
how do i return the identity from stored procedure to asp.net code behind page?
CREATE PROCEDURE [dbo].[myInsert] ( @Name varchar(35), @LastIdentityNumber int output)AS insert into table1 (name) values (@name)
DECLARE @IdentityNumber intSET @IdentityNumber = SCOPE_IDENTITY()SELECT @IdentityNumber as LastIdentityNumber
code behind:
public void _Insert(
string _Name,
{
DbCommand dbCommand = db.GetStoredProcCommand("name_Insert");db.AddInParameter(dbCommand, "name", DbType.String, _userId);
db.AddParameter(dbCommand, "@IdentityNumber", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Current, null);
db.ExecuteNonQuery(dbCommand);
int a = (int)db.GetParameterValue(dbCommand,"@IdentityNumber");
whats wrong with to the above code?
We're using ASPUpload as a tool to upload files to our server and savethe details to SQLServer. However, I have an application where I needto return the pkID of the just saved file. I'm assuming that I coulduse the @@Identity command but cannot get this to function.Has anyone used this command with ASPUpload with an success, or anyother methods that could be used?Thanks.
View 8 Replies View RelatedHi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function
hi friends,i have an identity column in my table.(e.x : id )and it's identity increment is 1.when i insert a row the id field is 1, and in next record the field is 2.....now , i delete second record(id=2)and now when i insert a record again , the id column is 3.i want to record be 2 instead 3.plz help me.thanks
View 2 Replies View RelatedHi:
I created a small SQL Express database/ASP.net/C# application and in the learning process. Before I implement it I would like to re-set autonumber / identity field back to 1. Also, I need to start with the blank database. I am not sure how to approach that?
Can you assist?
Thanks
Hello friends,
I had created a web application and uploaded the application. Now the problem is with the DB. I had created the DB on the server (using script). But the fields that have identity field yes is not been set Now how I can set the fields identity field to yes. Fields are already there. Only I want to set there identity field to yes.Let me know how this can be done.
Thanks & RegardsGirish Nehte
Please, How can I get the value of the identity field of the register that I was including in the data base. I am using a stored procedure in SQLSERVER in a asp .net application and I need to show that for the user, it´s like the number of the reclamation.
View 2 Replies View RelatedIf my table has an IDENTITY field, say, the table schema is:
CREATE TABLE BBB(
id int NOT NULL IDENTITY(1,1),
name varchar(20),
job varchar(40))
My data file, which does not carry the IDENTITY field
and its field terminator. The data file looks like this:
debbie cao,programmer
John Doe,engineer
Mary Smith,consultant
I tried to use a format file to bulk copy data from the
data file to the table. Never had any luck.
On the other hand, if I put a comma before the name field,
say, the data file looks like the following:
,debbie cao,programmer
,John Doe,engineer
,Mary Smith,consultant
then, bcp works fine.
But, my data file is automatically generated. It does not
suppose to have the leading comma. Without the leading comma,
I have no idea how may I make bcp work. The SQLBOL says it
can be done. Does anybody have an example to show me?
Please help, thanks!
Dear Fellow Administrators,
We are experiencing an identity problem on a table with 3.7 millions rows of data in the table. The identity field is not auto numbering any more and are wondering if anyone has a suggestion on how to get the auto number field working again without doing a bcp and restoring the table back to the database from bcp. Any suggestions?
Thanks in Advance,
Daimon
Greetings !
Having an unusual problem - have created several (20) tables in a database.
All of these tables have an identity field in them (defined as FIELDNAME int IDENTITY(1,1) NOT NULL ). In most of the tables this works as expected, but in 2, so far, when I do the first load (an insert based on a different database) the identity fields are all zero.
Have recreated the tables, changed the field name and location, etc.
Anyone have any clues ?
TIA
Eric
I need to update an identity field that I have on a table. How can
I go about doing this?
Thanks!
Toni
Folks
I am inserting some values into a table with the following stmt
Insert into table(number,name) values ('12','name')
In the table I have one more identity column ID. I know that I cannot insert a value in that column and the value is automatically increased once I insert a record. After this insert statment, I need to get the value
of the ID (the most recent one) in the next select statement.
ie Select @@identity from table (any condition????)
How do I get the most recent ID value? Actually I m inserting the records in a loop and the ID is increased for every insert.
Thanks for the help,