GET IDENTITY FROM TABLE

Mar 16, 2006

How do i get identity from stored procedure? I CANNOT GET THE ID I CANNOT RETURN ANYTHING AT ALL! WHY?

This is last three lines of stored procedure

SELECT ID = SCOPE_IDENTITY()
RETURN
GO

I get ABSOLUTELY nothing I can't get anything to come back from by stored procedure to my ASP page. I have a "RETURN_VALUE" parameter, that's not the problem. There's something really stupid about this stored procedure that since I've inserted records into a table then from that point on I cannot return ANYTHING! WHYYYYYY???

Sorry, I am SO VERY frustrated. Can anyone help me?

mellamokb

View 3 Replies


ADVERTISEMENT

Last GASP On Insert Row In Table With Identity Field, And Get New Identity Back ?

Jul 9, 2006

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

View 10 Replies View Related

Insert Row In Table With Identity Field, And Get New Identity Back

Jun 30, 2006

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?

View 12 Replies View Related

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

Jun 19, 2008

Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View 6 Replies View Related

Transact SQL :: How To Alter Existing Table Column As Identity Without Dropping Table

Nov 20, 2013

I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.

create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)

View 7 Replies View Related

SQL Server 2012 :: Insert Into Table With Identity Columns From Another Table

Dec 23, 2013

I just created a new table with over 100 Columns and I need to populated just the first 2 columns.

The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.

column1 = ID
column2= P_CLIENT_D

SET IDENTITY_INSERT PIM1 ON

INSERT INTO PIM1 (P_CLIENT_ID)
SELECT
Client.ID
FROMP_Client

So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.

View 1 Replies View Related

ExecuteScalar --&> How To Get The OrderID(Identity) From A Table To Another Table ??

Mar 23, 2006

I am new to asp.net and studying on book.. currently i am stuck with a problem which not understand what is it !! Can anyone help me ?? I trying a shopping cart "Check Out" method, and when i am done the process.. My order_lines Table can update the OrderID which just generated !! What wrong with the statement ??  
 
Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick        ' Insert the order and order lines into the database        Dim conn As SqlConnection = Nothing        Dim trans As SqlTransaction = Nothing        Dim cmd As SqlCommand
        Try            conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)            conn.Open()            trans = conn.BeginTransaction            cmd = New SqlCommand()            cmd.Connection = conn            cmd.Transaction = trans
            ' set the order details            cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @City,@State, @PostCode, @Country, @Total)"            cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime)            cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255)            cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@State", SqlDbType.VarChar, 50)            cmd.Parameters.Add("@PostCode", Data.SqlDbType.VarChar, 15)            cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@Total", Data.SqlDbType.Money)
            cmd.Parameters("@MemberName").Value = User.Identity.Name            cmd.Parameters("@OrderDate").Value = DateTime.Now()            cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text            cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text            cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text            cmd.Parameters("@State").Value = CType(Wizard1.FindControl("txtState"), TextBox).Text            cmd.Parameters("@PostCode").Value = CType(Wizard1.FindControl("txtPostCode"), TextBox).Text            cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text            cmd.Parameters("@Total").Value = Profile.Basket.Total
            Dim OrderID As Integer            OrderID = Convert.ToInt32(cmd.ExecuteScalar())  <-- Is it wrong or need to add wat ?            ' change the query and parameters for the order lines            cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID,Quantity, Price) VALUES (@OrderID, @ProductID, @Quantity, @Price)"            cmd.Parameters.Clear()            cmd.Parameters.Add("@OrderID", Data.SqlDbType.Int)            cmd.Parameters.Add("@ProductID", Data.SqlDbType.Int)            cmd.Parameters.Add("@Quantity", Data.SqlDbType.Int)            cmd.Parameters.Add("@Price", Data.SqlDbType.Money)            cmd.Parameters("@OrderID").Value = OrderID
            For Each item As CartItem In Profile.Basket.Items                cmd.Parameters("@ProductID").Value = item.ProductID                cmd.Parameters("@Quantity").Value = item.Quantity                cmd.Parameters("@Price").Value = item.UnitPrice                cmd.ExecuteNonQuery()            Next            ' commit the transaction            trans.Commit()        Catch SqlEx As SqlException            ' some form of error - rollback the transaction            ' and rethrow the exception            If trans IsNot Nothing Then                trans.Rollback()            End If            ' Log the exception            Throw
        Finally            If conn IsNot Nothing Then                conn.Close()            End If        End Try        ' we will only reach here if the order has been created successfully        ' so clear the cart        Profile.Basket.Items.Clear()    End Sub

View 4 Replies View Related

The Identity Of My Table

Mar 23, 2008

Hello,
I created a table with the identity column "ID". It will increase by 1 when I insert a row to the table.
However, when I wanted to publish my database with my application, I deleted all the rows in my table. At this time, I wanted the identity to begin with 1 again. Can I do it in sql server management studio or is there other ways to do it?
Thanks.

View 4 Replies View Related

IDENTITY ON CRETE TABLE HELP PLEASE!!

Sep 10, 2004

Below is the snippet of code that matters. Currently Figure 1 works great. I need to make Figure 2 work. Notice the Identity Seed vaue I need changed. Any ideas?


FIGURE 1:
CREATE TABLE _SMDBA_.Tmp__CUSTOMER_
(
SEQUENCE int NOT NULL IDENTITY (1, 1),


Figure 2:
CREATE TABLE _SMDBA_.Tmp__CUSTOMER_
(
SEQUENCE int NOT NULL IDENTITY ((SELECT NBRCOLUMN FROM TABLE WHERE BLAH = 'BLAH'), 1),


I need this desperately...
Thanks in advance.

Joe

View 2 Replies View Related

Inserting With DTS In IDENTITY Table

Oct 12, 2006

Hi

I need to insert values from a text-file to a table with a primary key as identity. In the text file I have no idea of the primary key values and i get "foreign key constraint violation" when trying to import null values into the column.

How can I solve the problem? With ordinary insert-statement there is no problem since the table generates identity- key values automatically. Is there a possibility to generate identity values with DTS-import?

Björn

View 1 Replies View Related

Two Identity Cols In Table

Jul 14, 2006

Hi,

How can we use two identity column (with identity property) in table ?

GK


gk

View 5 Replies View Related

Insert Into A Table With Identity

Aug 31, 2006

I have 2 tables with same structure.only difference is, TableA has key colum which is declared as identity starting from 1.

I want to insert data from TableB into TableA where TableB.Key is all null with TableA.Key starting from 9000001

when i try to do that iam getting an error
"An explicit value for the identity column in table 'lEADlOAN' can only be specified when a column list is used and IDENTITY_INSERT is ON.
"
even when I turned IDENTITY_INSERT ON for TableA

View 4 Replies View Related

How To BCP IN To Table With IDENTITY Column

Sep 8, 2005

I have fixed length records (167 bytes) in a .txt file and want to loadthis data exactly as is into a staging table where I hope to be able tolater get at selected columns using the SUBSTRING function. Here is mytarget table:CREATE TABLE JJA_BCP_NHO_DAT (RecID int IDENTITY(1,1) NOT NULL, Data VARCHAR(167) NOT NULL)Here is my bcp command:bcp DevMDW.dbo.JJA_BCP_NHO_DAT inN:RawData_MDSPurchaseUnzipArea2005_08_Aug_0149 7407.txt -f bcp.fmtHere is the .fmt file:8.011 SQLCHAR 0 167 "" 2Data SQL_Latin1_General_CP1_CI_ASHere are the first 3 lines of data in the .txt file (note how 1st 2bytes are blank; last 2 bytes of each record are 78, 79, 80):02160168C013CMA20050819 328UUU AGAWAM 36422005072901001 BERKSHIREBANK 25013 7801940155C001 MA20050805 254UUU AGAWAM 28072005071801001 WEBSTERBANK 25013 7902350188C014CMA20050729 067UUU AGAWAM 24812005070701001 FIRSTPIONEER FARM CR 25013 80The bcp command runs OK but the output is "shifted" in the columncalled DATA in the table. The IDENTITY column looks good in all rows,but only the first row is OK in the DATA column. Starting in the 2ndrow, all bytes are truncated or shifted by what appears to be 2characters. This is hard to show with pasting results but I ran thisquery:SET ROWCOUNT 10SELECT RecID, SUBSTRING(Data,1,10) as FirstTen, SUBSTRING(Data,158,10) as LastTen, DATALENGTH(DATA) AS LEN_Data FROM JJA_BCP_NHO_DAT1 02160168 25013 781672 019401 25013 167379 0235CR 250116743 80 01 CO 251675013 80 CORP 167625013 80 REG SYS 1677 25013 98ANK 1678 25013 VGS BK 1679 2501 21 MTG CO16710RP 25RYWIDE HOM167Every line in the .txt file ends after column 167 with x'0d0a'. I'vetried a field terminator of
and that produces this error:SQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encounteredin BCP data-fileThanks in advance for some help on this.

View 2 Replies View Related

Table Identity After Updated

Oct 1, 2007



Hello all,

I fixed a after update trigger with sql server 2005 and I need to return the last identity updated in the table to insert it into another table. How can i get the last Identity updated in a table?

Please help ..
Sms

View 11 Replies View Related

Identity Seed Reset In SQL Table

Feb 16, 2004

I have a test database that is being moved to the production server. Currently in one of the tables I have an identity seed for each record. Is there a way to reset it back to zero. I have deleted all my records but it still doesnt work, and I dont want to create a new table.

Thanks

View 5 Replies View Related

Identity Column In Temp Table

Jun 19, 2005

Hi,

I am trying to create a temp table with an identity column.  Here is the code that I am using...

SELECT UserId, IDENTITY(int, 1, 1) AS colId
INTO #User
FROM MyUserTable
WHERE UserId = 1

I am getting an error though.

Server: Msg 8108, Level 16, State 1, Line 9
Cannot add identity column, using the SELECT INTO statement, to table
'#User', which already has column 'UserId' that inherits the identity
property.

Is there any way to work around this? 

Thanks for your help.

View 1 Replies View Related

An Explicit Value For The Identity Column In Table

Sep 15, 2005

for some unknow reasons.. my store proc stop working.. and i got an error.. i have installaed the latest SP4 for SQL server 2000 and still have the problem !any ideas why ?? Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."CREATE procedure LCMS_Modules_Add
 @PageID int, @ModuleDefID int, @Panename nvarchar(32), @Title nvarchar(128), @Admins nvarchar(256)
as
insert into LCMS_Modulesvalues(@PageID, @ModuleDefID, 99, @Panename, @Title, '0;', @Admins, 0, '', 'Center', '', '', '', 1, 0)GO

View 1 Replies View Related

DTS Help - Identity Column In Target Table

Mar 26, 2001

Having an identity column in target table, how do I use SQL 7 DTS ?

In Import wizard, I chose 'ignore' in source, checked Enable Indenity Insert - DTS failed with a msg like - a column cannot have NULL...

In DTS package, had Fast load and Enable Indenity checked - no luck !

What would be the standard procs for this since this sounds like a very common
senario.

Appreciate your help very much !

-Ivan

View 1 Replies View Related

How To Fill Table With Many Of Identity Values?

May 17, 2004

I created table

create table t1
(
oid int identity(1, 1),
f tinyint
)

and need way to fill it as fast as possible with say 100000 sequential values. Field f may stay = 0 but oid should be raising from row to row. How can I do it in MSSQL 2000?

View 3 Replies View Related

INSERT Into Table With IDENTITY Column

Jul 31, 2001

I'm sure this is a common problem but I can't find any relevant info on this site

I have a table that I would like to insert values into. I want to take the values from a secondary table using a select statement.

e.g. a simplified version...


TABLE1
(
Id INTEGER IDENTITY,
UserName VARCHAR(50),
Description VARCHAR(50)
)

TABLE 2
(
UserName VARCHAR(50),
Description VARCHAR(50)
)

Runing the insert statement

INSERT Table1 (UserName, Description)
SELECT *
FROM TABLE2

results in the error
erver: Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'Id', table 'CDS_Live.dbo.Table1'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I was under the impression that an identity column would be automatically inserted by SQL server. Now I know I could write a piece of anonymous Transact SQL which declares a cursor by selecting all rows from table 2 and inserting rows into table 1 on a row by row basis, but is there any way I could do the Insert with a single INSERT statement?

thanks

neill

View 1 Replies View Related

How To Add Identity Property To Existing Table?

Mar 2, 2004

Has anybody ever tried to do this. I can't figure it out. All I want to do is take an existing table that already has values in the column that I want to change and add the identity property to yes and set the identity seed and increment to a specific number. I know you can do it in the CREATE TABLE statement but is there a way to use the ALTER TABLE command?

View 4 Replies View Related

Adding Identity For Existing Table

Apr 26, 2008

Hi Friends,
I have a existing table named as activity, and have the column like ID,Description. I want to add the Identity for the ID column using script only.. Have any ideas how to do in sql query analyser?

Thanks in Advance

View 7 Replies View Related

Reuse Deleted Identity In Table Again

Jun 19, 2014

I have a table in which there is a column of type identity(1,1),in the same table i have bulk data, and i have deleted a row from my table, so now i want to reuse that same Id again, how to do that.

View 3 Replies View Related

Insert A Field (IDENTITY) Into Table

Apr 25, 2007

Hi,

I have a table with 1000000 records i try to add a field with following`spec.

[mkey] [int] IDENTITY(1,1) NOT NULL

I get the following meassage:-

yearly' table
- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.

Then I Have the following error:-
Time out error

- How is the best way to copy a large table from one to anther.

regards

View 2 Replies View Related

How To Insert A Row In A Table With 1 Identity Column

Jul 20, 2005

HI,I have an SQL Server table with only 1 column. That column is anidentity column. How can I insert a row in this table using SQLsyntax?I tried insertinto T_tableName () values ()and a few other options, but I can't seem to get it to insert.ThanksAlain

View 1 Replies View Related

Adding Identity Column To BIG Table

Jul 20, 2005

I've got a table with 36+ million rows. I've been asked to modify thetable and add in an identity column. The code I used caused SQL tolock up and it maxed out the log files. :)The code I used is:Begin TransactionAlter Table ODS_DAILY_SALES_POSADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)CommitIs there a way to break up the code? Maybe only do a few millionrecords at a time? Or is there a way to do this without lockinganything up?Thanks,Jennifer

View 2 Replies View Related

Is There Any Way To Remove IDENTITY Property On Table?

Aug 1, 2007



Is there any way to remove IDENTITY property on particular table? I tried removing IDENTITY property using Manangement studio, but this operation behind the scene use migration concept that is by creating tmp table and then populating with data; droping the orginal and renaming the tmp back to original.

Second, i want some kind of generic solution using certain system table like aya.sysobjects, sys.identitycolumn etc such a way that i should be able to remove the idenity property from all of the table accross a database.

Mandip

View 5 Replies View Related

How Do I Find IDENTITY Columns On Table Using T-SQL

Mar 23, 2006

Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?

View 8 Replies View Related

Retrieving Identity Field From Table On INSERT

Feb 29, 2008

 I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost)  How do i obtain this value and how would I supply it to the second INSERT statement? 

View 3 Replies View Related

How Can I Alter A Table Turning ON Or OFF An IDENTITY Field ?

Nov 9, 2003

How can I alter a table turning ON or OFF an IDENTITY field ?

for example:
if I had my DB with Client_ID as an I IDENTITY field and for some reason it has
changed to just INT (with no IDENTITY) - how can I tell it to be IDENTITY field again ?


+

Does anyone knows an article on database planning ?
(I wanna know when should I use the IDENTITY field)

View 6 Replies View Related

Generate Identity Field On Existing Table

May 11, 2004

Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?

Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.

Thanks in advance!

View 3 Replies View Related

Restarting The Identity Column On Table Variable

Jun 2, 2006

In a cursor, I declare a table variable like so:
DECLARE @TempTable TABLE(RowID INT IDENTITY, valueID int)
I then insert into that table from another table.  The purpose is to get a list that looks like this after the insert:
RowID     valueID1               348972               345223               94822
etc....
However, the next time through my loop (cursor) I want to restart my RowID identity property, because the next batch of valueID's should then again have a RowID starting from 1.
I tried
delete from @TempTable  DBCC CHECKIDENT(@TempTable , RESEED, 0)
but I get 'Must declare the variable @TempTable table' error.
Is there a way to destroy and recreate that @TempTable  variable?
 
 

View 4 Replies View Related

How To Disable/enable An Identity Column In A Table

Jan 3, 2002

hi folks,
How to disable/enable an Identity Column in a table with the help of an sql
statement.
Thanks in advance.
Vineet Agarwal

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved