Identity Field Values - A Simple One!

May 20, 2000

To All,
I have a table [multiple entries over the web] where the identity field values do not appear in order using a simple SELECT [no ORDER BY]. Why?

View 2 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

Conditionalize Field Values Based On Other Field Values

Apr 17, 2007

Here's a portion of the current statement.

UPDATE EngagementAuditAreas

SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,

numInherentRiskID = parent.numInherentRiskID,

numControlRiskID = parent.numControlRiskID,

numCombinedRiskID = parent.numCombinedRiskID,

numApproachTypeId = parent.numApproachTypeId,

bInherentRiskIsAffirmed = 0,

bControlRiskIsAffirmed = 0,

bCombinedRiskIsAffirmed = 0,

bApproachTypeIsAffirmed = 0,

bCommentsIsAffirmed = 0

FROM EngagementAuditAreas WITH(NOLOCK) ...

And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).

UPDATE EngagementAuditAreas

SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,

numInherentRiskID = parent.numInherentRiskID,

numControlRiskID = parent.numControlRiskID,

numCombinedRiskID = parent.numCombinedRiskID,

numApproachTypeId = parent.numApproachTypeId,

bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),

bControlRiskIsAffirmed = (numControlRiskID IS NULL),

bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),

bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),

bCommentsIsAffirmed = (parent.txtComments IS NULL)

FROM EngagementAuditAreas WITH(NOLOCK)

Thanks.

View 1 Replies View Related

Converting An Integer Field Into An Identity Field

Sep 14, 2006

I 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





View 1 Replies View Related

Change Field To Identity Field

Aug 20, 2007

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 8 Replies View Related

Very Simple Question: Cannot Create Table With Identity Fields Using SQL Sentences

Jan 26, 2007

Hi, I'm trying to build a several tables using sql ce 3.1
I refer to the manual on Create Table:
ms-help://MS.SSCE.v31.EN/ssmprog3/html/143cad25-5c1d-4c96-bd8b-6a95dbfb1b00.htm

The sample:
CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY,
CompanyName nvarchar (50))

just simply doesn't work displaying this error:

SQL Execution Error
Executed statement: CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY,
CompanyName nvarchar (50))

Error Source: SQL Server Compact Edition ADO.Net Data Provider
Error Message: There was an error parsing the query. [Token line number =1,
Token line offset 40, Token in error=IDENTITY]


I tested against a SQL Desktop, everything is OK.
What's wrong with the sentence?
Please some advise or workaround will be appreciated
Thanks
MSCD Fernando Zorrilla de San Martín

View 4 Replies View Related

Really Simple Query Needs Another Field

Nov 5, 2007

I have written this query in SQL

select * from wce_contact
where (Mail3Date <= '2007-09-25')
AND NOT (Mail1Date is NULL )
AND NOT (Mail2Date is NULL )
AND (Mail4Date is NULL )
AND (Mail5Date is NULL )
AND (Mail6Date is NULL )
AND (Mail7Date is NULL )
AND (Mail8Date is NULL )
AND (Mail9Date is NULL )
AND (Mail10Date is NULL )
AND (Mail11Date is NULL )
AND (Mail12Date is NULL )
AND (Mail13Date is NULL )
AND (Mail14Date is NULL )
AND (Mail15Date is NULL )
AND (Mail16Date is NULL)

Little bit of background info - We have a database of over 500k websites with email addresses. We use email marketing to contact these sites and everytime they get mailed i need to fill in the MailDate field. Obv Mail5Date means they have been emailed 5 times etc The above qry is trying to find all contacts that have been mailed 3 times and havent been mailed in the last 42 days.

I also have an IDStatus field that allows me not to use the contacts that have opted out our mailing or website shutdown etc I try adding this to the bottom of the query

AND (IDstatus is Null)

But the query runs and finds 0 contacts which i know isnt the case, as when i use our front end database application it produces results.

I was wondering if anybody could possibly help me?

Thanks!

View 20 Replies View Related

&<&<identity Field&>&>

Jul 2, 2006

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 Related

Re-set Identity Field

Nov 7, 2007

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 1 Replies View Related

Sql Identity Field

Mar 10, 2008

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 2 Replies View Related

Identity Field

Nov 5, 2005

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 Related

Bcp With IDENTITY Field

Apr 2, 2001

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 2 Replies View Related

Identity Field

Jul 12, 1999

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 1 Replies View Related

Identity Field

Jul 9, 1998

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 3 Replies View Related

Identity Field

Apr 19, 1999

I need to update an identity field that I have on a table. How can
I go about doing this?

Thanks!
Toni

View 1 Replies View Related

Identity Field

Mar 8, 2004

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 3 Replies View Related

Identity Field

Jun 29, 2007

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 2 Replies View Related

Identity Field Fix

Mar 14, 2007

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 6 Replies View Related

Best Way To Store Simple Up Or Down Values

Jul 20, 2005

I'm creating a table for maintenance records.In each record, many of the values are simply checkboxes (on the UI).In the database table for these attributes, is a good way to store thestate of these checkboxes as simple as 0 for false, 1 for true?-David

View 6 Replies View Related

Identity Values

Nov 15, 1999

I have a database that has an Identity field in one table which has duplicate values. how can this be? and is there any way that this can be adjusted or corrected. (this field is used as the primary key).....UG!

View 1 Replies View Related

Returning Identity Field

Jun 12, 2006

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 1 Replies View Related

Replication With Identity Field

Apr 17, 2000

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 3 Replies View Related

Identity Column Is The Only Col In The Field

Dec 6, 2001

I have a table which has a single column ,which is an IDENTITY colum.How do you insert values in it by using an insert statement.Thanks in advance

View 2 Replies View Related

How To Rearrange The Identity Field

Jan 12, 1999

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 3 Replies View Related

Identity Field And IDENTITY_INSERT Is Off

Nov 14, 2005

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 1 Replies View Related

Identity Field With A Query ?

Sep 14, 2006

I want to kown if a field is an identity (counter) using a query or a stored procedure ?


Thanks

View 1 Replies View Related

Identity Field Issue

Jun 22, 2007

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 1 Replies View Related

Using An Identity Field As Primary Key

Jul 20, 2005

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 9 Replies View Related

SQL Server Identity Field

Jul 20, 2005

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 5 Replies View Related

INSERTING Into IDENTITY Field

Nov 15, 2006

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 7 Replies View Related

Getting Last Inserted Identity Values

Jan 2, 2008

 i have a website that accepts users on it. first the user will apply to make use of my site and the data that he supplied will be put to account table. my problem is how can i get the last inserted identity value lets say id, to create the id of the person applied by simply incrementing it... i dont want to use the built in function of the sql server. can anybody help me of this process. asap...

View 5 Replies View Related

Resetting Identity Values Of PKs

Sep 21, 2000

What is the easiest way in T-SQL to reset the identity values of
the PK columns in a DB. Here's the scenario.

I'm going to be backing up a HUGE DB and then restoring
it to a new server. Once there I will run an SP to remove all the existing
data. The tables are now row-free. However my customer doesn't like that
the first record they add gets PK value of 5,321, or whatever.

After I remove all the data how can I reset the ids?
It would be nice to add some code to my sp_cleardb
that would go back and reset the identity columns
FYI
All of my tables use auto-incrementing identity fields with a seed of 1

1) I know how to do it in enterprise manager, but I need it in scripting
since I'll have to do it on hundreds of tables
2) I tried using alter table to drop the PK constraint, but that requires
knowing the pk constraint name, which is SQL generated and I won't know.

I think I got very close, but the code was just getting too big. I thought,
there must be an easier function to use, or a built in SP, or something?

Anyone know how to script this?
THanks in advance.
Josh

View 2 Replies View Related







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