Incrementing Number In A Column -- Rookie Sql User
Jul 23, 2005
Sorry to bother you guys with what I though would be an easy task. I
have a table in my database were I would like one of the rows to
increment a number for each row. I want the first row to start at 1000
and keep on incrementing by 1 'till the end of the rows (about 2.7
million rows). I though this would be a piece of cake, but I just can't
seem to find anything like it on the internet...weird.
Anyways, I'm just a rookie in sql, any help would be appreciated
Thanks
JMT
I 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
I need a bit of generic advice about a project I am currently working on which is slightly out of my comfort zone. I€™m using SQL 2005 and VB.Net to develop an application that produces invoices for a number of different countries. The issue is that the data is arriving from an AS400 system so the tables I am working from are a little strange! This results in a fair degree of work tweaking the data into a format that is useful for my application.
To simplify the explanation each invoice in its basic form consists of the invoice data and the line items (so there is nothing new here). What is unusual is that the invoice data has an invoice number but then an additional invoice number specific to the country it is destined for. The initial invoice number arrives with the data from the AS400 but the secondary number is generated according to a number of business rules.
I have written an update query in a stored procedure which updates the invoice data with a number of values from a secondary table and a number of calculated values. There are several functions, one in particular which looks up the new invoice number from a secondary table based on specific criteria (country of destination being one of them) and I had intended to write an update query, called from within this function which then increments the invoice number but I have since found out that you cant do an update/delete from a function?
I can€™t add an update query to increment the value after the initial update query as this results in the stored procedure running through and adding all the invoice numbers (which are the same) and then the number being incremented at the end.
I assume I can€™t call an update query from within the initial update query so that the increment takes place after each loop of the initial update (if I can what would the syntax be?).
I have tried writing a trigger which completes the increment when the invoice data is updated. I haven€™t used triggers before but once again it appears as if the first update query is running through and completing all the updates and then the trigger fires incrementing the number. Or have I just got this wrong?
I guess what I need is a trigger which fires after each row of the table is updated which was how I assumed it should work!
Ironically I could write this in VB.Net with ease but for speed we need it within the database.
What is the best way to increment my invoice number?
I€™m sorry of this is general in nature but im really looking for some advice as to the best approach to deal with this issue.
I have a user variable , say max_id, which retrieves the max id from a primary key column of a table.
now in Data Flow Task, I have a Derived Column where I am creating a new column which will have the values max_id + 1 for the first record, then max_id + 2 for the second record of the derived column and so on..
and then I am mapping this derived column with that primary key column of the table, so that the new id's are different than the old ones which are already in the table, so there wont be any primary key violation.
I cannot make that primary key column as an Identity column.
my question is how do I increment this max_id user variable everytime ?
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.
There are 3 columns in the result set - part num, Qty and MO num. Each MO num has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty.
1.There are duplicates of part.num and I want to remove duplicates and add quantities of those duplicates into one single quantity. For example, xxxx is a part num, then xxxx=1,xxxx=3,xxxx=5. I want xxxx=9. I want to sum those. Another question is, each MO has a user. I want to join user and MO num in MO.
Heres the code,
part.num , (woitem.qtytarget/wo.qtytarget) AS woitemqty, (SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums FROM mo INNER JOIN moitem ON mo.id = moitem.moid LEFT JOIN wo ON moitem.id = wo.moitemid LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10 LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom from woitem JOIN part on woitem.partid = part.id and part.typeid = 21 JOIN uom on woitem.uomid = uom.id group by 2,3) as labor on wo.id = labor.woid LEFT JOIN part ON woitem.partid = part.id
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…
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!
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)?
I'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:
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.
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 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)
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)
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:
I have created a local user on Report Server Computer and the user has the administrative rights. When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ). I get the following error.
The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user. -------------------------------------------------------------------------------- SQL Server Reporting Services
Then i try to login using a different user with administrative rights on the machine, i can logon successfully. The system is up for a month but this problem occured today?!? What could be the problem?!?
Hello, I would like to create a SQL Server DB on my win95 machine...I have VB Studio 97 Enterprise Edition loaded on my machine.
When I go to Interdev and try to create a Project (via New DB wizard) the first question is "What Server do you want to create your DB on?"
I haven't found ANY documentation that details how to set up the Client and the Server on the SAME machine.
1) Can you set up SQL Server in a way that the same machine is the Client and the Server? 2) Do I need anymore software than VB Studio 97 Enterprise Edition? (To setup a SQL Server DB on my machine)
I basically need guidance on how to create a DB on my local machine, so I can reference it in my ASP application.
Hi, I'm trying to read the most recent entries in a table of data. Its fairly straightforward because there is a timestamp saved when I write into the table. For a table with the following columns Username, description, location, timestamp, result1, result2 I can select the max timestamp for a username and it will return the most recent date/time that an entry was written to the table for that particular user. No problems with that. However, what I really want is to select the most recent timestamp for each location for each username; I also need the result1 and result2 fields returned. Thus for the following table: user description location timestamp result1 result2 Jo Red Hall 01/01/06 Yes Yes Jo Blue Hall 01/02/06 Yes No Jo Blue Deck 01/04/06 No No
I would like a query to return: Jo Blue Hall 01/02/06 Yes No Jo Blue Deck 01/04/06 No No The problem is if I do Select max(timestamp) as Tmstp, description, user, location, result1, result2 from tblSomeTble I will get Jo Red Hall 01/01/06 Yes Yes Jo Blue Hall 01/02/06 Yes No Jo Blue Deck 01/04/06 No No because some of the results are different and thus the SQL will return the most recent entry for each unique combination of user, location, result1 and result2. I've solved the problem by doing two separate database reads; the first does a simple Select max(timestampdescription) as Tmstp, user, location then I use VB.net to fill a dataset and read these values from the dataset and plug them into a second SQL for a second database read like: Select max(timestamp) as Tmstp, description, user, location, result1, result2 from tblSomeTble where timestamp = [timestamp from first sqlread] and user = [user] and location = [location from first sqlread] it all works fine, but I'm aware its messy and probably not very scaleable to have multiple database reads that are unnecessary. It must be a commonly encountered problem though? How do other people handle this? is there a common solution people use? thanks
I have a database on my development machine and its working fine. And I want to duplicate the database schema on the production server. What is the easiest way to do this?James
I am getting an error backing up databases on a remote SQL Server. I have tried using two options:
1. All tasks, Backup Database... (Enterprise Manager) 2. Database Maintenance Plan Wizard
In both cases, I get the following error:
2000-11-09 18:21:34.93 kernel BackupDiskFile::CreateMedia: Backup device 'FMSSQLBACKUPwOLTP_db_200011091821.BAK' failed to create. Operating system error = 5(Access is denied.).
What are the exact NT and SQL Server permissions needed to perform backups. Also, I am able to write manually to the backup directory not through the SQL Server application.
NOTE: ALTHOUGH I WAS ABLE TO REGISTER THE REMOTE SERVER USING USER sa, I CANNOT STOP OR START ANY OF THE SERVICES (SQL SERVER AGENT...) OR CONFIGURE THEIR STARTUP ACCOUNTS (GREYED OUT)!!
I'm new to SSIS. I need to Extract, transform and load data from one SQL table to another. I want to do something like "select count(a) from table 1 where ...."
I have define the following Data flow items on the package designer, I believe that OLE DB source & OLE DB Destination, are correctly configured, however i don't see how can i transform data on the "OLE DB COMMAND" item....should i use "data conversion" instead ?
ps: is there any site where i can find examples of data transformations ?
I have much MSACCESS expeience and am new to SQL so here goes...I am trying to create calculated fields in a view, but can't seem tomore complex calculations to work.I can create a new field which does simple arithmetic on two otherfields and rename it so it becomes part of the new view to be queriedby other views, but when I use more complicated constructs I can'tseem to get them to work.For starters:How do you create a new field that has a number based on an IFconstruct based on another field.Example:field1 has state abbrevs and field 2 should have a "1" if field1="NY".In access I do field2:IIF(field1="NY",1,0),How do I accomplish this in MS SQL.Can it be done in the view?Thanks,Brad
I have a table in which a non-primary key column has a unique index on it. If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep
I am using SQL 6.5 and I would like to know (using Isql/w) the number of users connected to my server at any given time. Thanks for your help in advance.
I'm new in all this, I have sql server 2005 and have a problem I need let access to more users to the database, in the properties page of my DB I hava a Number of user field with 5, how can change this to up number?
I have a windows forms app and I'm trying to add functionality that would allow the user to enter a number into a textbox control on a setup form and then that number would autoincrement by one each time a new record on another form is inserted into the DB .
I've searched high and low and can't seem to find much information on how I can put this together. I'm sure I'll need a "numbers" table in the DB that stores the numbers and is joined with the parent table where the records are being inserted into. After that, I'm pretty much lost on how to generate the numbers in the "Numbers" table.
If anyone could assist or point me in the right direction of a resource, I would really appreciate it!!
I have a Sqlserver 6.5 production server used for web based applications Compaq 5500 4 GIG RAM, 4 processor
Sql Memory config on that server is 1048576 (which is 2048 MB) and the run value is 640000 (which is 1250 MB). runvalue for user connections was 500
We have only 90 user connections may increase upto 100 depends on the connections. We don't have any overload on that server.
Error: I was seeing "unable to connect maximum no. of 500 configured user connections are already connected" in the error log.
Action Taken:
I increased the user connections to 600 and rebooted the server. Immediately the same error I can see in the error log. But we have only 20 user connections at that time. I can able to connect.
Asp.net 2.0 (VB) application with Sql server 2000 Installed in windows 2000 professional server.Only less than 10 users using this application.
The problem is sometimes the application didn't response, it's very slow to get the information and after sometimes its never responding it showing hourclass.
I used Helper class (Application Block). Since Sql Server 2000 doesn't have the MARS Function, i have manually close the connection and opening again everytime.
Any idea?. Thanks in advance.
Help Class Modified Code: -------------------------------------- Code :
' If the provided connection is not open, we will open it
If connection.State <> ConnectionState.Open Then
connection.Open()
mustCloseConnection = True
Else
mustCloseConnection = False
End If
Data ObjectBase : ---------------------------
'The transaction will call this method and also check the connection status
Public Sub BeginTransaction()
Try
If Not (myTransaction Is Nothing) Then
'Throw New Exception(ConfigManager.ReadFromXml("msg_DataAccess_Begin_trans"))
Throw New Exception("Transaction Error in Begin")
End If
If ourConnection.State <> ConnectionState.Open Then
I am new to sql server query. I am trying to write a query for an application and I want user to enter number of over due days for payment.Below is my query and I am getting an error:Â An expression of non-boolean type specified in a context where a condition is expected, near 'Group'.
SELECT (P.FirstName+', '+P.LastName) As PA, Px.PRespDate,Px.DateFrom  WHERE Px.Hide = 0 AND Px.Total <> Px.Payments AND Px.PRespDate IS NOT NULL AND PE.Hide = 0 AND PE.BillReady = 1 AND DATEDIFF(DAY, Px.DateFrom, PRespDate)  LIKE 'Enter # of days |%% ' Group By (P.FirstName+', '+P.LastName), Px.PRespDate,Px.DateFrom
Is it possible to control the number of simultaneous connections one login id can have? I`d like to avoid my users to share their login ids and passwords to help them enter data into the system.
I am designing wepage using Asp.net with Sql server as database. I have used Multiline textbox in which user has to enter their family detail. Like number of brother and sister family status, source of Earning for family.
But User do not have to enter his/her phone/contact number. For that I restricted the user to enter any nuberic digit through asp.net Regex. But User is too smart now they are entering his/her contact number in words.
Suppose somebody's Contact number is 533445928 as they can not enter numberic digit because of asp.net regex which allows the user to enter only alphabets. Now they are entering their contact number in words like five three three four four nine two eight seven. I would like to restrict the user to enter contact detail in words also.
