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
LVL 5
bfuchsAsked:
Who is Participating?
 
Dustin SaundersDirector of OperationsCommented:
What version of SQL?  You can use
exec sp_msforeachtable

Open in new window

to accomplish this.

To add the column
exec sp_msforeachtable 'ALTER TABLE ? ADD [DateExported] [DateTime] NULL';

Open in new window


To insert default values
exec sp_msforeachtable 'UPDATE ? SET DateExported = GETDATE()';

Open in new window


To delete NULLs
exec sp_msforeachtable 'DELETE FROM ? WHERE DateExported IS NULL';

Open in new window


Be sure to back up or set up a test Database when running to ensure expected results.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
bfuchsAuthor Commented:
@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
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

REGISTER NOW
 
Dustin SaundersDirector of OperationsCommented:
The stored procedure should be in 2008 if I recall, so you should be good to go.
0
 
_agx_Commented:
(no points...)

Just confirming it works with 2008
0
 
bfuchsAuthor Commented:
Thank you!
0
 
bfuchsAuthor Commented:
@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
0
 
Dustin SaundersDirector of OperationsCommented:
Sounds like the database has a lot going on, do you have a large number of triggers and constraints in your DB?
0
 
bfuchsAuthor Commented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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'
1
 
bfuchsAuthor Commented:
Thank you very much Aneesh!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
bfuchsAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here