1.为数据表添加一个新字段
Alter TABLE [dbo].[CustomerBackupConfig] Add [Stamp] [timestamp] NULL
GO
2.为数据表添加两个新字段
Alter Table tblStates Add [TaxRate] money not null default 0,
[Transit] int not null default 0
GO
3.为数据表删除一个字段
Alter Table [dbo].[tblOrder] Drop Column [CookieID]
GO
4.修改数据表一个字段的定义
Alter Table [dbo].[tblOrder] Alter Column [CookieID] int not null
GO
5.删除数据表
Drop Table tblTaxRate
GO
6.为数据表去掉一个外键关联,然后再添加一个外键关联
Alter TABLE [dbo].[OrderPackageOption] Drop CONSTRAINT [FK_OrderPackageOption_OrderPackage]
ALTER TABLE [dbo].[OrderPackageOption] ADD
CONSTRAINT [FK_OrderPackageOption_OrderPackage] FOREIGN KEY
(
[OrderPackageID]
) REFERENCES [dbo].[OrderPackage] (
[OrderPackageID]
)
GO
7. 为数据表添加两个外键关联,并且设置其中之一不检查现有数据是否符合关联要求。
ALTER TABLE [dbo].[Account] ADD
CONSTRAINT [FK_Account_State] FOREIGN KEY
(
[StateID]
) REFERENCES [dbo].[State] (
[StateID]
),
CONSTRAINT [FK_Account_TimeZones] FOREIGN KEY
(
[TimeZoneID]
) REFERENCES [dbo].[TimeZones] (
[timeid]
)
GO
Alter table [dbo].[Account] NoCheck Constraint [FK_Account_TimeZones]
GO
8. 增加外键时不检查当前数据
ALTER TABLE [dbo].[Account] With NoCheck
ADD CONSTRAINT [FK_Account_AccountType]
FOREIGN KEY
(
[TypeID]
) REFERENCES [dbo].[AccountType] (
[TypeID]
)
GO
9.为了对数据表进行批量的数据导入,需要将identity_insert先打开,执行完后在关闭。其目的是避开自增列的检查,顺利执行插入操作。
set identity_insert [dbo].[PSTNUsageNotes] on
--在这里执行对PSTNUsageNotes表的批量插入操作
set identity_insert [dbo].[PSTNUsageNotes] off