how to write the delete and reseed for stored procedure?

Sathya Sambath

8y1.2k

hi,

I have one database for eg 300 table ,i have delete specify the 200 table data only and then reseed identity the delete table .

but error withshown .

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
Accepted answer
Nitin has the right answer, it's prefect
Maybe if you like to go more dynamic you can use...
DECLARE  @dynamicSQL nvarchar(max) = ''
SELECT  @dynamicSQL = @dynamicSQL + N'TRUNCATE TABLE [' + [TABLE_SCHEMA] + N'].[' + [TABLE_NAME] + N'] ' + CHAR(13) + 'GO' + CHAR(13) + ';' + CHAR(13) + ''
FROM    INFORMATION_SCHEMA.TABLES
--WHERE .......

PRINT @dynamicSQL
EXECUTE sp_executeSQL @dynamicSQL
Nitin Sontakke8y ago
Furthermore, if it is Microsoft SQL Server, there is no need to use cursor. Instead store all your dynamic SQL in a variable and execute it later.

For example,
declare @dynamicSQL nvarchar(max) = ''
select @dynamicSQL += 'truncate table [dbo].[' + name + ']
'
from sys.tables
where 1 = 1
  and .....

execute sp_executeSQL @dynamicSQL
Better this way, I believe.
Shahriar Nikkhah8y ago
Make it simple

if you can TRUNCATE a table it will get automatically reseeded from what it was
nik