Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server query Automation Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 3:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, October 30, 2012 4:06 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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
Posted Tuesday, October 30, 2012 4:58 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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
Posted Tuesday, October 30, 2012 5:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Post #1379095
Posted Tuesday, October 30, 2012 6:02 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:15 AM
Points: 652, Visits: 1,428
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
Posted Wednesday, October 31, 2012 1:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 »

Add to briefcase

Permissions Expand / Collapse