SQL Server query Automation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL Server query Automation

SQL Server query Automation

Rate Topic

Display Mode

Topic Options

Author

Message

krishna30

Posted Tuesday, October 30, 2012 3:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 3:39 PM
Points: 5, Visits: 16

Hi,

I am testing one application I am preparing some queries as given below

Step1:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'CMC_PDRA_RATE_AGES'
Step 1 Output

COLUMN_NAME
___________
HIST_ROW_ID
PDRA_AGE_FROM
PDRA_REF_DTM

Step 2:
Use the Columns name from step 1 and create a delte statement as given below.

DELETE FROM [FACETS_TEST].CMC_PDRA_RATE_AGES WHERE HIST_ROW_ID = '' AND PDRA_AGE_FROM = '' AND PDRA_REF_DTM = '';

I have about 300 tables like to do. Is there any automated SQL statement that someone can recommend.

THanks,
Krishna

Post #1379063

CapnHector

Posted Tuesday, October 30, 2012 4:06 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

What you are asking is possible through the blessing and curse of dynamic sql. before you start in on dynamic sql have a read through this as it has a detailed section on SQL Injection risks when using dynamic sql along with a good primer on dynamic sql.

http://www.sommarskog.se/dynamic_sql.html

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler

Post #1379066

GilaMonster

Posted Tuesday, October 30, 2012 4:58 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

I strongly recommend you do not go that design path. It's possible, it's not going to be fun and honestly it's not that good of an idea.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1379078

krishna30

Posted Tuesday, October 30, 2012 5:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 3:39 PM
Points: 5, Visits: 16

I am kind of new in writing Procedure's or dynamic SQL's. Any suggestion that you could help me out with.

This manual work is just making me go crazy Sad

Post #1379095

George M Parker

Posted Tuesday, October 30, 2012 6:02 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:13 PM
Points: 516, Visits: 1,004

I concur with Gail on this one. If you are trying to come up with a one time process to generate the delete statements to incorporate into some other type of code, then it might be worth considering. But if you are trying to repeat the delete statement generation process, you might want to rethink your design.

Post #1379102

GilaMonster

Posted Wednesday, October 31, 2012 1:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

krishna30 (10/30/2012)

I am kind of new in writing Procedure's or dynamic SQL's. Any suggestion that you could help me out with.

A procedure should do a single thing. The academic term is 'single responsibility'

If writing in C# or java or the like, you wouldn't write a class that can do one of 20 things depending on the parameters, that would be against so many coding practices. Same thing with SQL, don't write a procedure that depending on the parameters passed selects an employee, updates an order, deletes a product or drops the database.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1379171

« Prev Topic | Next Topic »

Permissions