Identity Field With A Query ?
I want to kown if a field is an identity (counter) using a query or a stored procedure ?
Thanks
View Complete Forum Thread with Replies
Related Forum Messages:
SQL Query Not Returning Identity Field - Sometimes
Hello, I have a C# application that adds records to a SQL Server database using a query something like this one: INSERT INTO table_name (first_name, last_name, date_added) ('john', 'smith', '1/1/2005 12:00:00pm') ; SELECT SCOPE_IDENTITY() AS [Scope_Identity] This works fine unless there's already a John Smith in the database. When that happens, Scope_Identity is null even though the date_added is different. About half the time the record is added even though Scope_Identity is null. I've added code to notify me when this happens, but it's a pain in the neck to re-run my import utility for individual records. (The table I'm adding to does have a autonumbered key field) Thanks in advance!
View Replies !
Insert Row In Table With Identity Field, And Get New Identity Back
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 Replies !
How To Change Integer Field To Identity Field.
I have an integer column(not null) in a table. how can i change that to identity column through query analyzer, through a script, b'cos i have to apply this to number of databases. I tried this, but got an error: ALTER TABLE table_name alter column company_id int IDENTITY ERROR: Incorrect syntax near the keyword 'IDENTITY'. any help is welcome.
View Replies !
Last GASP On &&"Insert Row In Table With Identity Field, And Get New Identity Back &&" ?
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 Replies !
Change In Pk Field Into An Identity Field - How?
Ok, simply problem. I have a table that has an in primary key field. I want to (need to) set this up as identity, through a script. Is it right that there is no (working) alter table statement for this? After trying for two hours I finally just started to look on how Enterprise Manager handles this. And - EM basically... ...creates the new table, ...sets IDENTITY_INSERT to ON, ...copies the data over (select *, insert) ...deletes the old table and renames the new one. Frankly, this is not really nice. Anyone a better idea?
View Replies !
Change Field To Identity Field
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
View Replies !
Sql Identity Field
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
View Replies !
Re-set Identity Field
Hi: 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
View Replies !
&<&<identity Field&>&>
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 Replies !
Identity Field
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 Replies !
Identity Field
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,
View Replies !
Identity Field
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
View Replies !
Identity Field
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
View Replies !
Bcp With IDENTITY Field
If 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!
View Replies !
Identity Field Fix
I inherited a system with a SQL 2000 DB. We discovered an identity field named barcode with some values that are incorrect. About 1000 of the records contain a barcode field with 13 digits, not forteen as required. This field is a standalone field only used on an ID card. I would like to select those 1000 records and update the barcode field to 14 digits. Is there an easy way to do this? Thx
View Replies !
Identity Field
Hello... New to SQL and have the following situation.... Example of table (currently) PayID (Primary Key) PaymentNumber (Identity Field) John 1 Tim 2 Chris 3 John (same as John above) 4 Jack 5 Steve 6 Would like to see it work this way PayID (Primary Key) PaymentNumber (Identity Field) John 1 John (same as John above) 2 Tim 1 Chris 1 Jack 1 Steve 1 Can the identity column be setup to start the counter over depending on value of PayeeID...If another Jack was added then the Payment Number would be 2 for Jack since there is already a record with value 1.... Hope this makes sense... Thanks
View Replies !
Returning Identity Field
Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot my sproc: CREATE PROCEDURE ng_AddCotacao(...@Codigo_cotacao int OUTPUT)ASBEGINSET NOCOUNT ONINSERT INTONegocios_cotacoes(...)VALUES(...)SELECT @Codigo_cotacao=SCOPE_IDENTITY()SET NOCOUNT OFFENDGO class file public class Cotacoes { public int codigoCotacao; } public class CotacaoAtualiza { public Cotacoes cotacoes = new Cotacoes(); public CotacaoAtualiza() { } public void AdicionarCotacao( ... ) { SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]); SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection); myCommand.CommandType = CommandType.StoredProcedure; ... SqlParameter paramCodigo_cotacao = new SqlParameter("@Codigo_cotacao", SqlDbType.Int, 4); paramCodigo_cotacao.Direction = ParameterDirection.Output; myCommand.Parameters.Add(paramCodigo_cotacao); ... myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader(); while(result.Read()) { this.cotacoes.codigoCotacao = (int) result["@Codigo_cotacao"]; } myConnection.Close(); } calling into code-behind file: CotacaoAtualiza ca = new CotacaoAtualiza(); Cotacoes cotacoes = ca.cotacoes; Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);
View Replies !
Using An Identity Field As Primary Key
I want to use the Identity field (increment 1,1) as a primary key andhave a unique constraint on my other field which is of type char.I am worried that related data in other tables may lose referntialintegrity if records in the ID table get messed up and need to bere-entered.Can you please advice on best way to do this. I definitely need anumeric id field because it makes the joins and queries so muchfaster.
View Replies !
SQL Server Identity Field
I have an identity field on a table in SQL Server. The identity seedis 1 and the identity increment is 1. If I remove a record from thistable, the identity sequence is broken. For example:Table contents prior to record delete:Fname(varchar), Lname (varchar), row_id (identity)--------------------------------------------------Smith, Jane, 1Smith, Tom, 2Jones, Mark 3Delete from mytable where row_id = 2Fname(varchar), Lname (varchar), row_id (identity)---------------------------------------------------Smith, Jane, 1Jones, Mark 3How can I re-set the identity field so that there are no gaps in thesequence?For example, I want to update the sequence after the delete so thatrecords look like this:Fname(varchar), Lname (varchar), row_id (identity)---------------------------------------------------Smith, Jane, 1Jones, Mark 2
View Replies !
SQL Server Identity Field
>> I have an identity field [sic] on a table in SQL Server ... If Iremove a record [sic] from this table, the identity sequence isbroken. <<Your problem is that you still think in terms of files, records, andfields and do not understand an RDBMS model of the world. A real SQLprogrammer does not use IDENTITY for a host of reasons. What you aredoing in SQL is mimicking a sequence magnetic tape file, circa 1950.Get a book and learn the basics before you write any more code.
View Replies !
Identity Field And IDENTITY_INSERT Is Off
ok, so i get this error, Exception Details: System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'TBL_LAPTOP_BOOKINGS' when IDENTITY_INSERT is set to OFF. i ran SET INDENTITY_INSERT TBL_LAPTOP_BOOKINGS ON before i ran my script... and i also checked, we are running SQL 2000 with service pack four, because of the cursor error.... so im just wondering, is there anyway to make identity_insert to always be on, or any other way to have a unique id in my booking_id field?? Thanks Guys, Justin
View Replies !
How To Rearrange The Identity Field
Dear All, Please help me! I have a table will a identity field the value are, for example, 1, 2, 4, 6, 8, 10, 11 .... etc. I want to make it in a sequence, that is, 1,2,3,4,5,6,7... How can I do it, Please help me, thank you for you helping. Best Regards, J.H.
View Replies !
Replication With Identity Field
1. Can someone tell me how to replicate the identity field. 2. When I use the 'NOT FOR REPLICATION' option, I keeps getting synx error. Here is the command: alter table tusers alter column {userid INT} IDENTITY (1, 1) [NOT FOR REPLICATION]. Do you know why? 3. When I use the transaction replication wizard, why some of the table do not allow me to replicate(There is a key and cross symbol on the articles selection)? Thanks
View Replies !
INSERTING Into IDENTITY Field
I have a table with the following schema: CREATE TABLE [itis].[wrk_taxon_authors] ( [wb_taxon_author_id] [int] NOT NULL , [taxon_author_id] [int] IDENTITY (1, 1) NOT NULL , [taxon_author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [kingdom_id] [smallint] NOT NULL ) ON [PRIMARY] GO I am trying to insert the following data (as you can see fields are seperated with the | ): 19||Flores-Villela and Sánchez-H., 2003|5| 20||Wiegmann, 1828|5| 16|17482|Gray, 1838|5| 17|9823|(Wiegmann, 1828)|5| I get the following error:Server: Msg 4869, Level 16, State 1, Line 1 Bulk Insert failed. Unexpected NULL value in data file row 1, column 2. Destination column (taxon_author_id) is defined NOT NULL. Server: Msg 4869, Level 16, State 1, Line 1 Bulk Insert failed. Unexpected NULL value in data file row 2, column 2. Destination column (taxon_author_id) is defined NOT NULL. Since I have defined column 2 as an identity column, I don't understand why SQL Query analyzer is upset when I do not have a value in that field. To me, I would think it would auto-insert an integer (starting at seed 1 and incrementing by 1), but it doesn't. Could someone tell me what I'm doing wrong?
View Replies !
Identity Field Issue
Hello, I have a field in my table called "MyNewCounter", where the data type is int. Unfortunately I set the identity field to "No" instead of "Yes" on this field. Since this time a few thousand records were imported in and this field is now set to a value of null for all records. When I tried to change the identity to "Yes" and save the table, I get an error indicating that the field can not contain a null value. If I populated this field with numeric values, would this correct my issue? and if so, what SQL command would I use to set the field value starting at 1 to increment for each existing record?
View Replies !
Select Statement With A New Identity Field
Hello, Is it possible to generate a identityfield dynamically upon select, like this: SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC I want the output to be: 1 - Name12 - Name23 - Name3 The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes. Like: SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC I want the output to be: 1 - Name32 - Name23 - Name1 Patrick
View Replies !
Out Of Order Identity Field - Sql2000
Hi AllI am finding unexpected results when inserted into a newly createdtable that has a field of datatype int identity (1,1).Basically the order I sort on when inserting into the table is notreflected in the order of the values from the identity field.Have I been wrong in assuming that it should reflect the order from thesort?The code is ...create table tmp (A varchar(50), L float, C int identity(1,1))insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Lland I don't understand why the values in tmp.C aren't in the ordersuggested by the sort.Any comments most appreciatedBevan
View Replies !
Manage Identity Field At Subscriber
Anyone has the standard approach for handling the identity column with replication environment. Currently i am using following process. http://www.databasejournal.com/features/mssql/article.php/3483421 I want to know if someone have some generic script to do this or much easier way..
View Replies !
Return The Lastest [Identity] Field Value
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.
View Replies !
How To Get Identity Field Value After Recordset Update
I want to add a record and then get the automatically generated identifier(ReqChildID in this case). Is there a better way? Why won't this one work. This is what I have: rsRec.AddNew rsRec("RequisitionID") = intReqNum rsRec("CreatedBy") = cint(session("empno")) rsRec("DateCreated") = Now rsRec.Update intItemId = rsRec("ReqChildID") rsRec.Close Forgive me if this has already been posted, but I couldn't find it. [edit to add:] rsRec is an ADODB.Recordset in case that wasn't clear.
View Replies !
Resetting Value Of Identity Field After A Delete
Can anyone tell me of a way to reset the value of an Identity field back to its seed value after all the rows in a given table have been deleted and I try to re-populate it? There are foreign key relationships so I cannot just drop and recreate the table, and I really need to address this issue from within sql, rather than from the application. Thanks, any and all guidance would be great :)
View Replies !
Alter Table For Identity Field
Hi all, Pls help on this one, I think should be very simple, but I donno how to do it..:( anyway, here is my problem, I like to replicate a DB, and there is some table which got identity field on it, so I can't replcaite those, and as I read the help, it said that if I put in the "NOT for replication" option for the identity field, then it should work, my question is how can I alter the table for the alter table function? can someone pls tell me the sctipt how to do it? all i know is: Alter table <table_name> <column_definition> identity(1,1) not for replication but what is the column_definition? I donno what they mean here... pls help ppl.. an another question (sorry for too many question), if that identity is replicated, is that mean from the publisher inserting then the number will replicate to the subscriber right, but what happen for the subscriber doing the insert, will that just replicate the number back to the publisher? and what happen if they do insert at the same time, I know there will be a conflicts and it can be solve somehow, but can it be auto add to the next auto number, so it won't be a conflict but in fact both record gets in? is the possible? thanx for all the help ppl. Thanx, Westley
View Replies !
Change/issue A New Id For IDENTITY Field
Hey guys, I have a content table with a field "[Content_ID] [int] IDENTITY(1,1) NOT NULL". I need to change the content id of some of the content because it was leaked into an xml feed and we want to stop people from accessing the content, is it possible to change/issue a new Id for a field that is set as "IDENTITY"?
View Replies !
Change/issue A New Id For IDENTITY Field
Hey guys, I have a content table with a field "[Content_ID] [int] IDENTITY(1,1) NOT NULL". I need to change the content id of some of the content because it was leaked into an xml feed and we want to stop people from accessing the content, is it possible to change/issue a new Id for a field that is set as "IDENTITY"?
View Replies !
Insert A Field (IDENTITY) Into Table
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 Replies !
Retrieving Identity Field From Table On INSERT
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 Replies !
Generate Identity Field On Existing Table
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 Replies !
How Can I Alter A Table Turning ON Or OFF An IDENTITY Field ?
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 Replies !
|
TRACKER
