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 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.