Delete records dynamically from a SQL Server table

Question

I have a table called Name_Tables that contains the names of other tables.

Some of that tables have names like dbo.companyname.test and others dbo.testtest.

For all these tables, mentioned in the table Name_Tables, I would like to delete the data, so that I have empty tables. I would like to do that by using dynamic SQL.

My code is executable and I can delete some records, but not all of them. Maybe you have an idea where my mistakes are...

DECLARE @i INT = 1
DECLARE @count INT = 50
DECLARE @tablename NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
DECLARE @value NVARCHAR (MAX)`

WHILE(@i <= @count)
BEGIN       
    SET @tablename = (SELECT TOP (1) Expression FROM Name_Tables WHERE Id = @i);

    IF EXISTS (SELECT '[dbo].[company$'+@tablename+']')
    BEGIN
        SET @sql = N'DELETE FROM [dbo].[company$'+@tablename+']';

        EXECUTE sp_executesql @sql;
    END
    ELSE IF EXISTS (SELECT '[dbo].[' + @tablename+']')
    BEGIN
        SET @sql = N'DELETE FROM [dbo].[' + @tablename+']';
        EXECUTE sp_executesql @sql;
    END
    ELSE
    BEGIN
        SET @i += 1;
    END

    SET @i += 1
END

One would argue that your fundamental problem is storing the same data in different tables, only distinguished by company names. You should fix the data model. — Gordon Linoff, Jun 22 '18 at 10:26
@GordonLinoff When you have mutiple companies in ERP system such as Navision it creates the same tables just with prefix of company. — SqlKindaGuy, Jun 22 '18 at 11:50
Are there any foreign key references between tables that might require a specific order in which the tables must be processed, e.g. you can't delete `Customers` until you have deleted their `Orders`? — HABO, Jul 02 '18 at 14:56

score 0 · Answer 1 · answered Jun 22 '18 at 10:33

0

Might I suggest using a cursor query - here's one I made earlier which you could modify for your purpose.

SQL Server view - bad naming convention?

answered Jun 22 '18 at 10:33

JonTout

618
6
14

score 0 · Answer 2 · answered Jun 22 '18 at 11:47

0

If you are delete statement is without Where clause, then it should delete all records from the table. What is the error. Print the SQL statement that you had prepared using dynamic SQL loop. and try to execute in SSMS. Share error if any.

answered Jun 22 '18 at 11:47

Pravin Pandit

1

score 0 · Answer 3 · answered Jun 25 '18 at 07:19

0

Thank you very much for helping me with my code. I found out, that I just had to delete the last else branch and change the else-if branch to an else branch. Now it is working fine :)

answered Jun 25 '18 at 07:19

malue

59
2
10

Delete records dynamically from a SQL Server table

3 Answers3