Clear table data in SQL loop, SQL Loop

Clear table data in SQL loop, SQL Loop
SQL statement to clear table data

Recently, database clearance tests are often required for projects, but truncate is slow and time-consuming. Therefore, you have written an SQL statement to clear table data in batches. This facilitates the next use of flexible statements. Statements can be used not only to clear data, but also to update and delete data in batches.

Logic:
Returns the table name of each statement executed based on the string to be split.
Returns the number of tables in the string, that is, the number of cycles, based on split.
Each cycle first obtains the name of the table executing the statement and then removes the name of the executed table under substring until the loop ends.

-- Define -- declare @ I int -- loop variable declare @ length int -- number of cycles declare @ tableList varchar (Max) -- The table string to be recycled declare @ split varchar (Max) -- split string character declare @ tableName varchar (Max) -- Name of the table executing the statement

-- Initial -- set @ I = 0 set @ tableList = 'atable, BTable, CTable 'set @ split = ', 'set @ tableList = ltrim (rtrim (@ tableList )) -- clear the Left and Right spaces of the string set @ length = (select len (@ tableList)-len (REPLACE (@ tableList, @ split, '') -- calculate the number of cycles required

Execute the statement at the beginning to write the truncate table @ tableName directly.
This certainly won't work because @ tableName is a varchar type variable that is equivalent to truncate table 'table name' during execution, so an error is reported and then the exec () dynamic execution statement is used.

While (@ I <@ length + 1) -- cyclic statement beginif (charindex (@ split, @ tableList) = 0) -- determine whether there is only one table beginset @ tableName = @ tableListendelsebegin set @ tableName = substring (@ tableList, 0, charindex (@ split, @ tableList )) -- retrieves the table name endexec ('truncate table' + @ tableName) from the start to the First @ split character in the string -- executes the statement set @ tableList = substring (@ tableList, charindex (@ split, @ tableList) + 1, len (@ tableList) -- remove the name of the executed table set @ I = @ I + 1end