How Do I Update A Column To Be Incrementing Each Row By 1
Apr 2, 2008I have a temp tbl and in col1 row1 i would like to have 1, col1 row2 value 2, col1 row3 value 3 and so on. how can i do this?
View 3 Replies
ADVERTISEMENT
Incrementing Column
Feb 28, 2008Hi,
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.
Thanks for an feedback...
Incrementing And Concatentating Non-Auto Incrementing Fields
Mar 11, 2008My SQL table has the
following columns:
ID – Auto IncrementingNumber_of_Records – IntAccount_Number – Varchar
(Format 1234)Account_Number_Instance – Varchar
(Format e.g. 1234-01)Other_Field1Other_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 = 12345Number_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!
Thanks
Identity / Auto Incrementing Column
Feb 16, 2006Hi,
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)?
Incrementing Dates And Displaying Range As Column
Mar 19, 2013I'm using SQL Server 2000 sp2...I have created a view that gives me customer info from which I need to create a view and or table that gives me a 24 monthly columns of the sum of each account_number monthly revenues (going back 24 months from this month)..The columns I'm pulling from are these:
Customer_Name
Account_number
First_insert_date
Order_net_price
Here's what I have so far:
----------------------------
SELECT TOP 100 PERCENT Account_number, Customer_Name, SUM(Order_net_price) AS 'CM - 24'
FROM dbo.Customer_Feed
WHERE (First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 24, 0)) AND (First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 23,
0))
GROUP BY Account_number, Customer_Name
ORDER BY Account_number
I've basically hacked out a way to get the monthly totals for each account. What I would like to do is so be able to repeat the query but increment the date range 1 month until reaching the present or last FULL month and display these sums in individual columns named CM - n (where CM means current month and 'n' is the # of months back from current.how to make this query run over again the 23 other times I need it to and display those months sum totals in individual columns.
Incrementing Number In A Column -- Rookie Sql User
Jul 23, 2005Sorry to bother you guys with what I though would be an easy task. Ihave a table in my database were I would like one of the rows toincrement a number for each row. I want the first row to start at 1000and keep on incrementing by 1 'till the end of the rows (about 2.7million rows). I though this would be a piece of cake, but I just can'tseem to find anything like it on the internet...weird.Anyways, I'm just a rookie in sql, any help would be appreciatedThanksJMT
View 6 Replies View RelatedT-SQL (SS2K8) :: Add Auto Incrementing Column To SELECT Statement
Apr 1, 2013Along 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.
SQL Server 2014 :: Incrementing Number In Table And Then Resetting Column
Jul 15, 2015I have four columns in my table, the first one is the identity column
col1 Col1 col2 col3
1 12 1 This is Test1
2 12 2 This is Test1
3 12 3 This is Test3
4 12 4 This is Test4
5 12 5 @@@@@
When, I see, @@@ sign in my col4, I need to restart the col3 from 1 again so it will look like this
col1 Col2 col3 col4
1 12 1 This is Test1
2 12 2 This is Test1
3 12 3 This is Test3
4 12 4 This is Test4
5 12 5 @@@@@
6 12 1 This is another test1
7 12 2 This is another Test2
Is it possible to do that?
Power Pivot :: How To Create Incrementing Sub Counter For (sort By Column) Purposes
Nov 16, 2015I have a table ComponentPeriod. In it we have the combination of a component (e.g. A,B,C ) and a period (2014 Q1, 2014 Q2, 2014 January etc)I want the periods to be in descending order (2015 Q4, 2015 Dec, 2015 Nov, 2015 Oct, 2015 Q3 ... etc) and so I need to create a sequential number series to allow this to happen (as we can only order in the client tools by a single column - and so I guess the technique I'm looking for is used a lot to produce these types of "order by" columns)
I have done this in the past using
Period_Sequence =
calculate
(countrows(Period)
,filter
(Period
, Period[Start_Date] <= earlier(Period[Start_Date])
&& Period[Duration_String] = earlier(Period[Duration_String])
)
)
Which was fine when I was referring to a table where Periods where distinct directly but now I have denormalised this for ComponentPeriod so I need something a little more sophisticated Whats the best way to get a sequence with perhaps some partitions in across a subset of distinct columns (I guess from SUMMARIZE or similar)
E.g. I want
Period_Name, Type, Sequence
2015 Q4, Quarter, 1
2015 Dec, Month, 2
2015 Nov, Month, 3
2015 Oct, Month, 4
2015 Q3, Quarter, 5
2015 Sep, Month, 6
etc
even though there may be multiple records in ComponentPeriod that have the period 2015 Q4, but I want them all to have the value Sequence value of 1? I've got as far as:
Period_Sequence Desc =
calculate
(countrows(summarize(ComponentPeriod, ComponentPeriod[Period_End_Date]))
,filter
( ComponentPeriod
, ComponentPeriod[Period_End_Date] >= earlier(ComponentPeriod[Period_End_Date])
)
)
But this doesn't distinguish between the different types. I need an equivalent of the t-sql -
row_number() over (order by Period_End_Date desc, case when 'P3M' then 1 when 'P1M' then 2 end asc
SQL Server 2012 :: Update A Column Using Value Of Another Column
Sep 9, 2015I have a student table like this studentid, schoolID, previousschoolid, gradelevel.
I would like to load this table every day from student system.
During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.
My question in my merge statement something like below
Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid
When matched then update
set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;
My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?
Update Column Value In Whole Database (based On Column Value)?
Aug 27, 2015How to Update Column Value in the whole data base (based on Column Value)?
View 2 Replies View RelatedIncrementing
Nov 21, 2007Hi,
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.
Any help and advice will be very grateful
Thanks in advance
Incrementing A Value
Apr 27, 2007I 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....
Cheers.
Incrementing Id Field
Mar 21, 2004Hi
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?
Thanks
Incrementing Primary Key
May 13, 2004hi am new in this forum and am designind a database in SQLserver and i want to make the primary key auto incremented
can anyone help
Incrementing Primary Key
May 13, 2004hi am new in this forum and am designind a database in SQLserver and i want to make the primary key auto incremented
can anyone help
Incrementing A Field In SQL
Jun 29, 2004Hello,
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 :)
Thanks in advance for your help
Incrementing An Id Field
Mar 21, 2004Hi
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?
Thanks
Incrementing Value Query
Sep 23, 2014I 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.
View 4 Replies View RelatedAuto Incrementing
Nov 14, 2007Hi
I'm using a insert into fuction kind of like below
INSERT INTO table1(
col1
col2
col3
col4
)
SELECT
col2
col3
col4
FROM ...............
I'd like to auto-increment col1 so it counts how many rows are being added
any help is very appreciated
Convert Column And Update Column
Jun 20, 2000Hello,
I'm using SQL Server 7. I have an invoice table. The invoice table has a datetime column called InvoiceDate. The InvoiceDate column contains the following date format:
5/3/00
I would like to use the InvoiceDate column to update the char (6) column called zInvoiceDate as a formatted date field like yymmdd.
The following syntax did not work:
SET zInvoiceDate = Convert([ARInvoiceID],GetDate()12)
Any suggestions please :)
Thanks,
Denise
Update Column Value Based On Value In Another Column?
Jan 29, 2008Hi.
I'm writing a web application with VS2005 andSQL Server 2005 express edition.
I have an SQL table:
Table name:
statistics
Columns:
stat_id
firm_name
stat_month
stat_year
view_count
follow_count
percentage
When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:
UPDATE [statistics]
SET percentage = follow_count / view_count * 100
WHERE (stat_id = 15)
This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)
Any help would be appreciated.
Update 1 Column With Data From Another Column
Mar 12, 2007Hello,
I have a problem i'v been searching all day but i can't find an answer anywhere maybe someone here can help.
What I want to do is give a column in a table the same value as another column from the same table. For example:
Table:Requests
A request has a relatedrequestId wich links another request to it. Now I want the date from the linked request in the date from the master request. Because all the master requests date's are empty and i want them to have the date from the linked request.
How To Set Up Auto Incrementing For A Field
Aug 10, 2006
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.
Question About Incrementing A Field
Jul 17, 2005Hi 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!
ID Field Not Incrementing Correctly
Sep 14, 2001I 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?
Also, this is a cold fusion application. Thanks!!
ID Field Not Incrementing Correctly (again)
Sep 17, 2001I 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?
View 1 Replies View RelatedSQL To Generate Incrementing Values?
Jul 31, 2007Hi. Is there such thing as a way to write SQL to generate a column of increasing integers?
Expression1
-----------
1
2
3
4
5
6
7
8
9
10
11
Unwanted Auto-incrementing
Aug 17, 2006Here 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
Auto-incrementing A Field
Nov 9, 2006I 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?
Thanks!
Lisa
Build ID W/Incrementing Digit
Jul 23, 2005Help, 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.
View 8 Replies View RelatedIncrementing Within A Group/Subset
Oct 6, 2006I 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
View 1 Replies View RelatedIncrementing Value And CONSTRAINT Issue
Nov 15, 2007
Very new to SQL sorry.
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.