-
Sep 27th, 2005, 02:30 PM
#1
Thread Starter
Addicted Member
AutoNumber with SQL
HI,
I am wondering how to get an autonumber field with SQL, just like in Access. Is this possible?
-
Sep 27th, 2005, 02:45 PM
#2
Re: AutoNumber with SQL
If you mean SQL Server (which is one of many DBMS's which support SQL), you need to set it to be an Identity field.
In the table design in Enterprise Manager you can set the Identity Seed (the number to start at), and the Indentity Increment (the number to add to find the 'next' number). Both of these are normally set to 1.
-
Sep 27th, 2005, 04:19 PM
#3
Thread Starter
Addicted Member
Re: AutoNumber with SQL
si_,
I have more questions....
1. What does 'Yes(Not for Replication)' mean?
2. I need to create this column in a temp table, what is the code to set the indenity seed and increment?
3. And, this is really dumb I know, but when I create a temp table, where do I do that? I can attach the code to a button, right? Or am I just completely missing the SQL query boat?
Thanks
-
Sep 28th, 2005, 06:39 AM
#4
Re: AutoNumber with SQL
callydata,
You are missing the boat. Use an SQL query to create a temp table.
-
Sep 28th, 2005, 05:23 PM
#5
Re: AutoNumber with SQL
In most cases I agree, but it depends on what callydata is doing with the table.
To answer the questions:
1) Unless you are replicating the database (to another DB server), this is fine - see the help if you want details of what it means.
2) The default is 1 and 1, I'm afraid I don't know how to set i differently as I've never needed to. The T-SQL guide in the help for Create Table should show the syntax
3) As long as you have a database connection set up in code (not a data control) you can run it from wherever you like in your program. If you don't know how to do this, there is an ADO Tutorial link in my signature that should help.
-
Sep 29th, 2005, 02:58 AM
#6
Fanatic Member
Re: AutoNumber with SQL
check out the thread on this page auto increment key field ,
some people helped me with a similar problem,
it works 60% of the time, all the time.
-
Sep 29th, 2005, 06:41 AM
#7
Re: AutoNumber with SQL
Is this MS SQL Server??
From BOL...
Examples
A. Reset the current identity value, if needed
This example resets the current identity value, if needed, of the jobs table.
USE pubs
GO
DBCC CHECKIDENT (jobs)
GO
B. Report the current identity value
This example reports the current identity value in the jobs table, and does not correct the identity value, if incorrect.
USE pubs
GO
DBCC CHECKIDENT (jobs, NORESEED)
GO
C. Force the current identity value to 30
This example forces the current identity value in the jobs table to a value of 30.
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
I thought I had read about how to set the increment - but apparently I cannot find that right now...
If you are creating the table you can set the start and increment at that moment though...
IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
seed
Is the value used for the very first row loaded into the table.
increment
Is the incremental value added to the identity value of the previous row loaded.
Example...
CREATE TABLE Sales
(SaleID INT IDENTITY(100000,1) NOT FOR REPLICATION,
CHECK NOT FOR REPLICATION (SaleID <= 199999),
SalesRegion CHAR(2),
CONSTRAINT ID_PK PRIMARY KEY (SaleID)
)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|