|
Subject:
|
how to get autogenerate value for the ID Field
|
|
Posted By:
|
MunishBhatia
|
Post Date:
|
12/5/2006 12:49:49 AM
|
Hello To All
i want to know how to set the column of table so that it took autogenerated values by sql 2005. e.g i have three fields in table ID,Name,Password. i want Name and Password provided by user and ID must be autogenerated by SQL in table
Plz Tell
thanks......
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
12/5/2006 2:20:03 AM
|
IDENTITY column... look it up in Books OnLine.
--Jeff Moden
|
|
Reply By:
|
CH011KI
|
Reply Date:
|
12/6/2006 7:27:16 AM
|
Hi Munish
As Jeff says it is worth checking the Books Online But if you are scripting the Table you should end up with something near to this
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT TRANSACTION
BEGIN TRANSACTION
GO
CREATE TABLE dbo.TableNameOfYourChoosing
(
/*
[ID]is the Column Name,
Int is its Datatype of an Integer,
As this is our Identity Column we do not want to allow null values,
IDENTITY (1,1) Sets the Column as Identity, (1 represents the very first seed to start with
,1) is the amount to increment by
*/
[ID] int NOT NULL IDENTITY (1, 1),
--Column [Name], DataType Varchar(50) - specify max length
[Name] varchar(50) NULL,
--Password of DataType Varchar(50) again you can specify lengh by replaing a value where the 50 appears. Note: A Max of 255
[Password] varchar(50) NULL
) ON [PRIMARY]
GO
COMMIT
Hope this Helps
Thanks
Chris
|
|
Reply By:
|
MunishBhatia
|
Reply Date:
|
12/6/2006 7:51:15 AM
|
Hi Chris,
your code absolutelu works fine and applied it. Thanks a Lot!
thanks......
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
12/6/2006 10:21:58 AM
|
Yep... And if you are using a GUI, use Scope_Identity() to find the ID of a row you just inserted if you need to.
--Jeff Moden
|
|
Reply By:
|
MunishBhatia
|
Reply Date:
|
12/8/2006 2:14:39 AM
|
Hello
as helped to me to get autogenerate numbers works for me i get another way to do this in sql server object browser-right click on any table and click modify-it will open table columns on right side.
now click on column name to whom you want to apply autogenerate number, when you click the properties window will be displayed below-it have one property called "IsIdentity" click on it and select "Yes" the field have now auto generate number. you can also select initial value as well as seed.it is again very simple if u have already created table and want to apply identity column to field, no need to perform alter command.
thanks......
|
