I am using SQL Server 2008 and a primary key of a database table I am using is not an IDENTITY
column (not sure why). I need to change that.
I am in SQL Server Management Studio in design view, under column properties and for some reason I can't change the identity specifications to Yes
.
Is there something that I am missing.. I am new to SQL Server - any ideas on what I am missing??
Here is the create table
CREATE TABLE [dbo].[AR_Transactions](
[Trans_ID] [bigint] NOT NULL,
[DateTime] [datetime] NOT NULL,
[Cashier_ID] [nvarchar](50) NULL,
[CustNum] [nvarchar](12) NOT NULL,
[Trans_Type] [nvarchar](2) NOT NULL,
[Prev_Cust_Balance] [money] NULL,
[Prev_Inv_Balance] [money] NULL,
[Trans_Amount] [money] NOT NULL,
[Payment_Method] [nvarchar](4) NULL,
[Payment_Info] [nvarchar](20) NULL,
[Description] [nvarchar](38) NULL,
[Invoice_Number] [bigint] NOT NULL,
[Store_ID] [nvarchar](10) NOT NULL,
[Dirty] [bit] NOT NULL,
[Station_ID] [nvarchar](5) NULL,
[Payment_Type] [smallint] NULL,
CONSTRAINT [pkAR_Transactions]
PRIMARY KEY CLUSTERED([Store_ID] ASC, [Trans_ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[AR_Transactions]
ADD CONSTRAINT [DF_AR_Transactions_Trans_ID_AR_Transactions]
DEFAULT ((0)) FOR [Trans_ID]
ALTER TABLE [dbo].[AR_Transactions]
ADD CONSTRAINT [DF_AR_Transactions_Invoice_Number_AR_Transactions]
DEFAULT ((0)) FOR [Invoice_Number]
Here is the query that I need to run... its a complete hack to try to auto-increment my inserts myself
BEGIN TRANSACTION
INSERT INTO
[cresql].[dbo].[AR_Transactions](Trans_ID, DateTime , Dirty, Store_ID, Trans_Type,
Cashier_ID, CustNum, Trans_Amount, Prev_Cust_Balance)
SELECT
(SELECT MAX(Trans_ID ) + 1 FROM [cresql].[dbo].[AR_Transactions]),
DATEADD(MINUTE, -30, Getdate()), 1, 1001, 'C', 100199, CustNum,
-Acct_Balance, Acct_Balance
FROM [cresql].[dbo].[Customer]
WHERE Acct_Balance <> 0
UPDATE [cresql].[dbo].[Customer]
SET Acct_Balance = 0
WHERE Acct_Balance <> 0
COMMIT TRANSACTION
To illustrate Martin's point:
And PS: - as Mikael Eriksson rightfully mentions (and documents nicely), this Identity Specification remains grayed out as long as that column you're working on has a default constraint.
You need to expand the "Identity Specification" node to change it via the (Is Identity)
property.
This will rebuild the table so you might also need to go into Tools -> Options -> Designers -> Prevent saving changes that require table re-creation
.
This can be an extremely time consuming operation on large tables as well as entailing a lot of logging and locking. To perform this operation on a large table see my answer here.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With