CODEWITHSUNDEEP
sqlpowershellsplit
Phani
Phani

Reputation: 863

Split a sql file into multiple files using powershell

Hi I have a single file with multiple(~400) Insert statements like this and I need to split this to multiple files having one insert statement in each of those files. I am guessing it can be done using powershell effectively.

PRINT N' Populating aud.DT'
GO
-- Insert into DT for Sanity
TRUNCATE TABLE aud.DT;
SET IDENTITY_INSERT aud.DT ON;
insert into aud.DT (Key,TableName,Description,SQL,Enabled,SingleRowTest) 
values (1,'dm.FCA','Desc1',
'select count(*) cnt 
from @SourceDB.dm.FCA  cp 
where cp.ProductionAmount > 0 and cp.ind = 0
AND cp.PMK BETWEEN CONVERT(VARCHAR(6), DATEADD(month, -2, GETUTCDATE()), 112)
AND    CONVERT(VARCHAR(6), GETUTCDATE(), 112)',1,0);

insert into aud.DT (Key,TableName,Description,SQL,Enabled,SingleRowTest) 
values (2,'dm.FCA','Desc2',
'select count(*) cnt
FROM @SourceDB.dm.FCA cp     
JOIN @SourceDB.dm.DimSubsidiary ds ON cp.key = ds.key 
WHERE (ds.SubsidiaryCode = ''NK'') 
and PMK  BETWEEN CONVERT(VARCHAR(6), DATEADD(month, -2, GETUTCDATE()), 112)
AND    CONVERT(VARCHAR(6), GETUTCDATE(), 112)',1,0);

insert into aud.DT (Key,TableName,Description,SQL,Enabled,SingleRowTest) 
values (3,' dm.FCR','Desc3',
from @SourceDB.dm.FCR  cp       
'select count(*) cnt 
inner join @SourceDB.ref.ask a on cp.key = a.key       
inner join @SourceDB.ref.clk c on cp.clk = c.clk       
where a.asi >= 300  and a.asi <= 399         
and c.cli > 200  and cp.pi = 0  
AND cp.PMK BETWEEN (CONVERT(VARCHAR(6), DATEADD(month, -2, GETUTCDATE()), 112))
        AND    CONVERT(VARCHAR(6), GETUTCDATE(), 112) ',1,0);

Also the file names need to be Test_[Key]_[Tablename.sql] like Test_0001_FCA.sql,Test_0010_FCR.sql where [Key] is the value inserting into the aud.DT table

Upvotes: 1

Views: 1284

Answers (1)

morgb
morgb

Reputation: 2312

This should help get you going in the right direction:

$desktop = [Environment]::GetFolderPath("Desktop") #saving to desktop, adjust as needed
$file = Get-Content "C:\Temp\test.sql" #replace w/ your file location
$content = "" #variable to hold content for each individual file

$pre = "Test_"
$key = ""
$tableName = ""

foreach ($line in $file)
{

    if($line -eq "") #if line is blank, export file and reset everything
    {
      $outFile = $desktop + "\" + $pre + $key + "_" + $tableName + ".sql"
      $content | Out-File $outFile

      $key = ""
      $tableName = ""
      $content = ""
    }
    else 
    { #otherwise, collect line
        $content += $line + "`r`n"
        $words = $line -split " "

        if($words[0] -eq "insert") #if this is the insert, take the table name
        {
          $tableName = $words[2]
        }

        if($words[0] -eq "values") #if this is the values, take the key
        {
          $k = $words[1] -split ","
          $key = $k[0].Replace("(","").Replace(",","")
        }
    }


} # end loop
# get last chunk for final file
if($content -ne "")
{
    $outFile = $desktop + "\" + $pre + $key + "_" + $tableName + ".sql"
    $content | Out-File $outFile
}

Upvotes: 2

Related Questions