Post reply

SQL Server query Automation

  • October 30, 2012 at 3:59 pm

    #264094

    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

  • October 30, 2012 at 4:06 pm

    #1554409

    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[/url] 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[/url]

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

  • October 30, 2012 at 4:58 pm

    #1554420

    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, MVP, M.Sc (Comp Sci)
    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

  • October 30, 2012 at 5:47 pm

    #1554434

    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 🙁

  • October 30, 2012 at 6:02 pm

    #1554439

    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.

  • October 31, 2012 at 1:15 am

    #1554494

    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, MVP, M.Sc (Comp Sci)
    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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply