我正在使用 SQL Server 2008,并且我使用的数据库表的主键不是IDENTITY
列(不知道为什么)。我需要改变这一点。
我在 SQL Server Management Studio 的设计视图中的列属性下,由于某种原因,我无法将标识规范更改为Yes
.
我是否缺少某些东西..我是 SQL Server 新手 - 关于我缺少什么有什么想法吗?
这是创建表
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]
这是我需要运行的查询...这是一个完整的黑客尝试自己自动增加我的插入
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