Help for Newbie: How to create an autonumber

Hi guys/gals. An intro, I've just pick up some database basic skills and currently doing a simple project.

I really need advise on how do I create a field that can generate autonumbering for eg. scenario

There are 3 types of service_id like CTXXXX, GPXXXX, and STXXXX (where XXXX are running numbers). What I need is to have the XXXX auto generate and running. Also if I were to delete an old record for example CT0033, the latest number eg. CT1111 will not be changed to cover up for the missing CT0033.

I apologize if the description is a bit improper, but I'm new to this. Really appreciate anyone that can help. Thanks!! :oI'd suggest the IDENTITY property when you create the table. You'll have to "decorate" the resulting number with the letters you need, but that's trivial.

-PatP|||my money's on access

and what do you mean by basic skills?

can you spell dba?

Is this homework?|||Sarcasm, Brett? How unlike you!

NOT!

:)

If all three of these services are stored in the same table, then you will run into difficulties with IDENTITY unless you don't mind each service being numbered consecutively. IDENTITY will autonumber for the entire table, but not for individual services within the table.

Your best bet is to store the last ID number used for each service in a separate table, and then use a trigger on your data table that looks up the last code used, increments it by 1, and creates the new services codes.

But this is not simple SQL.

An alternative would be to denormalize your data and store the services in separate tables each with their own IDENTITY property, but this is frowned upon for many good reasons.

Actually, your best course of action would be to drop the idea of creating these codes in the first place, since such pseudo-surrogate keys violate several principles of database application design. That is why they are difficult to code.

Help for INTRO function

Hello.
I need to create a new table from a query result, then I've written this SQL
command:
"SELECT Name, Number FROM Friends INTRO Result"
BUT
SQL give me an error!!
Please Help Me
Bye
CrisI assume you mean INTO not INTRO
SELECT Name, Number INTO result FROM Friends
http://sqlservercode.blogspot.com/|||Cristian wrote:
> Hello.
> I need to create a new table from a query result, then I've written this S
QL
> command:
> "SELECT Name, Number FROM Friends INTRO Result"
> BUT
> SQL give me an error!!
> Please Help Me
> Bye
> Cris
It's INTO not INTRO.
However, the resulting table won't have any keys or constraints.
Although SELECT INTO is handy as a quick and dirty way to create
tables, it may be better to do something like:
CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
NULL, PRIMARY KEY ...)
INSERT INTO Result (name, number)
SELECT name, number
FROM Friends ;
David Portas
SQL Server MVP
--|||Also,
If you need an autonumber in the destination table and need the data ordered
you must create the table first.
Good code:-
Create table myTable ( id int identity(1,1) not null, name varchar(50) not
null )
INSERT INTO myTable ( name ) SELECT name FROM friends ORDER BY name
if you use "select identity(int,1,1) AS id , name into MyTable from ..."
the order is not guaranteed and so it is advised against.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134382059.521678.242420@.g44g2000cwa.googlegroups.com...
> Cristian wrote:
SQL
> It's INTO not INTRO.
> However, the resulting table won't have any keys or constraints.
> Although SELECT INTO is handy as a quick and dirty way to create
> tables, it may be better to do something like:
> CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
> NULL, PRIMARY KEY ...)
> INSERT INTO Result (name, number)
> SELECT name, number
> FROM Friends ;
> --
> David Portas
> SQL Server MVP
> --
>