Generating Auto Incrementing No Using SQL Query

Sep 22, 2004

Hi everyone,

I am having a table with which i generate a report. Now how to using a SQL query i can generate a auto incrementing no.

Say i am executing this query

SELECT NAME,AGE,ADDRESS FROM MEMBER which gives

NAME AGE ADDRESS
HOLYMAC 13 MALACCA
HOLYCOW 25 USA
HOLYGOD 55 LONDON


Now how can i make it come out like this

SNO NAME AGE ADDRESS
1HOLYMAC 13 MALACCA
2HOLYCOW 25 USA
3HOLYGOD 55 LONDON

See the first column is a auto incrementing number 1,2,3.


How can I write a SQL QUERY that outputs a auto incrementing number.


Thank you
Have nice day

View 2 Replies


ADVERTISEMENT

Incrementing And Concatentating Non-Auto Incrementing Fields

Mar 11, 2008

My 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

View 2 Replies View Related

Auto Incrementing

Nov 14, 2007

Hi

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

View 5 Replies View Related

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.
 

View 2 Replies View Related

Unwanted Auto-incrementing

Aug 17, 2006

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

View 9 Replies View Related

Auto-incrementing A Field

Nov 9, 2006

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?

Thanks!

Lisa

View 5 Replies View Related

Identity Value Not Auto Incrementing

Feb 19, 2008

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.

cheers

View 1 Replies View Related

Identity / Auto Incrementing Column

Feb 16, 2006

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)?

View 1 Replies View Related

Re:- Auto-incrementing An Alphanumeric Field

Jul 13, 2001

Hi

Everyone

I have a problem my table structure is like this

book_id varchar2(30)
book_name varchar2(30)


Now i want to enter the data into the fields like this

B001 Java Unleashed
B002 ASp Unleashed


and so on

So my Problemis how i will auto increment the book_id field though that is an alphanumeric field.

PLease help me that how i will insert and select from this table


Waiting for your help


Manish

View 1 Replies View Related

Auto-incrementing With Leading Zeros

Aug 10, 2006

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

View 5 Replies View Related

Transact SQL :: Primary Key Auto Incrementing By 1000 Instead Of 1

Jul 13, 2012

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?

View 18 Replies View Related

Replication In Database Having Auto Incrementing Field

Aug 31, 2007



Hi,

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...?????

View 6 Replies View Related

Auto Incrementing-&>Updating Values In Two Related Tables :Help!!!

Nov 11, 2005

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.  
                                                                                 
thank you all........
                                                                                                           
     ahmed_ind

View 4 Replies View Related

T-SQL (SS2K8) :: Add Auto Incrementing Column To SELECT Statement

Apr 1, 2013

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.

View 7 Replies View Related

Set Custom Identity Keys For Master Tables(auto-incrementing):

Jun 17, 2007

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.

View 2 Replies View Related

SQL Server 2012 :: Auto Incrementing Alphanumeric Field In Table

Oct 1, 2014

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

View 9 Replies View Related

Auto Generating An Email

Jun 5, 2006

Hi All,
I need to opening up a browser page everyday at 6am. (What I am trying to do is to send an email with some formatted output everyday at 6am)
Is there anyway to achive this via SQL 05 Express?
Thanks,
Mal.

View 4 Replies View Related

Auto Generating Numeric Column

Sep 5, 2006

Hi There,I want to create a column that usto numbers upto a specified value, and the resets. I've tried using the identity column and then using DBCC CHECKIDENT, but this doesnt doesn't have the desired affect. Here's an example of what I'm after.AutoRow1234 -------- reset1234 --------resetAny help would be great, many thanksStuart   

View 5 Replies View Related

Auto Generating Stored Procedures ?

Jan 17, 2007

Hi!
Let's say users of my app can build some reports based on a current database schema and my custom-database adapter will generate some SQL to get the right data. User can save his report template ( basically data needed to create SQL ) in some storage. Will it be better solution to generate stored procedure for such report and save stored proc to a database and their name with raport data so next time user needs this report it will use stored proc.
Jarod

View 1 Replies View Related

Help In Auto Generating Item Code

Aug 28, 2007

Hi guys,

I am new to programming and sqlserver 2000. i use sql2000 as my database server...

I have a problem on auto-generating medicine codes. and i hope someone would help me with my script.

as you noticed i declared two variables (@id and @code). I am having a problem in generating @code... i would just like to create an autogenerated medicines_code from another field from another table (hosp_counter.counter_id)

here is how my medicines_code should look like ('MEDS34') "where 34 is from a counter_id in hosp_counter table...

but it only creates a medicines_code = 'M' (below is the result)

34(this is from counter_id)M(this is the autogenerated code)AUTOGENERATE11111010110012110010000000018/29/2007 11:29:46 AM1/1/19001/1/1900

here is my script in autogenerating medicine code...

set xact_abort on
begin transaction
declare @Code as varchar
select @Code =
(select 'MEDS' + CAST
(Counter_ID + 1 as varchar (50))
from Hosp_Counter where table_description = 'Medicines')
declare @ID as integer
select @ID = Counter_ID + 1
from Hosp_Counter
where table_description = 'Medicines'
select @ID
INSERT INTO Hosp_Medicines
(medicines_id,
medicines_code,
medicines_description,
medicines_specification,
medicines_genericname,
medicines_drugclass,
PhicCategory,
medpreparation,
medicines_prohibited,
medicines_trade,
medicines_NoneTrade,
medicines_taxable,
medicines_PhicCovered,
medicines_AmountCovered,
medicines_Discountable,
medicines_SmallUnit,
medicines_BigUnit,
medicines_AverageCostSmall,
medicines_AverageCostBig,
medicines_WeightedAverageCost,
medicines_weight,
medicines_conversion,
medicines_LowStockLevel,
medicines_OverStockLevel,
medicines_LowSalesReference,
AllowStat,
status,
CreateBy,
UpdateBy,
DeleteBy,
CreateDate,
UpdateDate,
DeleteDate)
VALUES
(@ID,
@code,
'AUTOGENERATE2',
'',
1,
1,
1,
1,
0,
1,
0,
1,
1,
0,
0,
1,
2,
1,
100,
100,
0,
0,
0,
0,
0,
0,
1,
'',
'',
'',
'8/29/2007 11:31:25 AM',
'',
'')
select @ID
Update Hosp_Counter set Counter_ID = @ID
where Table_Description = 'Medicines'
commit transaction

any help is very much appreciated!

'confused'
Joel

View 3 Replies View Related

Generating Auto Sequential Numbers

Jul 23, 2005

I am using sybase aSE12.5. I have a table with only one column withdatatype char(2). when i query this table to select all the records, ishould get these records in the ascending order and they should be numbered, i.e, the o/p should look something like thiscolumn_name------ --------1 AB2 AC3 ADand so on.I cannot add an extra column and i need this to be done in a single query.--Message posted via http://www.sqlmonster.com

View 2 Replies View Related

Auto-generating A DateTime In SQL Server Express 2005

Jul 22, 2007

Is it possible to have a DateTime field in a row automatically generated when a row is created?
ie:

INSERT INTO [TableName] ([DateTimeStamp], [UserId], [Title], [Explanation]) VALUES (DateTime.Now(), @UserId, @Title, @Explanation)
or something like that?

View 2 Replies View Related

Incrementing Value Query

Sep 23, 2014

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.

View 4 Replies View Related

Query For Generating A Tag Cloud

May 12, 2008

I've read some articles on generating tag clouds but so far only one actually showed a query. And the query was on a simple database with a few tags. In our database we have records that can have literally hundreds of unique tags. So, I'm wondering how to get a realistic result set of the top tags? I can't just do a group by and select the top 30 or 50 because that won't necessarily get me through the alphabet. I'd like to at least have as many letters from the alphabet in the tags as possible like (ant, cat, dog, horse, lion, seal, zebra) but if I do the top 30 it may cut off around the letter "c". Know what I mean? So, how can I generate an accurate tag cloud query that uses all available letters from the alphabet as starting letters for the tags? Thanks.

View 5 Replies View Related

Generating Record ID In Your Select Query

Apr 11, 2008

If I wanted to run a query on any table and in the recordset that is returned have an 'id' field (or whatever) with the record id of that record, how would I do this?

I'm thinking something like

Select field1, field2, recordNumber // derived somehow - not an actual field
from table

where the result woule be:


field1 field2 1
field1 field2 2
field1 field2 3
field1 field2 4
field1 field2 5
...

View 6 Replies View Related

SQL Search :: Generating Dynamic Query

Jun 24, 2015

I have a table named "Persons"  which has the following columns [ Name ,Age, City]. I have a Windows app which has  3 textboxes for each column. My requirement is when I enter only name and enter the search button, I need result similar to this query.
     
select * from persons where name like '@tbname%'
when I enter only age and enter the search button, I need result similar to this query.
select * from persons where age =@tbage
when I enter name and city, I need result similar to this query.
select * from persons where name like '@tbname%' and city like '@tbcity%'

If I don't enter any fields and enter the serach button,  I need result similar to this query.

select * from persons for these 3 fields I might give any combination as above. But how can  I have a single query with all these combinations. I want a single query capable of doing this. I cannot have query like this, select * from persons where name like 'tbname%' and city like 'tbcity%' and age =@age

Because if I give only name,  city is considered as null nad age is considered as 0 and I get empty result. I cannot have Stored proc and I cannot do much in C# like using "selectqueryBuilder" etc. I need pure SQL query. 

View 4 Replies View Related

Generating A Datatable From Results Of A Dynamic Query?

Aug 25, 2004

I am developing a datagrid which will display a summary "report" of sales revenue grouped by a particular field. There will also be a drill-down on this field that will display in more detail the table of data that was used for the summary.

Because the user is selecting multiple criteria under which to run the search, I have created a dynamic query that gathers all the necessary data I need for both the drill down and the summary based on the variable parameters of the user. I've optimized the query that gathers the detailed data (taken from 7 tables) to execute far under a second.

So, that said, here's my question: It seems the most efficient way to get the summary data is to run it against the datatable (which gathers the detailed data) created by the dynamic query and just pass them both into a dataset. Can this be done? Originally, I was calling 2 separate sprocs in my application - one for the detailed data and another for the summary data using a "group by" on the table that was produced by the dynamic query (using "INTO TABLE" in the SELECT statement of the dynamic query). With this method, I received an access exception by asp.net. for the table created by the dynamic query so have dropped the "INTO TABLE", not to mention it increased the performance considerably NOT using the table.

Any suggestion on the most efficient/optimal method to use the data generated from the dynamic query sproc for the summary data? I am trying desparately to avoid running the dynamic query again just for the summary data.

I am a total newbie, so would appreciate any feedback. Also, would someone please tell me the datetime stamp function to print so that I can see how many milliseconds it takes to execute stored procedures?

TIA!

Sandy

View 1 Replies View Related

Transact SQL :: Generating XML (serialized) From Query Results

Aug 20, 2015

I didn't work with XML before and so I'm posting this question on how we can generate serialized XML. I have the following table -

DECLARE @Population TABLE (CountryId INT IDENTITY(1,1), CountryName VARCHAR(15), StateName VARCHAR(20), PopulationCount INT)
INSERT INTO @Population (CountryName, StateName, PopulationCount)
VALUES ('USA','California',300000),
('USA','Chicago',500000),
('Australia','Queensland',550000),

[Code] ....

Please note that I can have another country with 10 states or 30 states, so State Name is dynamic. Can this be done in SQL ? or we have to use .NET ?

SQLServer2014
-12.0.2000.8(X64)

View 6 Replies View Related

Nested Select Query Generating Syntax Error

Jan 23, 2008

I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:

Oracle Version:



Code Snippetselect avg(days) as days from (
select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data
where
dm_type = 'prime' and
dm_closedate <= '31-dec-2007' and
dm_closedate >= '1-dec-2007' and
program = 'aads'
group by dm_number)





SQL Version:



select round(abs(avg(days)), 0) as days from
(select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days
from dm_data dm, ProgramXref px
where
px.Program_Name = 'aads'
and dm.Program_Id = px.Program_Id
and dm.dm_type = 'prime'
and dm.dm_closedate <= '31-dec-2007'
and dm.dm_closedate >= '1-dec-2007'
group by dm.dm_number)





In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?

Thanks,
Lee

View 4 Replies View Related

SQL JOIN Query (using: FOR XML AUTO)

Nov 15, 2007

I've never been able to grasp the usage of JOIN when querying the database, and would like some help building the correct SQL query for my task.My database table looks like this: 


tableid    
parentid    
item    

1
NULL
Fruits

2
NULL
Colours

3
NULL
Sizes

4
1
Apples

5
1
Grapes

6
1
Bananas

7
1
Peachs

8
2
Red

9
2
Blue

10
2
Green

11
3
X-Small

12
3
Small

13
3
Medium

14
3
Large

15
3
X-Large

16
4
Granny Smith

17
4
Golden Delicious

18
10
Lime Green

19
10
Dark Green

20
10
Light GreenAs you can see this table uses internal foreign key linking (not sure what the correct term is). I am trying to write a query that will JOIN the correct columns and their values (...and then have it set using "for xml auto".  My expected generated xml should look like this (note that I am not outputting the XML because it will be used as a datasource, but this is for clarity):<Categories>    <Category ID="1" Name="Fruits" checked="false">        <SubCategory Id="4" Name="Apples" checked="false">            <SubSubCategory Id="16" Name="Granny Smith" checked="false" />            <SubSubCategory Id="17" Name="Golden Delicious" checked="false" />        </SubCategory>        <SubCategory Id="5" Name="Grapes" checked="false" />        <SubCategory Id="6" Name="Bananas" checked="false" />        <SubCategory Id="7" Name="Peachs" checked="false" />    </Category>    <Category ID="2" Name="Colours" checked="false">        <SubCategory Id="8" Name="Red" checked="false" />
        <SubCategory Id="9" Name="Blue" checked="false" />
        <SubCategory Id="10" Name="Green" checked="false" />            <SubSubCategory Id="18" Name="Lime Green" checked="false">            <SubSubCategory Id="19" Name="Dark Green" checked="false">            <SubSubCategory Id="20" Name="Light Green" checked="false">        </SubCategory>    </Category>    <Category ID="3" Name="Sizes" checked="false">        <SubCategory Id="11" Name="X-Small" checked="false" />
        <SubCategory Id="12" Name="Small" checked="false" />        <SubCategory Id="13 Name="Medium" checked="false" />
        <SubCategory Id="14" Name="Large" checked="false" />        <SubCategory Id="15" Name="X-Large" checked="false" />
    </Category></Categories>Can anyone help me with this query? 

View 18 Replies View Related

Problems With FOR XML AUTO Query

May 20, 2002

Hello-

I'm hoping someone can help me. I am trying to generate query results in xml. I am using FOR XML AUTO and FOR XML AUTO, elements.

The problem is that the results are returned in xml but are trunacted - the entire results set is not returned - the results cut off midway through one row...

I am using SQL 2000 - I have installed sp2 - this made no difference in the behavior

I'd be grateful for any info anyone has on this. Thanks!

View 1 Replies View Related

SQL 2012 :: SSMS Auto-recovery / Auto-save New (unsaved) Queries

Feb 16, 2014

Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:

%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1

However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.

I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:

%userprofile%AppDataLocalTemp

View 2 Replies View Related

Query Issue (Auto Numbered Column)

Mar 15, 2008

Hello Guys!I have an important for me and maybe easy question for you...

I want to take the results of a select but i want the first column to be an auto incresement Number...

For example
aa Name Number
1 Alexander Papadopoulos 212222222
2 .... ...
3
4
5

How Can this happen?


I sell my mother in law.Is anybody interested?

View 8 Replies View Related







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