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 Repliesi 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 RelatedWhat 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
We are experiencing a problem at more than one site - it has only
just started happening.
We are actually getting duplicate Identity column values in various
tables and the values seem random - its not like the counter just gets
wound back.
We have used dbcc checkident and also bcp out and in the data, which
of course corrected the tables but only temporarily. Our application
is not doing any select into`s - just plain old inserts which for some
reason are allowing dups to be inserted into the tables.
problem sites are either sp3 or sp4.
There are thousands of sites (including most of ours) where there are
no problems like this with this particular app.
Any help from anyone would be most appreciated.
hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain it
If I have an Identity-declared column, the table has lots of rows and I start deleteting records - will any old, deleted identities be re-used (without an explicit re-seed) ?
(I'm talking about numerical Identities, not these GUID things)
hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain it
bye
Going from Oracle to SQL 2000 and want to repeat Oracle process whereby the job_id field is generated using a trigger and a sequence one-up generator. These two values are then combined to create the job_id number. The sequence is simple: a minvalue, maxvalue and increment 1. The trigger is generating the first five characters from yyddd and appending the next sequence value to it.
Example:
JOB_ID: 08088155
YY: (Year = 08)
DDD: (88th day of year)
Next sequence number: (155)
When I create the table in SQL 2000 I will identify the JOB_ID column as an IDENTITY type data field, but is there any way to duplicate this Oracle sequence in SQL?
Any help will be greatly appreciated.
Bill
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?
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?
Hi All,
This is my stored procedure
CREATE PROCEDURE testProc AS
BEGIN
CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT)
INSERT INTO #tblTest(Col1)
SELECT colA FROM tableA ORDER BY colA
END
This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5.....
or is there any chance of ID column having values like 1,2, 4, 6,7,8....
Please reply...
qa
This is a very basic question on replication.
I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.
Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.
Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-
1. I don't want to use GUID.
2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.
i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.
any help will be highly appreciable.
Is there any way to get the new identity values created after an OLE DB Destination has finished its inserts?
Basically my problem is this...
My source database has its primary and foreign key columns as a GUID datatype. In my conversion, my destination database will be using INT (identity) columns for primary/foreign keys. How do I propagate the foreign key relationships to the destination database in an SSIS package? So obviously I can use an OLE DB Destination to generate the data with identities for the parent table, but after that I am stuck in how to create the relationships for the child tables.
I know how to do this in TSQL fairly easily (cursor or temp tables), but it doesn't seem so simple in SSIS.
thanks in advance.
We are facing the following issue, several machines/users that are executing very often a command similar to :
INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo');
SELECT SCOPE_IDENTITY() AS Table_ID;
Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables
These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.
Howdy all,
I've run into an interesting scenario that I can't seem to resolve.
We have a table that we are using to create sequence ids. It's literally a table with a single field with the identity value turned on.
create table ident (seq_id int identity(1,1) primary key NOT NULL)
If I want to grab a new sequence id, I can execute
INSERT INTO
ident
DEFAULT VALUES
SELECT scope_identity() AS seq_id
All is well and good. However, now let's suppose that instead of just one, I'd like to get a range of values.
If the table had one other field in it, I could do the following
create table ident2 (seq_id int identity(1,1) primary key NOT NULL, placeholder char(1))
GO
;
WITH RECORDS AS
(
SELECT
CAST('A' as char(1)) AS col1
UNION ALL
SELECT
char(ascii(col1) + 1)
FROM
RECORDS
WHERE
col1 < 'C'
)
INSERT INTO
ident2
OUTPUT
INSERTED.seq_id
SELECT
NULL
FROM
RECORDS
Can this be done if there is only the identity column and if so, someone care to educate me?
how to reseed for selected basing on their last count.I have written a query to to reseed basing on last count.But for how to do 10 tables at a time .
declare @last int
select @last=max(empid) from Table_1
DBCC CHECKIDENT (Table_1, RESEED, @last)
but how to do for more than 10 tables or more tables...reseeding at one go basing on last count.
Arif writes "I want to insert values in to a table which is having identity column, how? can you help me out from this problem.
Thanks in advance
Jamal"
Hi Guys
Is there an easy way to pad identity column values with zeros to the left (0001, 0010,0100, etc) or I will just have to format them before displaying
George
Howdy all,
I've run into an interesting scenario that I can't seem to resolve.
We have a table that we are using to create sequence ids. It's literally a table with a single field with the identity value turned on.
Code Snippet
create table ident (seq_id int identity(1,1) primary key NOT NULL)
If I want to grab a new sequence id, I can execute
Code Snippet
INSERT INTO
ident
DEFAULT VALUES
SELECT scope_identity() AS seq_id
All is well and good. However, now let's suppose that instead of just one, I'd like to get a range of values. If the table had one other field in it, I could do the following
Code Snippet
create table ident2 (seq_id int identity(1,1) primary key NOT NULL, placeholder char(1))
GO
;
WITH RECORDS AS
(
SELECT
CAST('A' as char(1)) AS col1
UNION ALL
SELECT
char(ascii(col1) + 1)
FROM
RECORDS
WHERE
col1 < 'C'
)
INSERT INTO
ident2
OUTPUT
INSERTED.seq_id
SELECT
NULL
FROM
RECORDS
Is there a way to do this if there is only the identity column and if so, someone care to educate me?
From a design perspective, all I can say is "it's not mine" but I do have to live with it, or at least my coworker has to live with it. I'm merely trying to prevent them from opening a cursor and calling the default values version 45M times and no, that's not an exageration.
Hello,
We have a couple of tables that can have quite a bit of data each day prior to replication. Can we increase the default values for a table for each subscription? For example we have a table called table1 and on the sqlexpress client they could enter in 10000 rows a day, on table2 it's just 100 rows a day. How can we increase the values to where we do not get the error for table1 stating that the insert failed because it conflicted with the identity range check constraint. Thanks in advance.
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
I have a process to rollover prior quarter data to new quarter in a table.
For example, i have a table with (col1, col2, year, qtr) with data like ( Note: col1 is identity(1,1) )
1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
Now when i run my process, above 3 records will be rolled over new quarter 2014 Q2 and the table will be like
1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
4,'today',2014,2
5,'tomorrow,2014,2
6,'friday',2014,2
Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed )
Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.
Here, i have another table called "ident_map" with columns like (old identity, new identity ) and during rollover i am supposed to load ident_map table with old and new identity. So after rollover is complete, ident_map table should look like
1,4
2,5
3,6
I know using output clause I can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.
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 have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies View RelatedHi,
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
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 Replies View RelatedHow to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5
Hello all,
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
Any suggestions?
thanks for your time...
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies View RelatedI am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
View 6 Replies View Related