Dynamic Query with sql file name

Welcome Forums General PowerShell Q&A Dynamic Query with sql file name

This topic contains 5 replies, has 3 voices, and was last updated by

 
Participant
3 months ago.

  • Author
    Posts
  • #111833
    joe

    Participant
    Points: 0
    Rank: Member

    Hello,

    I have n number of sql file in a folder like below.

    dbo.sql1.sql

    stg.sql2.sql

    I want to create dynamic sql like:

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dbo' AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = 'sql1' )
    BEGIN

    Table schema and name should come from the file name without.sql file.

    I was trying something like below :

    Get-ChildItem C:\Folder | foreach{$_.Name -replace ".sql",""}|
    foreach{"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= $_ AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = '$_' )
    BEGIN"}

    Here I am not able to generate schema Table schema at the runtime.

    Once it's done then I want to put this script in the .sql  file before the create command.

    Thanks,

  • #111841

    Participant
    Points: 884
    Helping Hand
    Rank: Major Contributor

    Basically, you want SQL query to be created based on the file name
    If the file name patterns are all same, then ...

    Get-ChildItem C:\Folder -File | ForEach-Object -Process {
    $SplitFileName = $_.BaseName -split '\.'
    "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= $($SplitFileName[0]) AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = '$($SplitFileName[1])' )BEGIN" | Out-File -FilePath "c:\$($_.BaseName)-query.sql"
    }
    

    btwn,
    I request you to use code formatting tags while posting code in the forum which makes other to easily understand your code, below link will help you.

    • #111844
      joe

      Participant
      Points: 0
      Rank: Member

      @Kvprasoon, Thanks for the reply now the dynamic query is working.

      And in the second part I don't want to create the new file but in the existing .sql file  I have the table creation script and before the Create statement I want to append the newly created if exist command.

    • #111848

      Participant
      Points: 884
      Helping Hand
      Rank: Major Contributor

      @Joe – You can append to a file, "Append – not prepend". So you can have logic to re create your existing query with multiple appends and yourself can do it. Just go through below documentation.

      Get-Help Out-File -Full

      Put on some logic and update when you encounter any problems, we are here to help you.

  • #111842

    Participant
    Points: 321
    Helping Hand
    Rank: Contributor

    Color me confused, as I am having a really hard time trying to get what you are after.
    Bare with me, I'm old and halftimers kicks in, well, you know.

    You are getting a bunch of files with this...

    Get-ChildItem C:\Folder | foreach{$_.Name -replace ".sql",""}
    

    Then passing those file names (dbo.sql1, stg.sql2) sequentially, to this ...

    foreach{"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= $_ AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = '$_' ) BEGIN"}
    

    ... what exactly did you expect to happen?

    In that second foreach, you are not passing any kind of executable command or statement, just a string with the filename.

    Get-ChildItem D:\Temp\*.sql | 
    foreach{$_.Name -replace ".sql",""}
    
    # Results
    
    dbo1
    stg2
    

    Why are you removing both .sql tokens again?
    Or did you mean to do this?

    #  Remove the file extension
    Get-ChildItem D:\Temp\*.sql | 
    foreach{$_.Basename}
    
    # Results
    
    dbo.sql1
    stg.sql2
    

    and them pass that adjusted filename to this string?

    Get-ChildItem D:\Temp\*.sql | 
    foreach{$_.Basename} |
    foreach{"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= $_ AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = '$_' ) BEGIN"}
    
    # Results
    
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= dbo.sql1 AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = 'dbo.sql1' ) BEGIN
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= stg.sql2 AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = 'stg.sql2' ) BEGIN
    

    The above is just passing a string without calling SQL to run it. Is that what you meant to do?

    Then pass those to some other file to run as a SQL command, using either Out-File or redirects?

    I am working under the assumption that these are different SQL instances, as per the .sql1 and .sql2

    • #111845
      joe

      Participant
      Points: 0
      Rank: Member

      @postanote Thanks for understanding my code, Actually I am Couple of days old in PowerShell. learn some basic and trying something. basically, I want to achieve what  @Kvprasson replied.

       

       

The topic ‘Dynamic Query with sql file name’ is closed to new replies.