Generating Numbers In SQL

Jan 19, 2004

hi,

I am developing a ASP.NET application using SQL Server as my database.

I want to create a number that is unique.

The number will have 8 digits.

first 2 digits correspond to year. ex:04 or 03

the next 6 digits start with 000001 and it should get added for each new entry of data.

i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.

Any help would be highly appreciated.

thanks,

-sriram

View 7 Replies

Generating Numbers

May 12, 2007

A simple question! I would like to create a database that creates say serial numbers and saves them to a table. If poss I would like to create them on demend and not duplicate.....

I have blank page syndrome!!!!

Thanks in advance

View 2 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

Generating A Series Of Numbers Or Dates?

Jul 20, 2005

I want to generate a resultset that is just a series of numbers inascending order or perhaps a series of dates.. What I mean is, isthere a way to generate a temporary table of dates given an input of astart date and an end date.. This table would then contain an entryfor each date in ascending order from the start date to and includingthe end date..Or perhaps with numbers, given a start of 5 and and end of 7the resulting table would be567Would appreciate any help with this one.. ThanksChris

View 2 Replies View Related

Help In Understing Logic For Generating Row Numbers?

May 18, 2006

Hello,

I found this query below that creates a Row Number column. Can someone help me to explain what the logic behind it is?

Thanks for help!

--------------------------------------------------------------------------------------------------------------------------------------------------

SELECT orderid, custid, orderdate,

(SELECT COUNT(*)

FROM dbo.Orders AS O2

WHERE O2.orderid <= O1.orderid) AS rownum

FROM dbo.Orders AS O1

ORDER BY orderid;

----------------------------------------------------------------------------------------------------------------------------------------------

View 4 Replies View Related

Generating Sequence Numbers In Target Table

Dec 4, 2007

Hi,
What transformations can be used to generate sequence numbers in a data flow?

View 2 Replies View Related

Generating Sequenced Line Numbers For Each Invoice Number

May 8, 2008

Can you help me with SQL issue I��m stuck on?
I wish to take source data that looks like this:

Invoic_num

Line_num

6658

0

6658

2

6658

8

7721

2

7721

3

And rebuild the line numbers like this:

Invoic_num

Line_num

6658

1

6658

2

6658

3

7721

1

7721

2

This seems completely impossible to me. So I was thinking that maybe a second procedure using update could be run against the table after the initial build.

View 10 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?

View 1 Replies View Related

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008

I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 Replies View Related

Generate List Of All Numbers (numbers Not In Use)

Feb 21, 2007

I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:

select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)

but don't know how. Any clues?

View 1 Replies View Related

Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error

Mar 27, 2007

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:@; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.

So close.. Thanks for any help or information.

View 1 Replies View Related

Generating SQL Script

May 31, 2001

Hi all,

I have generated an SQL script such that when it is run, it will automatically create tables if not existing or drop the existing tables. The problem I have is that, is there any way I can also make the script create not only the tables but also transfer the data from the old database to the new database?

I would really appreciate if any of you could point me to any existing programs, scripts etc that can do that. Thanks in advance

Regards,
Celia

View 1 Replies View Related

Generating SQL Scripts

Oct 8, 1999

Is there a way to generate SQL Scripts from a stored procedure?

For disaster recovery and documenation reasons, we'd like to create scripts of our databases periodically. I, being somewhat lazy, would like to automate this so I really don't have to remember to run this every so often.

Any help would be appreciated.

View 1 Replies View Related

Generating SQL Scripts

Jun 7, 1999

Is there a way to generate SQL scripts from other than SQL Enterprise Manager?

View 3 Replies View Related

Generating Nested XML

Oct 29, 2014

I must produce an XML file with this layout:

Code:
<root>
<dataroot>
<Professions>
<Profession>web designer</Profession>
<tags>
<tag>Ruby on Rails</tag>
<tag>Apache</tag>
<tag>HTML/CSS</tag>

[code]...

I can't get my head around it ..I've even tried to solve it by using FOR XML RAW.

View 3 Replies View Related

Generating A Leading Zero

May 13, 2008

Hi

I am unable to see how to generate a leading zero.

Table A

declare @TableA table ( ID numeric ,
Fruits varchar(10)
)
insert @TableA
select 1,'Oranges'
union all select 2,'Mangoes'
union all select 3,'Apricots'

ID Table A
1 Apples
2 Oranges
3 Grapes
4 Apricots

declare @TableB table ( seed numeric ,
)
insert @TableB
select 080513000448
union all select 080513000449
union all select 080513000450

Table B
seed
080513000448
080513000449
080513000450

I wrote the following query but i need generate a leading zero not sure which function can help maybe the right function but i am not sure how to use it in this case

SELECTconvert(varchar(10), getdate(), 12) +
(SELECT
CASE
WHEN SUBSTRING(ISNULL(max(seed),'00000'),1,6) = convert(varchar(10), getdate(), 12)
THEN SUBSTRING(ISNULL(max(seed),'00000'),7,12)
ELSE '000000'
END AS SEED
FROM B)
+ (row_number()
over (order by Id))
as SEED
FROM A

SEED
----
80513000451

The output which i need is

SEED
----
080513000451

rather then

SEED
----
80513000451

regards
Hrishy

View 20 Replies View Related

Generating A Cube ..

Feb 19, 2008

Can anyone suggest a case-study/reference links/video demos for creating a cube using Analysis Services and generation of reports using Reporting Services.

Thank You

View 2 Replies View Related

Generating Sequence

Mar 15, 2008

Guys,

I have to generate sequence for distinct group of values for example

intially seq is set to 1 through out the table

categorydescidseq
__________________________________
AccountingAccounting61
AccountingAccounting72
AccountingFinal81
AccountingFinal92
AddendumAddendum 101

Is there any way to accomplish this?

Any suggestions and inputs would help

Thanks

View 4 Replies View Related

Generating The Csv Files

Mar 31, 2008

hi

how can we generate the data in csv files of database tables by using sql query?

regards

View 3 Replies View Related

Primary Key Generating

Aug 24, 2006

Help again please,I need to insert rows into a table from another table. The tables areidentical column wise except the table im inserting from does not havea primary key value. On insert I need to generate a primary key thatis consecutive based on the table im inserting into. Also the table iminserting into does not have a identity column. Much appreciated.

View 7 Replies View Related

Generating Packages Using C#

May 12, 2006

Hi,

we would like to generate SSIS packages(connections, data flow tasks, etc..) programatically using C#. Is it possible to do so?

At present we are creating packages by dragging and dropping required componets from toolbox and configuring them manually. We have a requirement of creating around 300 packages every week. Manual creation of packages is really a nightmare.

I heard from one of friends that using c# we can create packages. is it true? if so, can we view/edit the packages generated using c# using VS 2005 IDE(if required) ?

Regards,

Gopi

View 4 Replies View Related

Generating Reports On Fly

Mar 29, 2007

Hello,

SRS 2005 provides functionality for loading and rendering reports on fly using LoadReportDefinition and Render methods defined in ReportExecutionService webservice.

I was wondering if the same/similar behaviour can be accomplished in Reporting Services 2000.

Thanks in advance,

Kobi

View 1 Replies View Related

Generating Script

Jul 12, 2007

What does N indicate in the following script

SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].xxxx' )

Plz can anyone explain

EXEC dbo.sp_executesql @statement = N'Create View xxxxx'

View 4 Replies View Related

Generating Sql Logins

Oct 17, 2006

I've been given an excel file that lists:

userID's,
Names
Password
expiary dates.

I need to convert this list into sql server logins. Can this be done via a tsql statement? if so how, and if it cant be done via a statement then what other way can it be done.

Many thanks in advance.

View 2 Replies View Related

Generating A Primary Key

Sep 18, 2007

I'm having problems generating the primary key for a sql server table. I use a slowly changing dimension to discriminate modified and new records. The primary key in the SQL Server table is a combo number/letter incremental (ex. 0000A, 0001A...9999A, 0000B...). I tried creating Instead of insert and For insert trigger for a table but this doesn't seem to do the work.

What are my other options? How can I generate a primary key for every new row?

Any advice is appreciated.
Regards
Sara

View 6 Replies View Related

Generating CREATE Scripts

May 20, 2008

Hello all,
I have a few questions relating to scripts and I'm looking for some help, or at least a point in the right direction.
1) I'm generating CREATE scripts to move a number of tables/procedures/functions from my testing database to my production database. The issue I have is the script doesn't seem to be generated in a particular order, so I'm getting errors when a table/procedure/function is created that references something that hasn't been created yet. Is there a setting available to create the script in a specific order to prevent these errors?
2) This is somewhat related to the first question. I need to drop all the tables/procedures/functions before I re-create them. Can I set all the DROPs at the beginning of the script, and then run all the CREATEs?
3) Eventually the database will be ready for production and in use. And eventually I'll be asked to make changes, without erasing existing data. Can I accomplish this using the generate scripts feature? What topics should I be investigating when looking into conducting these types of updates? "Updating database" is too general and I'm not sure the technical term. I've done very simple DB-related tasks so I'm trying to figure out what I'm looking for so I can educate myself. Basically, I don't know what I don't know.
Any help is appreciated. Thanks.

View 1 Replies View Related

Generating Tables Programatically

Jun 7, 2008

hi i am trying to gernerate some tables ( first with a script and then with a stored procedure),, in ms server management studio exrress 2005 using the followingcreate table ip( id int IDENTITY NOT NULL, ip varchar(15) NOT NULL, hostname varchar(128) NOT NULL, primary key(id)) ; i store this in a new query (assume :right click associates the query to the current database ?) and run it and nothing happens its been a while since i did sql but i think the sql is ok....how or more specifically where is the appropriate place to run this code thankssimo stored as a new query

View 1 Replies View Related

Generating Text File

Dec 4, 2003

I have a table(say tblUserInfo) on SQL Server. What I like to do is a text file will be generated on the hard drive which SQL Server sits on when a new record is inserted into tblUserInfo. The content of the text file comes from the table. Is there any way we can go for doing that?

View 2 Replies View Related

Generating Membership Number

Jan 20, 2005

Hi all,
I have a question about generating membership numbers on the fly when someone registers to my website.

Rather than using the auto increment field as a membership number, I would rather keep it as just as the ID for the record and I would like to have a seperate membership number that looks something similar to this...

SR357324J

This will then stay with them for the lifetime of their membership and be on their printed loyalty card.

My questions are...
1) Is there a 'good practice' for membership number format and generation?

2) If this was used as a unique field, is there a degradation in performance when looking up records due to it being alphanumeric.

I may be well off base here, however these are my thoughts so far and your opinion/help is greatly appreciated.

Thanks for your contribution.

View 2 Replies View Related

Generating A Database Design From Xml

Apr 21, 2006

Hi, I'm looking for a way to take an XML feed and basically do what this article describes: http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/

(In short, take in XML, generate a database generation script, creating that database, then load the XML data into said database.)

But, the above article involves user interaction and I would like this all automated, ie: in .NET 2.0/C#

Does anyone know how to do this?

Cheers,

Matt.

View 1 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

GENERATING A LIST OF DATES

Jan 25, 2002

Can someone show me how to load a list of dates into a table.
(i.e. from Jan. 1, 1995 to Jan, 1, 2005)

Thanks in advance!
BV

View 1 Replies View Related