Prev: Send Mail from SQL server 2000
Next: Stored procedure, delete from multiple tables
From: MaMo on 9 Feb 2010 08:46
Hello,

In a stored procedure i want to delete data from multiple tables.

Now i am doing this in my stored procedure:

DELETE FROM table1


From: Uri Dimant on 9 Feb 2010 08:58
See Dan Guzman's script to do that

DECLARE @TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @TruncateStatement
IF @@FETCH_STATUS <> 0 BREAK
RAISERROR (@TruncateStatement, 0, 1) WITH NOWAIT
PRINT (@TruncateStatement
-- EXEC(@TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements




"MaMo" <afraidofspam(a)sorry.com> wrote in message
news:222cb$4b7167d7$d969db07$16824(a)cache90.multikabel.net...
> Hello,
>
> In a stored procedure i want to delete data from multiple tables.
>
> Now i am doing this in my stored procedure:
>
> DELETE FROM table1
>
> DELETE FROM table2
>
> DELETE FROM table3
>
> DELETE FROM table4
>
> ... and so on
>
> Is it possible to do this in a more elegenat manner like holding the table
> names in some kind of array or variable and use a cursor or something to
> do de deletion?
>
> Any help would be very appreciated!!!
>
> Marcel
>
>


From: --CELKO-- on 9 Feb 2010 09:34
>> Is it possible to do this in a more elegenat manner like holding the table names in some kind of array or variable and use a cursor or something to do deletion? <<

REFERENCES .. ON DELETE CASCADE declarative referential action clause
instead of procedural code.
From: Uri Dimant on 10 Feb 2010 01:50
"--CELKO--"
I have met one client when he implement REFERENCES .. ON DELETE CASCADE on
huge tables and it caused terrible locking on the system


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:cc2095ce-7ede-43d1-a270-8f63aafa9a15(a)d37g2000yqa.googlegroups.com...
>>> Is it possible to do this in a more elegenat manner like holding the
>>> table names in some kind of array or variable and use a cursor or
>>> something to do deletion? <<
>
> REFERENCES .. ON DELETE CASCADE declarative referential action clause
> instead of procedural code.


From: TheSQLGuru on 10 Feb 2010 13:24
My guess would be that that happened because the child key wasn't indexed.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Uri Dimant" <urid(a)iscar.co.il> wrote in message
news:OSpaM1hqKHA.1800(a)TK2MSFTNGP02.phx.gbl...
>
> "--CELKO--"
> I have met one client when he implement REFERENCES .. ON DELETE CASCADE
> on huge tables and it caused terrible locking on the system
>
>
> "--CELKO--" <jcelko212(a)earthlink.net> wrote in message
> news:cc2095ce-7ede-43d1-a270-8f63aafa9a15(a)d37g2000yqa.googlegroups.com...
>>>> Is it possible to do this in a more elegenat manner like holding the
>>>> table names in some kind of array or variable and use a cursor or
>>>> something to do deletion? <<
>>
>> REFERENCES .. ON DELETE CASCADE declarative referential action clause
>> instead of procedural code.
>
>


 |  Next  |  Last
Pages: 1 2
Prev: Send Mail from SQL server 2000
Next: Stored procedure, delete from multiple tables