autogenerate numbers from 000001 to 999999

[m_evergreen]

I would like the numbers 000001 to 999999 to autogenerate in a new
database. I will be transfering information from another database and
in that database the numbers 000001 to 010000 are already taken. They
are used as identifiers in other programs and it would be easier if
they were stored as written. Using identity the 0's are eliminated.
Is there a way to keep them?

Thank you,
M

[David Portas]

If you're happy to generate IDs one at a time, try this:

CREATE TABLE Sometable (col1 CHAR(6) PRIMARY KEY CHECK (col1 LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9]'), col2 VARCHAR(10) NOT NULL)

INSERT INTO Sometable (col1, col2)
SELECT RIGHT('000000'+CAST(
COALESCE(CAST(MAX(col1) AS INTEGER),0)+1 AS VARCHAR(6)),6),
'Blah Blah'
FROM Sometable

--
David Portas
------------
Please reply only to the newsgroup
--

[m_evergreen]

Thank you very much. It occurred to me after reading your post that I
didn't need to do any math with the numbers, so I defined them as
varchar(6) and wrote a little program in vb.net with some loops to
fill them in for me. It worked out very well.
-M

"David Portas" <RE****************************@acm.org> wrote in message news:<Qf********************@giganews.com>...

If you're happy to generate IDs one at a time, try this:

CREATE TABLE Sometable (col1 CHAR(6) PRIMARY KEY CHECK (col1 LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9]'), col2 VARCHAR(10) NOT NULL)

INSERT INTO Sometable (col1, col2)
SELECT RIGHT('000000'+CAST(
COALESCE(CAST(MAX(col1) AS INTEGER),0)+1 AS VARCHAR(6)),6),
'Blah Blah'
FROM Sometable