Incrementing And Concatentating Non-Auto Incrementing Fields
Mar 11, 2008
My SQL table has the
following columns:
ID – Auto Incrementing
Number_of_Records – Int
Account_Number – Varchar
(Format 1234)
Account_Number_Instance
– Varchar
(Format e.g. 1234-01)
Other_Field1
Other_Field2…etc.
When the Account_Number was
initially inserted into the table, a Stored procedure (not written be me)
was used to concatenate the Account_Number
and Number_of_Records.
As a result
the
Account_Number_Instance became,
e.g. 1234-01, etc.
From the ASPX form we
retrieve each of the fields above, and based on the Number_of_Records an
appropriate number of records were inserted for the Account_Number.
For Example:
Account_Number = 12345
Number_of_Records = 4
The result inserted into the
table were as follows:
ID
# of Records
Acct_Number
New_Num
Acct_Number_Instance
…
1
4
12345
12345-01
2
4
12345
12345-02
3
4
12345
12345-03
4
4
12345
12345-04
Now, I would like to UPDATE
this table, and based on the original Number_of_ Records (4) I want to ADD, for
Account_Number (1234) add an appropriate Number_of_Instances, beginning with
the next incremental number and adding what the New Number of Records input is…
For Example:
Account_Number = 12345
New_Num(ber_Of Records) = 3
ID
# of Records
Acct_Number
New_Num
Acct_Number_Instance
…
1
4
12345
12345-01
2
4
12345
12345-02
3
4
12345
12345-03
4
4
12345
12345-04
5
12345
3
12345-05
6
12345
3
12345-06
7
12345
3
12345-07
I
do realize that the next time I have to update this Account_Number I am also
going to have to somehow ADD only one of the #_of_Records and New_Num for the
Acct_Number instances to obtain the next starting number, which in the example
above would be (8)…
Any
and all suggestions on how to accomplish this would be greatly appreciated!
I have a database table. I need to set a particular data field to allow for Auto Incrementing. Basically I need to set up the initial integer value to start at. Then to specify the amount to increment by. What I have done so far is to right click on the field name in SQL Server 2005. Then, on the pop-up menu I select the menu option for properties. When the properties are displayed I see some fields listed in dark grey but I am not able to edit these fields. In other words, if I try to type text in this field, nothing happens. The fields I am talking about are: Identity Increment Identity Seed Is Identity Does anyone know what the problem is? What do I have to do to edit these fields.
Here is a simplified version of my problem: I am inserting data into a table using a stored procedure. The table has an identity column that increments with each insert. When I use erroneous data in the other fields the insert fails….no surprises there! But when the next insert occurs with valid data I find that my identity field has increased even with those inserts that failed, so my sequence has jumped a few numbers. How do I get the identity inserts to roll back if the rest of the data in a row doesn’t insert successfully? Marcha x
I am porting a database from Informix to SQL Server 2000. Several fields in the informix database are specified as SERIAL, which means that they auto-increment auto-magically if you insert a 0 into them. Well...I was looking for an equivalent in SQL Server 2000, and thought I had found it in the IDENTITY column concept.
With further investigation into IDENTITY columns, however, I discovered that only one column per table can be given the IDENTITY property. Why is this? Is there a way around that? If not, is there another way to achieve this feature in SQL Server 2000? If not, any suggestions on how I can do it?
My Identity value column is not autoincrementing, its seed is set to 1, and increment set to 1. yet when i click the auto generated 'Add New Record' button in the BindingNavigator control set (in a form where it is populated with databound text boxes), this Identity column is not auto incremented?
And if i try to write a value to this column, i get the error telling me this column cannot be written to. Yet if i do not write to it, then i get "This column does not allow nulls"
To load my data i am using:
Code Snippet 'clear dataset Me.DsQuote.Clear() 'fill the datagrid with data from tblQuoteID Me.TblQuoteIDTableAdapter.Fill(Me.DsQuote.tblQuoteID)
and to save data im using:
Code Snippet Me.Validate() 'end data edit Me.TblQuoteIDBindingSource.EndEdit() 'update the dataset with table data Me.TblQuoteIDTableAdapter.Update(DsQuote.tblQuoteID)
anything im doing wrong? this data is being viewed in a datagrid btw.
Hi, I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?
Hi All, I need to set up a kind of identity insert that gives an output in the format: 00001, 00002, 00003 etc. Is there a formatting option for this sort of output using normal identity insert features or do I need to write a function to insert these values (perhaps as text) each time a new record is created? Sorry if this is really simple but it's only my 4th day in this job! Marcha
We have upgraded our SQL 2008 server to 2012 last month. i've noted since then that many tables that have auto increment primary key bigint field increases by 1 like it should, then for some reason it jumps up by 1000 or even 10000? i have seed adn auto increment set to 1 but doesnt effect it. Is there anything that could be causing the next value to jump that much?
If i were to replicate a database with another where in both the databases have an auto incrementing field. What could be the problems that's goin to arise out of it. How to deal with such a scenario...?????
hi there, i am new to sql server database.i am doing small projects right now using asp.net and sql to create webpages (very basic webpages) My problem is:
Problem :
i have two tables .....table 1 and table 2.
Table 1 has following fields: studentid,student name,student address.
Table 2 has following fields:studentid and course . table1 student id is the primary key refrencing table 2 student id. Now i delete a record in table 1 which will in turn also get deleted in table 2 . so for eg if i have three records 1 ,2 and 3 ....then i delete 2 in table 1 ...i will have 1 and 3 in both table 1 and table 2....now i want 3 to become 2 in both table 1 and table 2...so that i dont have empty space between two student id's 1 and 3. so this is my problem....if any one can help me out with suggestions please do.
Along with the data that I am pulling back from query, I also need to provide an ImportID column with the following requirements:
YYMMDDGP0001, YYMMDDGP0002, YYMMDDGP0003, and so on. The 0001, 0002, and 0003 part could get big into the hundreds of thousands.
I have the YYMMDDGP part down with the following expression:
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) + 'GP' AS [ImportID]
Now I need to get the Auto Incrementing number part of this. I have been researching this trying SELECT INTO a temp table using the Identity Function and declaring different variables all with no luck.
I need some help for designing the IDs / Primary keys for some master tables in my database. Following are master tables. Client_Master, Buyer_Master & Seller_Master; I want to set Client_Id, Buyer_Id & Seller_Id as their respective primary keys and they should have following properties
Client_Id :- a) should be auto-incrementing value, b) unique & c)should be of the format – CLXXXXXX, where “CL” {Constant start characters} & “X” {any number 0-9} Similarly:: Buyer_Id :- BYXXXXXX Seller_Id :- SLXXXXXX
We are implementing the database in MS-SQL 2005 & MySQL
Can anyone help me find a solution to this, especially in MS-SQL.
I need to auto increment an alphanumeric field in a table.
The process is as follows:
1. First position is static letter C for contact or static letter A for account - field contact_id 2. The following 6 positions are numeric - example of the string would be C004658 3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659
I currently have a table which has around 60000 records, within the table there is a field which is acting as the Primary but the numbers are not in consecutive order any more (due to deletion), and i need a SQL Script that will start at the beginning of the records and UPDATE (STK_LOCATION) and SET the field (LOC_PRIMARY) to 1 on the first record and then go through the rest of the table setting the same field to +1 of the record before it.
I have just started using SSIS this week. I am experienced with a different ETL tool (DataStage).
What I am trying to accomplish is the following:
Read the MaxValue of a Key from a Table and match to a stream of data using a LookUp stage. Increment the value of each row for insertion to the table where I obtained the MaxValue.
What would seem to be a very simple procedure (I could knock this out in 10 minutes with DS...of course I have more experience with that toolset) is problematic for me. I would just use a Transform stage and add the RowNumber variable to the MaxValue for each row. The stream would be ready for Insertion to the target table.
I am currently trying to use a Script Component in the Data Flow. Since my VB is rusty, does anyone have a script for incrementing a ReadWrite Variable for insertion to the stream? All I want to do is take the MaxValue and add the RowNumber or increment by 1 each time the script is performed.
If anyone has a simpler method, please let me know. Also, before it starts, yes we are using the Identity feature on the table, however due to processing constraints we need to preassign the Key in our Data Flow. Hence the incrementing of rows....
Does anyone know why an error occurs (constraints violated) when I try to have a table with an automated id incrementing by 1. I am uploading the data into the table from dts, so ignore the id field in the dts transformation.
Any ideas or help would really be appreciated, do I need to reference the id field in an activeX script?
Hello, I am making adjustments to a table in my SQL Server Database. I'm adding a collumn to my database of numeric type. I wish to write a SQL script that will go through every record in the database and give it a unique number (key) by simply incrementing each number by 1. I've inherited this database and as you have probably guessed i'm trying to add a primary key to this table as currently there isn't one!
I have no experience what so ever with T-SQL (which I presume i will need to use) as my expertise lie in web development with fairly simple SQL so go easy on me :)
Does anyone know why an error occurs (constraints violated) when I try to have a table with an automated id incrementing by 1. I am uploading the data into the table from dts, so ignore the id field in the dts transformation.
Any ideas or help would really be appreciated, do I need to reference the id field in an activeX script?
I have two columns in a table right now. My query is inserting data into this table from another so that non technical users can view this database value via excel. One column shows the date and the other shows a numeric value. This value grows daily and is used to determine when a reseed of the database is needed. I would like to add a third column to this table that shows the difference in growth. For example if 9/19 had a value of 1000 and 9/20 had a value of 1100, the third column would have a value of 100. My plan is to create a column in excel that calculates the number of days until the reseed is required based on average growth. It would be much easier to get this difference in growth from the database than through an excel formula.
Im not sure if this can/should be done through sql...but i have a sql statement and i need the first column returned to start at 1 - and then increment for each row.
I am then using the returned values in another app.
I tried using count(columnname) but this just stays at 1 for each row.
Hi everyone. I'm a new user to ASP.NET using VB.NET and SQL Server.
I have one question.
I am currently creating a website for my dissertation whereby whenever a customer purchases a product, a number is incremented within the 'Product' table. The column that needs to be incremented is called 'ProductSold'.
I have got an Insert query to store the orders in the 'Orders' table already within my webpage. Is there a SQL Query that I could use to increment the number in the 'Product' Table straight after my Insert Query?
I really appreciate if anyone could help me in this matter!
I have a field called contact_id in a table. Datatype is integer and has an identity seed of 1 and an identity increment of 1. When new records are created, the increment sometimes skips numbers. (ex. 12,13,18,21). What's going on here? Am I losing records somewhere?
I have a field called contact_id in a table. Datatype is integer and has an identity seed of 1 and an identity increment of 1. When new records are created, the increment sometimes skips numbers. (ex. 12,13,18,21). There is absolutely no deletion of any kind going on. (i.e. My application (cold fusion) has no code whatsoever that is deleting records.) What's going on here? Am I losing records somewhere?
Help, I'm stuck! I have a Customer table:ID Name-- ---------------------1234 Christopher's Surf Boards4321 Christina's NailsI have to build a Account table:ID GPID-- --------------------1234 CHRIST004321 CHRIST01I've built a function that gives me the six alphas and concatenates thetwo digits. The GPID in the account table is the first six alphas ofthe company name plus two digits to ensure uniqueness. (Don't ask why Ican't use the existing id -- the story is long and boring.) I built afunction that creates the alpha part and assigns the first 00. I builta second function that I thought would check the Account table as I wasinserting and increment the numbers if the account number alreadyexisted. It doesn't work. In the above table, both IDs come out asCHRIST00. Here's the second function:ALTER FUNCTION fnValidateID(@mString varchar(15))RETURNS varchar(15)ASBEGINDECLARE@sTemp varchar(15),@sInc varchar(2),@iInc intBEGINset @sTemp = @mStringwhile exists (select GPID from dbo.tempID where GPID = @sTemp)beginset @iInc = convert(int,right(@mString,2)) + 1set @sInc = convert(varchar(2),@iInc)if len(@sInc) < 2 set @sInc = '0' + @sIncset @sTemp = substring(@sTemp,1,len(@sTemp) - 2) + @sIncend--NEXTENDRETURN @sTempENDMy first guess is that the code is okay, so far as it goes, but that Ican't check the values in the table at the same time I'm inserting intoit. (The Account table is empty, initially.) In any case, I can'tfigure out where I've gone wrong.Thanks.
I could do this even with my limited knowledge of SQL but I'm surethere's a slick way that might be dead easy. My way hardly seems worththe effort . If not thanks anywayCol1Col2Col3QWQWQWQAQBQBQXWQXWQXWQXAQXBQXBEvery time there is a change of group (Col1 and Col2), I want to startand increment Col3 so I end up with;Col1Col2Col3QW001QW002QW003QA001QB001QB002QXW001QXW002QXW003QXA001QXB001QXB002
What if we needed to enforce a constraint of "01" and not just a "1" single digit. It's more than a display issue. I need the value to always be two digits regardless of value.
