asked on

Bulk Updates

Hi Experts,

I'm looking to write SQL statements affecting all tables of my database.
For example

A- Create a new field for each table Named DateExported (DateTime)
B- Set that DateExported field to today's date for all records in all tables.
C- Write some SQL like the following, "Delete * from ...(each table name here) Where DateExported is null"

How can these be accomplished the easiest way?

I recall doing that in excel a while back, by setting a formula and dragging down to all rows but dont remember exactly the formula.
I do have an excel sheet containing all my table names.

Thanks in advance

ASKER CERTIFIED SOLUTION

Dustin Saunders

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

bfuchs

ASKER

@Dustin,
This sounds great.
I use SQL 2008, does that work there?
If not, can I download SQL Express 2014/6 to accomplish that?

Thanks,
Ben

Dustin Saunders

The stored procedure should be in 2008 if I recall, so you should be good to go.

_agx_

(no points...)

Just confirming it works with 2008

bfuchs

ASKER

Thank you!

bfuchs

ASKER

@Dustin,
I got the below error when running the following (in test db).

exec sp_msforeachtable 'UPDATE ? SET DateExported = GETDATE()';

Open in new window

Msg 1934, Level 16, State 1, Line 1
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 1934, Level 16, State 1, Line 1
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 1934, Level 16, State 1, Line 1
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 1934, Level 16, State 1, Line 1
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 50000, Level 16, State 1, Procedure trgInsPatientsEmployeesScheduleHistory_HC, Line 24
Overlapping schedule(s), UPDATE aborted!
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Open in new window

Thanks,
Ben

Dustin Saunders

Sounds like the database has a lot going on, do you have a large number of triggers and constraints in your DB?

bfuchs

ASKER

we have some triggers but not too many, and why should a trigger cause this error?
also what constraints are you referring to?
where can I check?

Thanks,
Ben

Aneesh

You need to add few more line s

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"
EXEC sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

bfuchs

ASKER

Thank you very much Aneesh!

Aneesh

One of the main issues using a DELETE statement inside a sp_msforeachtable is, depending on the no of records you are deleting, your SQL log could fill up the space. If you use a dynamic sql then you could run a CHECKPOINT statement in order to handle this (as shown on the link i posted above ) provided your database is in simple recovery

bfuchs

ASKER

Hi Experts,
See following post.
https://www.experts-exchange.com/questions/29095487/error-in-exec-sp-msforeachtable-'delete-from.html#questionAdd
Thanks,
Ben