Tuesday 15 March 2022

Split or Separator in SQL Query

 CREATE TABLE #tmp_TBL(TableName varchar(255))

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RAGRDV__RAG__ErrorMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RAGRDV__RAG__WarningMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RARLDV__RAL__ErrorMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RARLDV__RAL__WarningMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RAPRILOT_Q__RAP__ErrorMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RAPRILOT_Q__AP__WarningMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RCHSRLEC1Q__RCH__ErrorMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RCHSLEC1Q__CH__WarningMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RDLDRV__DL__ErrorMessages')

INSERT INTO #tmp_TBL VALUES ('Prjid__Srv1__RDLDV__DL__WarningMessages')


SELECT 

[TableName]

,LEFT([TableName],CHARINDEX('__',[TableName])-1) AS LeftPart

,(RIGHT([TableName], CHARINDEX('__', REVERSE([TableName]))-1 )) AS RightPart

,SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1) AS Removed1RightPart

,SUBSTRING((SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)),1,LEN(SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)) - CHARINDEX('__', REVERSE(SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)))-1) AS Removed2RightParts

,REPLACE(REPLACE([TableName],REVERSE(SUBSTRING(REVERSE([TableName]),1,CHARINDEX('__',REVERSE([TableName])) +1)),''),LEFT([TableName],CHARINDEX('__', [TableName]) +1),'') AS RemovedLeftRightPart

,REPLACE(SUBSTRING((SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)),1,LEN(SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)) - CHARINDEX('__', REVERSE(SUBSTRING([TableName],1,LEN([TableName]) - CHARINDEX('__', REVERSE([TableName]))-1)))-1),LEFT([TableName],CHARINDEX('__',[TableName])+1),'') AS Removed2RightParts1LeftPart


/*Most Usefull

=============


[TableName]='18__10_192_143_18__RCTRDEV__CTRPRROD__ObjectsbyCategories'

--,REPLACE([TableName],REVERSE(SUBSTRING(REVERSE([TableName]),1,CHARINDEX(''__'',REVERSE([TableName])) +1)),'''')


--,LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))

,RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))


,SUBSTRING(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),1,LEN(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))) - CHARINDEX(''__'', REVERSE(RIGHT(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))))-1) as Server_Instance_Schema



,SUBSTRING(SUBSTRING(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),1,LEN(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))) - CHARINDEX(''__'', REVERSE(RIGHT(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))))-1),1,LEN(SUBSTRING(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),1,LEN(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))) - CHARINDEX(''__'', REVERSE(RIGHT(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))))-1)) - CHARINDEX(''__'', REVERSE(SUBSTRING(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),1,LEN(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))) - CHARINDEX(''__'', REVERSE(RIGHT(RIGHT([TableName],  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1))),  len([TableName])-LEN(LEFT([TableName],CHARINDEX(''__'',[TableName])+1)))))-1)))-1)  AS Server_Instance


===========================

*/

 FROM #tmp_TBL

DROP TABLE #tmp_TBL

Wednesday 19 January 2022

How to load data from multiple csv files to multiple tables according to filename


 #Read csv file 

#open connection to database using bulkcopy

#convert array to data table

#bulkload data into table

#note: column sequence and data types should match

$ServerName='DESK-HCD8L\INDLA'

$DatabaseName='RAJ_IND_Analysis'

function Get-Type 

    param($type) 

 

$types = @( 

'System.Boolean', 

'System.Byte[]', 

'System.Byte', 

'System.Char', 

'System.nvarchar',

'System.Datetime', 

'System.Decimal', 

'System.Double', 

'System.Guid', 

'System.Int16', 

'System.Int32', 

'System.Int64', 

'System.Single', 

'System.UInt16', 

'System.UInt32', 

'System.UInt64') 

 

    if ( $types -contains $type ) { 

        Write-Output "$type" 

    } 

    else { 

        Write-Output 'System.String' 

         

    } 

}



function Out-DataTable 

    [CmdletBinding()] 

    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject) 

 

    Begin 

    { 

        $dt = new-object Data.datatable   

        $First = $true  

    } 

    Process 

    { 

        foreach ($object in $InputObject) 

        { 

            $DR = $DT.NewRow()   

            foreach($property in $object.PsObject.get_properties()) 

            {   

                if ($first) 

                {   

                    $Col =  new-object Data.DataColumn   

                    $Col.ColumnName = $property.Name.ToString()   

                    if ($property.value) 

                    { 

                        if ($property.value -isnot [System.DBNull]) { 

                            $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 

                         } 

                    } 

                    $DT.Columns.Add($Col) 

                }   

                if ($property.Gettype().IsArray) { 

                    $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 

                }   

               else { 

                    $DR.Item($property.Name) = $property.value 

                } 

            }   

            $DT.Rows.Add($DR)   

            $First = $false 

        } 

    }  

      

    End 

    { 

        Write-Output @(,($dt)) 

    } 

 

} #Out-DataTable

$FileExtension = '*csv*.*'#you can use wildcards here for name and for extension

$SearchInfolder = 'D:\CSVMetaData\Ora2pg_reports\'

$FileNames=Get-ChildItem -Path $SearchInfolder -Filter $FileExtension -Recurse | %{$_.FullName}

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($FileNames)

foreach($Files in $FileNames)

{

$file=$Files

$FileName=[System.IO.Path]::GetFileNameWithoutExtension($Files)

$TblName="["+$FileName.Replace(".","_")+"]"

#write-output $TblName

$cn = new-object system.data.SqlClient.SQLConnection("Data Source="+$ServerName+";Integrated Security=SSPI;Initial Catalog="+$DatabaseName+"");

#Write-output $fileName

$cn.Open()

$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn

$bc.BatchSize = 10000;

$bc.BulkCopyTimeout = 1000

$bc.DestinationTableName = $TblName

#$bc.DestinationTableName = "["+ $fileName.Replace(".","_") +"]"

Write-Output $bc.DestinationTableName

$data = Import-Csv $file | Out-DataTable

$bc.WriteToServer($data)


}

How to create multiple tables from multiple csv files according to filename


 $ServerName='DESK-HCD8L\INDLA'

$DatabaseName='RAJ_IND_Analysis'

$FileExtension = '*csv*.*'#you can use wildcards here for name and for extension

$SearchInfolder = 'D:\CSVMetaData\Ora2pg_reports\'

$FileNames=Get-ChildItem -Path $SearchInfolder -Filter $FileExtension -Recurse | %{$_.FullName}

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($FileNames)

foreach($Files in $FileNames)

{

#$FileNamess=Get-ChildItem -Path $searchinfolder -Filter $fileExtension -Recurse | %{$_.FullName}

#$fileNa =  [System.IO.Path]::GetFileNameWithoutExtension($FileNamess)

#$Tbl =$fileNa.Replace(".","_")

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString = "Server='$ServerName';Database='$DatabaseName';trusted_connection=true;"

$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$Header = (Get-Content $Files | Select-Object -First 1).Split(',')

$CleanHeader = @()

foreach($H in $Header){

$CleanValue = $H -Replace '[^a-zA-Z0-9_]',''

$CleanHeader += $CleanValue

}

$FileName=[System.IO.Path]::GetFileNameWithoutExtension($Files)

$TblName=$FileName.Replace(".","_")

write-output $TblName

$StagingTableName = $TblName

$sql = @("IF EXISTS (SELECT name FROM sys.tables WHERE name = '$StagingTableName') DROP TABLE [$StagingTableName];")

#$sql = @()

$sql += ("CREATE TABLE dbo.[$StagingTableName]($($CleanHeader[0]) VARCHAR(255)")

$CleanHeader[1..$CleanHeader.Length] | ForEach-Object {$sql += ",[$_] VARCHAR(255)"}

$sql += ");"

$Command.CommandText = $sql

$Command.ExecuteNonQuery()

$Connection.Close();

#Write-output $sql

}


Tuesday 15 June 2021

List out all Object Count details in all databases in SQL Server


DECLARE @DBNAME VARCHAR(250)

DECLARE @CMD VARCHAR(MAX);

CREATE TABLE #OBJECTCOUNTDETAILS

(

INSTANCENAME VARCHAR(50),

DBNAME VARCHAR(50),

OBJECTTYPE VARCHAR(50),

CNT INT

)

DECLARE DBNAMES CURSOR FOR

SELECT NAME FROM SYS.DATABASES

OPEN DBNAMES

FETCH NEXT FROM DBNAMES INTO @DBNAME

WHILE @@FETCH_STATUS=0

BEGIN

SET @CMD='USE '+@DBNAME+CHAR(13)+ 'SELECT @@SERVERNAME,DB_NAME(),TYPE_DESC,COUNT(TYPE_DESC) 

   FROM SYS.OBJECTS

   WHERE IS_MS_SHIPPED=0 GROUP BY TYPE_DESC'

-- PRINT @CMD

INSERT INTO #OBJECTCOUNTDETAILS EXEC(@CMD)

FETCH NEXT FROM DBNAMES INTO @DBNAME

END

CLOSE DBNAMES

DEALLOCATE DBNAMES

SELECT * FROM #OBJECTCOUNTDETAILS 

DROP TABLE #OBJECTCOUNTDETAILS 

Tuesday 20 October 2020

Lazy writer in SQL Server

 

Lazy writer in SQL Server:-

  • Lazy writer check each page header and verify whether all transactions in the pages are committed then pages will be flushed to disk… [Page header contains an infusion of whether committed\uncommitted]
  • This is one of the background processes where works only from BUFFER Cache.
  • The lazy writer works only when the system is running with memory pressure or running out of memory.
  • User cannot see or create, manual lazy writer operation in SQL Server
  • We cannot able to capture lazy writer information into SQL Server logs.
  • Lazy writer allocating space for new pages in the buffer pool
  • Lazy writer Checking for memory pressure.
  • Lazy writer Only works with the buffer pool but not in the log file.

Note: The prime job of a lazy writer is it flush the pages from buffer to disk.

Difference Between Checkpoint And Lazy Writer

 

CHECKPOINT vs LAZY WRITER


CHECKPOINTLAZY WRITER
Checkpoint runs in an only transaction log fileThe lazy writer operates from a buffer pool
The checkpoint is the logged operation and writes to Tlog fileThe lazy writer  is the non-logged operation and did not write to Tlog file
Checkpoint can be controlled by user and SQL server engine as wellThe lazy writer only operate by the Sql server engine
The checkpoint is the background process which triggers every 3 secThe lazy writer does not have any fixed timeline and only occurs when there is memory pressure in the buffer pool
We can able to track checkpoint into Sql server logs by enabling the trace 3502Lazy writer information cannot be able to track into SQL Server logs.
In memory, free pages list not taken care and kept free listIn memory, free pages list  taken care and kept free list
We can fire a query to see checkpoint information by using Select * from:: fn_dblog (null, null) WHERE [Operation] like ‘%CKPT’

 

We don’t have any query to see lazy writer information
Command: Checkpoint  [time in a sec]No command available

Thursday 1 October 2020

PowerShell Scripts


PSScript 1:

The below mentioned PowerShell script is used to load all CSV files into SQL Server at a time. 

Note: Before executing this script need to check the SQL Server Instance name, Database name, and finally path of the CSV files.

 

function CSVUpload 

{

param(

    [parameter(Mandatory)]

    [ValidateNotNullOrEmpty()]

    [string] $serverName,


    [parameter(Mandatory)]

    [ValidateNotNullOrEmpty()]

    [string] $dbCheck,


    [parameter(Mandatory)]

    [ValidateNotNullOrEmpty()]

    [string] $pth

)


$strQuery = ""


$fls = Get-ChildItem -Path 'C:\\PSScript\\RINDLA\\' 

#$flsDifrectories =  $fls  | where {$_.mode -eq 'd----' } | select Name

#$flsDifrectories | Out-Host 

$csvAll=$fls | where {$_.name.EndsWith('.csv') } | select Name

foreach ($tree in $csvAll) {

$fileName='C:\\PSScript\\RINDLA\\'+$tree.Name 

if((Get-Content $fileName))

{

 $file = Import-Csv $fileName | Get-Member -MemberType NoteProperty

   #Check for table exists or not 

    $cn2 = new-object system.data.SqlClient.SQLConnection("Data Source="+'RAJ-PC\RINDLA'+";Integrated Security=SSPI;Initial Catalog="+'Sales_Info'+"");

     $strQueryTest= "select name from sys.tables where name = '"+$tree.Name.Replace(".csv","").Replace(".","_")+"'"

    $cmd = new-object system.data.sqlclient.sqlcommand( $strQueryTest, $cn2);

    $cn2.Open();

    $movedUserDetails= $cmd.ExecuteReader()

   try

   {

     

     if(! $movedUserDetails.HasRows )

     {

     $isExists="false"

     

     }

     if( $movedUserDetails.HasRows)

     {

    

        while ($movedUserDetails.Read())

         {

           if($movedUserDetails.GetValue(0) -eq $tree.Name.Replace(".csv","").Replace(".","_"))

           {

            $isExists="true"      

           }

         }

         }

     }

     catch

     {

       #log error

       throw "Error reading file "+$tree.Name

     }

     finally

     {

       $cn2.Close() 

  

     }


     #create table for new file 

      if($isExists -eq "false")

         {

         

         $strQuery = "Create Table ["+ $tree.Name.Replace(".csv","").Replace(".","_") +"]" 

        $strQueryCol="" 

         foreach ($col in $file) {

          $strQueryCol= $strQueryCol+ ",["+$col.Name + "] varchar (max)"

        }

        if($strQueryCol.Length -gt 0)

        {

         $isExists ="true";

        $strQueryCol = $strQueryCol.Substring(1,$strQueryCol.Length-1)

        if( $strQueryCol -ne "")

        {

         $strQuery =  $strQuery + " ( " + $strQueryCol + " )"

        }

          $cmdNew = new-object system.data.sqlclient.sqlcommand( $strQuery, $cn2);

           $cn2.Open();

            $cmdNew.ExecuteNonQuery();

            $cn2.Close() 

        }

         

       }


         #Insert data in table 

         $QueryInsert=""

         $data =Import-Csv  $fileName

        $fileName

        $i=0;

         if(!$data.length)

      {

      foreach ($dt in $data[0])

        {

        $Name=""

        $Values=""

             $dt.psobject.Properties | foreach { 

             if($Name -eq "")

             {

              $Name =$Name +"["+$_.Name +"]"

             }

            else

            {

             $Name =$Name +","+"["+$_.Name +"]"

            }

   

            if($Values -eq "")

             {

              $Values =$Values +"'"+$_.Value.Replace("'","''")+"'" 

             }

            else

            {

             $Values =$Values +",'"+$_.Value.Replace("'","''")+"'"

            }

   

            }

            

            $QueryInsert = "Insert into ["+$tree.Name.Replace(".csv","").Replace(".","_")+"] "

            $QueryInsert=$QueryInsert + "( "+ $Name + " )";

            $QueryInsert=$QueryInsert +" Values ("+ $Values +")";

         

           $cmdNewInsert = new-object system.data.sqlclient.sqlcommand( $QueryInsert, $cn2);

           $cn2.Open();

            $cmdNewInsert.ExecuteNonQuery();

            $cn2.Close() 

        }

      }

        while( $i -lt $data.length)

    {

    foreach ($dt in $data[$i])

        {

        $Name=""

        $Values=""

             $dt.psobject.Properties | foreach { 

             if($Name -eq "")

             {

              $Name =$Name +"["+$_.Name +"]"

             }

            else

            {

             $Name =$Name +","+"["+$_.Name +"]"

            }


            if($Values -eq "")

             {

              $Values =$Values +"'"+$_.Value.Replace("'","''")+"'" 

             }

            else

            {

             $Values =$Values +",'"+$_.Value.Replace("'","''")+"'"

            }


            }

            

            $QueryInsert = "Insert into ["+$tree.Name.Replace(".csv","").Replace(".","_")+"] "

            $QueryInsert=$QueryInsert + "( "+ $Name + " )";

            $QueryInsert=$QueryInsert +" Values ("+ $Values +")";

          $QueryInsert

           $cmdNewInsert = new-object system.data.sqlclient.sqlcommand( $QueryInsert, $cn2);

           $cn2.Open();

            $cmdNewInsert.ExecuteNonQuery();

            $cn2.Close() 

        }

        $i =$i +1

    }

    

    }

    }

 


}


CSVUpload -serverName 'RAJ-PC\RINDLA' `

            -dbCheck 'Sales_Info' `

            -pth 'C:\\PSScript\\RINDLA\\'




==================================================================================================================================================

PSScript 2:

The below mentioned PowerShell script is used to load the same CSV file structure of all files into SQL Server at a time. 

Note: Before executing this script need to check the SQL Server Instance name, Database name, Table name, and finally path of the CSV files.

#Read CSV file 

#open connection to database using bulkcopy

#convert array to data table

#bulkload data into table

#note: column sequence and data types should match


function Get-Type 

    param($type) 

 

$types = @( 

'System.Boolean', 

'System.Byte[]', 

'System.Byte', 

'System.Char', 

'System.nvarchar',

'System.Datetime', 

'System.Decimal', 

'System.Double', 

'System.Guid', 

'System.Int16', 

'System.Int32', 

'System.Int64', 

'System.Single', 

'System.UInt16', 

'System.UInt32', 

'System.UInt64') 

 

    if ( $types -contains $type ) { 

        Write-Output "$type" 

    } 

    else { 

        Write-Output 'System.String' 

         

    } 

}



function Out-DataTable 

    [CmdletBinding()] 

    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject) 

 

    Begin 

    { 

        $dt = new-object Data.datatable   

        $First = $true  

    } 

    Process 

    { 

        foreach ($object in $InputObject) 

        { 

            $DR = $DT.NewRow()   

            foreach($property in $object.PsObject.get_properties()) 

            {   

                if ($first) 

                {   

                    $Col =  new-object Data.DataColumn   

                    $Col.ColumnName = $property.Name.ToString()   

                    if ($property.value) 

                    { 

                        if ($property.value -isnot [System.DBNull]) { 

                            $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 

                         } 

                    } 

                    $DT.Columns.Add($Col) 

                }   

                if ($property.Gettype().IsArray) { 

                    $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 

                }   

               else { 

                    $DR.Item($property.Name) = $property.value 

                } 

            }   

            $DT.Rows.Add($DR)   

            $First = $false 

        } 

    }  

      

    End 

    { 

        Write-Output @(,($dt)) 

    } 

 

} #Out-DataTable

$fileExtension = '*csv*.*'#you can use wildcards here for name and for extension

$searchinfolder = 'C:\PSScript\RINDLA'

$FileNames=Get-ChildItem -Path $searchinfolder -Filter $fileExtension -Recurse | %{$_.FullName}

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($FileNames)

foreach($Files in $FileNames)

{

$file=$Files


$cn = new-object system.data.SqlClient.SQLConnection("Data Source="+'RAJ-PC\RINDLA'+";Integrated Security=SSPI;Initial Catalog="+'Estimation_Details'+"");

$cn.Open()

$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn

$bc.BatchSize = 10000;

$bc.BulkCopyTimeout = 1000

$bc.DestinationTableName = "All_Region_Sales"

#$bc.DestinationTableName = $fileName


$data = Import-Csv $file | Out-DataTable


$bc.WriteToServer($data)


}



==================================================================================================================================================

PSScript 3:

The below mentioned PowerShell script is used to Extract the query result from SQL Server to CSV files

Note: Before executing this script need to check the SQL Server Instance name, Database name, Query, and finally path of the CSV files.


[string] $Server= "RAJ-PC\RINDLA"

[string] $Database = "AdventureWorks"

[string] $UserSqlQuery= $("SELECT * FROM sys.tables")



# declaration not necessary, but good practice

$resultsDataTable = New-Object System.Data.DataTable

$resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery 


# executes a query and populates the $datatable with the data

function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {

    $Datatable = New-Object System.Data.DataTable

    

    $Connection = New-Object System.Data.SQLClient.SQLConnection

    $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"

    $Connection.Open()

    $Command = New-Object System.Data.SQLClient.SQLCommand

    $Command.Connection = $Connection

    $Command.CommandText = $SQLQuery

    $Reader = $Command.ExecuteReader()

    $Datatable.Load($Reader)

    $Connection.Close()

    

    return $Datatable

}


#validate we got data

Write-Host ("The table contains: " + $resultsDataTable.Rows.Count + " rows")

$resultsDataTable | export-Csv -Path "D:\RAJ\Queryoutput.csv"


==================================================================================================================================================

PSScript 4:

The below mentioned PowerShell script is used to Extract the query result from multiple SQL Server Instances to CSV files

Note: Before executing this script need to check the SQL Server Instance name list, SQL Scripts path, and finally output path of the CSV files.


Push-location

import-module sqlps

Pop-Location


$OutputPath = "D:\RAJ\Output"

$SQLScriptsPath = 'D:\RAJ\SE'

$ServerList = "D:\RAJ\ServerNames.txt"


Get-ChildItem $SqlScriptsPath -Filter *.sql | Sort-Object Name | ForEach-Object {

  $File = $_

  Get-Content $ServerList | ForEach-Object {

    $Server = $_


    # A directory and file based on the server name from the text file

    New-Item "$OutputPath\$Server" -ItemType Directory -Force | Out-Null

    $OutputFile = "$OutputPath\$Server\$($File.BaseName).csv"

    $OutputFile 

    try {

      Invoke-SqlCmd –ServerInstance $Server -QueryTimeout 0 -InputFile $File.FullName |

        Export-Csv $OutputFile -NoTypeInformation

    } catch {

      Write-Host $_.Exception.Message -ForegroundColor Red

    }

  }

}




==================================================================================================================================================

PSScript 4:

The below mentioned PowerShell script is used to save the result of SQL Query output into CSV files.

Note: Before executing this script need to check the SQL Server Instance name list, Output path(CSV), and SQL Scripts path.


Push-location

import-module sqlps

Pop-Location


$OutputPath = "E:\DBScripts\Raj\Output"

$SQLScriptsPath = 'E:\DBScripts\Raj\Scripts'

$ServerList = "E:\DBScripts\Raj\ServerNames.txt"


Get-ChildItem $SqlScriptsPath -Filter *.sql | Sort-Object Name | ForEach-Object {

  $File = $_

  Get-Content $ServerList | ForEach-Object {

    $Server = $_


    # A directory and file based on the server name from the text file

    New-Item "$OutputPath\$Server" -ItemType Directory -Force | Out-Null

    $OutputFile = "$OutputPath\$Server\$($File.BaseName).csv"

    $OutputFile 

    try {

      Invoke-SqlCmd –ServerInstance $Server -InputFile $File.FullName |

        Export-Csv $OutputFile -NoTypeInformation

    } catch {

      Write-Host $_.Exception.Message -ForegroundColor Red

    }

  }

}


==================================================================================================================================================