SQL Server 2000 filling a key field

[Daniel Kubicek]

I have a table that has a int field that contains unique nubers.

if I need to insert 4000 or so new records into this table using a SQL
command how would I be able to fill this field with the next available
number?

This will be a manual procedure done maybe once a year so the next
number will be known ahaed of time. but the insert command need to
increment this number by one everytime it puts a new row in.

[Carl H]

the correct answer depends on many things not supplied in the original
question. Is the number sequential (1,2,3...)? is the number
alpha-numeric? Can the column be changed to an identity field if needed?
check out BOL (identity function) for some good info.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

[posted and mailed, please reply in news]

Daniel Kubicek (dk******@upgradesetc.com) writes:

I have a table that has a int field that contains unique nubers.

if I need to insert 4000 or so new records into this table using a SQL
command how would I be able to fill this field with the next available
number?

This will be a manual procedure done maybe once a year so the next
number will be known ahaed of time. but the insert command need to
increment this number by one everytime it puts a new row in.

Does this int field have the IDENTITY property? In such case it's simple.
If it is not, you can do this:

SELECT id = IDENTITY(int, 1, 1), col1, col2, ...
INTO #temp
FROM ...

DECLARE @tblid int
SELECT @tblid = coalesce(MAX(id), 0) FROM tbl

INSERT tbl (id, col1, col2, ...
SELECT @tblid + id, col1, col2, ...
FROM #temp

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp