某些情况之下,我们需要做清除表的记录。可以使用TRUNCATE函数实现,并让自动增长的主键ID重新开始。但是如果遇上表与其它表有关联,即是说主键是其它表的外键时,使用这个函数清除表记录,会接收到一条Error的信息:
Msg 4712, Level 16, State 1, Line 1 Cannot truncate table 'dbo.topic' because it is being referenced by a FOREIGN KEY constraint. |
我们是否有其它替代方法可以使用呢?答案是肯定的。Insus.NET有写了一个存储过程,即是使用DELETE 和 DBCC一起来完成。
你可以参考下列存储过程:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
IF
OBJECT_ID
(
'
[dbo].[InsusTRUNCATE]
'
)
IS
NOT
NULL
DROP PROCEDURE [ dbo ] . [ InsusTRUNCATE ]
GO
CREATE PROCEDURE [ dbo ] . [ InsusTRUNCATE ]
(
@TableName NVARCHAR ( 128 ),
@NewReseedValue INT
)
AS
EXECUTE ( ' DELETE FROM ' + @TableName )
DBCC CHECKIDENT( @TableName , RESEED, @NewReseedValue )
DROP PROCEDURE [ dbo ] . [ InsusTRUNCATE ]
GO
CREATE PROCEDURE [ dbo ] . [ InsusTRUNCATE ]
(
@TableName NVARCHAR ( 128 ),
@NewReseedValue INT
)
AS
EXECUTE ( ' DELETE FROM ' + @TableName )
DBCC CHECKIDENT( @TableName , RESEED, @NewReseedValue )
调用存储过程演示:
EXECUTE
[
dbo
]
.
[
InsusTRUNCATE
]
'
dbo.topic
'
,
0