create multiple output files based on query

Question

create multiple output files based on query

Viewing 8 posts - 16 through 22 (of 22 total)

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

Bruin SSCrazy Points: 2091 More actions · Answer 1

Had to change initial query to this.

SELECT distinct substring(ImageName,1,8) as HitName from Image_Measurement_Data(nolock) where ImageName like '202405%' order by hitname OPTION (MAXDOP 0).

Now 2nd qry needs to use HitName against the full table substring (ImageName,1,8) to get csv files created by day

THanks.

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 2

You can create a query for Invoke-SqlCmd that accepts variables. See here: https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

From that page:

$StringArray = "MYVAR1='String1'", "MYVAR2='String2'"
Invoke-Sqlcmd -Query "SELECT $(MYVAR1) AS Var1,$(MYVAR2) AS Var2" -Variable $StringArray

Var1     Var2
----     ----
String1  String2

Or - you can build the query and replace the values:

$query = "SELECT ... FROM ... WHERE ImageName LIKE '$DateFilter'";

I would use the first example - that way I have a 'fixed' query and the only thing changing in each loop is the variable. Basic outline:

$yearMonth = "202405';
$fileDates = Invoke-SqlCmd ... -Query "SELECT DISTINCT left(ImageName, 8) AS FileDate FROM ...";

# Loop through file dates
$fileDates | % {
    $fileDate = $_.FileDate;
    $variableArray = "DateFilter='$fileDate%'";
    $imageFiles = Invoke-SqlCmd ... -Query "SELECT ... FROM ... WHERE ImageName LIKE `$(DateFilter)" -Variable $variableArray;

    $imageFiles | Export-Csv ...;
}

Note: this is just a basic outline and not meant to be the final code. You need to use appropriate variables for your process - and not just use the above directly.

This is just one way of approaching this - there are other ways to go about the process, but the first thing that always needs to happen is that YOU need to determine how you want the process to work.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

Bruin wrote:

Thanks for response, would you be able to mock up an example of how that can be achieve? I just need an easy way to crank out files based off the query output.
Select * from RMcstDetails where ImageName like '202405%'"

Would you actually be allowed to use xp_CmdShell? Most people are not allowed to use it because of some bad information about how it's supposed a "security issue".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2091 More actions · Answer 4

Bruin

SSCrazy

Points: 2091

January 6, 2025 at 1:39 am

#4510567

thank you both .. yes xp_cmdshell is allowed

Bruin SSCrazy Points: 2091 More actions · Answer 5

Okay I now have this running, but have a couple of issues...

Not sure how to code this piece where I just want to pass in the HitDate out of $fileDate = $_.HitName;

I was testing so I hard coded this:

ImageName like '20240501%';"

The second issue was the export-csv. I thought with my example I would just see 1 file created called

20240501.csv

When I ran my versoin it created all 31days for May from HitName with all the same data.

Thanks.

$Data = "SELECT distinct substring(ImageName,1,8) as HitName from Image_Measurement_Data(nolock) where ImageName like '202405%' order by hitname OPTION (MAXDOP 0);"


# Execute the query and store the results
$fileDates = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Data


# Loop through file dates
$fileDates | % {
    $fileDate = $_.HitName;
    
    $imageFiles = Invoke-SqlCmd -ServerInstance $ServerInstance -Database $Database -Query "SELECT * FROM Image_Measurement_Data(nolock) WHERE ImageName like '20240501%';"
        
    $imageFiles | Export-Csv -Path "c:\fileloading\$fileDate.csv" -NoTypeInformation -Append;
    }

Bruin SSCrazy Points: 2091 More actions · Answer 6

I tweaked it some and this looks like I want...

Any improvements or tweaks you can see for performance?

Thanks again.

$Data = "SELECT distinct substring(ImageName,1,8) as HitName from Image_Measurement_Data(nolock) where ImageName like '202405%' order by hitname OPTION (MAXDOP 0);"


# Execute the query and store the results
$fileDates = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Data


# Loop through file dates
$fileDates | % {
    $fileDate = $_.HitName;
    
    $imageFiles = Invoke-SqlCmd -ServerInstance $ServerInstance -Database $Database -Query "SELECT * FROM Image_Measurement_Data(nolock) WHERE substring(ImageName,1,8) = $fileDate;"
        
    $imageFiles | Export-Csv -Path "c:\fileloading\$fileDate.csv" -NoTypeInformation -Append;
  }

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 7

Ok... so you don't need the xp_CmdShell example then.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2091 More actions · Answer 8

I'd like to see it to compare against what I posted.

Thanks.