Prepare Insert Statement Using Powershell

How to prepare insert script using powershell script from SQL Server Datatable

May 9, 2019 6 min read PowerShell, Microsoft SQL

In my previous post I have explained, how we can connect to SQL Server database and execute stored procedure from the powershell. If you missed it here is the link : Execute Storedprocedure in Powershell

Lets take the same example where we used for Create Insert Script From MS Sql Table

The create script for the table in the example is


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable1](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Age] [int] NULL,
	[Address] [nvarchar](255) NULL,
	[City] [nvarchar](50) NULL,
 CONSTRAINT [PK_MyTable1] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO

We are going to convert the table row data to insert script using a stored procedure. Each data will be converted to string based on its datatype.

For that I'm going to use a function named “FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT” which returns a string which converts the data to string based on the column datatype.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Gobikannan P
-- Create date: 01 May 2019
-- Description: To Get the column string for insert script generation
-- =============================================
CREATE FUNCTION [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] 
(
	@ColumnName sysname, @DataType varchar(128), @IncludeTimestamp bit = 0
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @RetVal NVARCHAR(MAX)
	SELECT @RetVal =
CASE
WHEN @DataType IN ('char','varchar','nchar','nvarchar','sysname') 
			THEN 'COALESCE('''''''' + REPLACE(RTRIM(' + @ColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @DataType IN ('datetime','smalldatetime') 
			THEN 'COALESCE('''''''' + RTRIM(CONVERT(char,' + @ColumnName + ',109))+'''''''',''NULL'')'
WHEN @DataType IN ('uniqueidentifier') 
			THEN 'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @ColumnName + ')),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @DataType IN ('text','ntext') 
			THEN 'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @ColumnName + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @DataType IN ('binary','varbinary') 
			THEN 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @ColumnName + '))),''NULL'')'
WHEN @DataType IN ('timestamp','rowversion') 
			THEN
CASE
WHEN @IncludeTimestamp = 0
THEN '''DEFAULT'''
ELSE 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @ColumnName + '))),''NULL'')'
END
WHEN @DataType IN ('float','real','money','smallmoney')
			THEN 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @ColumnName  + ',2)' + ')),''NULL'')'
ELSE  'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @ColumnName  + ')' + ')),''NULL'')'
END   + '+' +  ''',''' + ' + '
RETURN @RetVal
END
GO

Below stored procedure uses the above function to construct insert statement from column. If you have any additional columns which you want to insert as NULL then need to use a constant value instead of calling the above function.

In the blow stored procedure script ‘MaritalStatus’ column is added with NULL value.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Gobikannan Ponmalai
-- Create date: 01 May 2019
-- Description:	To create insert script from database table
-- =============================================
CREATE PROCEDURE [dbo].[SP_INSERTSCRIPT_FROM_DATATABLE]
	@AgeLimit int
AS
BEGIN
DECLARE @InsertStatement varchar(max),@INCLUDENULLCOLUMN varchar(100) = '''NULL'''+ '+' +  ''',''' + ' + ', @ColScriptConstructed NVARCHAR(max) = ''
DECLARE @ResultInsertQuery AS TABLE(InsertQuery NVARCHAR(MAX) NOT NULL)

	IF NOT EXISTS (SELECT * FROM MyTable1)
	BEGIN
SELECT  '-- Info : There is no row found in the Table to convert insert script' AS [InsertResult]
		RETURN
END
SET @InsertStatement = 'INSERT INTO [dbo].[MyTable2]([Id],[Name],[Age],[MaritalStatus],[Address],[City]) VALUES '
SET @ColScriptConstructed +=  [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] ('Id', 'int', 0) -- [Id]
	SET @ColScriptConstructed +=  [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] ('Name', 'nvarchar', 0) -- [Name]
	SET @ColScriptConstructed +=  [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] ('Age', 'int', 0) -- [Age]	
	SET @ColScriptConstructed +=  @INCLUDENULLCOLUMN  -- [MaritalStatus]
	SET @ColScriptConstructed +=  [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] ('Address', 'nvarchar', 0) -- [Address]
	SET @ColScriptConstructed +=  [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] ('City', 'varchar', 0) -- [City]

SET @ColScriptConstructed = LEFT(@ColScriptConstructed,len(@ColScriptConstructed) - 6)
	-- SELECT @ColScriptConstructed

SET @ColScriptConstructed = 'SELECT ' +
' ''(''+ ' +  @ColScriptConstructed + '+'')''' + ' ' +
+'+ '''' AS InsQueryVal FROM [dbo].[MyTable1] WHERE Age > @Age' 
	print @ColScriptConstructed

	INSERT INTO @ResultInsertQuery(InsertQuery)
	EXEC sp_executesql @ColScriptConstructed,N'@Age int', @AgeLimit

	SELECT @InsertStatement + CHAR(13)+CHAR(10) + InsertQuery + ';' + CHAR(13)+CHAR(10) AS [InsertResult] FROM @ResultInsertQuery
END
GO

Now you can use this stored procedure to get the datatable to insert scripts in each rows. As we've seen earlier, we could call this stored procedure usnig powershell script as like below

# To connect to SQL Server and execute stored procedure using powershell"

$databaseServerName = "localhost"
$databaseName = "databaseName"
$databaseUsername = 'userName'
$dbUserPassword = '<PasswordHere>'
$connectionTO = 120

# you can use different option to do the parameter value processing

$ageLimit = 31
# Variable to hold current file directory

#$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

$scriptPath = $PSScriptRoot
$dateInFormat = (Get-Date).ToString("yyyyMMdd_HHmmss")

$DatabaseConnectionString = "Server=$databaseServerName; Database=$databaseName; User Id=$databaseUsername; Password=$dbUserPassword;Connection Timeout=$connectionTO;"
try {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $DatabaseConnectionString

    $SqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $SqlCommand.Connection = $SqlConnection
    $SqlCommand.CommandText = "SP_INSERTSCRIPT_FROM_DATATABLE"
    $SqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
    $SqlCommand.Parameters.AddWithValue("@AgeLimit", $ageLimit)

    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCommand)
    $SqlAdapter.Fill($DataSet)
    if ($conn.State -eq [System.Data.ConnectionState]::Open) {
        $SqlConnection.Close()
    }
    if (($null -ne $DataSet) -and ($DataSet.Tables.Count -gt 0)) {
        $resultTable = $DataSet.Tables[0]
        if ($resultTable.Rows.Count -gt 0) {
            $OutPutFilePath = "$scriptPath\SQLInsertQuery_Result_${dateInFormat}.sql"
            Write-Host "Result Row Count :" $resultTable.Rows.Count
            "-- Insert script created on  ${dateInFormat}" | Out-File $OutPutFilePath  -Encoding ascii
            foreach ($row in $resultTable.Rows) {
                $row["InsertResult"] | Out-File $OutPutFilePath -Append -Encoding ascii
            }
        }
        else {
            Write-Host "No rows found"
        }
    }
   
}
catch {
    Write-Error $_.Exception.Message 
}

The result will be as like below

-- Insert script created on  20190501_231339
INSERT INTO [dbo].[MyTable2]([Id],[Name],[Age],[MaritalStatus],[Address],[City]) VALUES 
(1,'Will Smith',50,NULL,'Philadelphia, Pennsylvania, USA','Philadelphia');

INSERT INTO [dbo].[MyTable2]([Id],[Name],[Age],[MaritalStatus],[Address],[City]) VALUES 
(2,'Leonardo DiCaprio',44,NULL,'Hollywood, Los Angeles, California, USA','Los Angeles');

INSERT INTO [dbo].[MyTable2]([Id],[Name],[Age],[MaritalStatus],[Address],[City]) VALUES 
(3,'Ben Kingsley',75,NULL,'Scarborough, Yorkshire, England, UK','Scarborough');

You can create stored procedure as like below to create multi value insert script.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Gobikannan Ponmalai
-- Create date: 01 May 2019
-- Description:	To create insert script from database table
-- =============================================
CREATE PROCEDURE [dbo].[SP_MULTI_INSERTSCRIPT_FROM_DATATABLE]
	@AgeLimit int
AS
BEGIN
DECLARE @InsertStatement varchar(max),@INCLUDENULLCOLUMN varchar(100) = '''NULL'''+ '+' +  ''',''' + ' + ', @ColScriptConstructed NVARCHAR(max) = ''
DECLARE @ResultInsertQuery AS TABLE(InsertQuery NVARCHAR(MAX) NOT NULL)

	IF NOT EXISTS (SELECT * FROM MyTable1)
	BEGIN
SELECT  '-- Info : There is no row found in the Table to convert insert script' AS [InsertResult]
		RETURN
END
SET @InsertStatement = 'INSERT INTO [dbo].[MyTable2]([Id],[Name],[Age],[MaritalStatus],[Address],[City]) VALUES '
SET @ColScriptConstructed +=  [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] ('Id', 'int', 0) -- [Id]
	SET @ColScriptConstructed +=  [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] ('Name', 'nvarchar', 0) -- [Name]
	SET @ColScriptConstructed +=  [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] ('Age', 'int', 0) -- [Age]	
	SET @ColScriptConstructed +=  @INCLUDENULLCOLUMN  -- [MaritalStatus]
	SET @ColScriptConstructed +=  [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] ('Address', 'nvarchar', 0) -- [Address]
	SET @ColScriptConstructed +=  [dbo].[FN_FORMAT_COLUMN_FOR_INSERT_SCRIPT_STMT] ('City', 'varchar', 0) -- [City]

SET @ColScriptConstructed = LEFT(@ColScriptConstructed,len(@ColScriptConstructed) - 6)
	-- SELECT @ColScriptConstructed

SET @ColScriptConstructed = 'SELECT ' +
' ''(''+ ' +  @ColScriptConstructed + '+'')''' + ' ' +
+'+ '''' AS InsQueryVal FROM [dbo].[MyTable1] WHERE Age > @Age' 
	print @ColScriptConstructed

	INSERT INTO @ResultInsertQuery(InsertQuery)
	EXEC sp_executesql @ColScriptConstructed,N'@Age int', @AgeLimit

	SELECT @InsertStatement + CHAR(13)+CHAR(10) + STRING_AGG(InsertQuery, ',' + CHAR(13)+CHAR(10)) AS [InsertResult] FROM @ResultInsertQuery

END
GO

The result will be as like below

-- Insert script created on  20190501_232250
INSERT INTO [dbo].[MyTable2]([Id],[Name],[Age],[MaritalStatus],[Address],[City]) VALUES 
(1,'Will Smith',50,NULL,'Philadelphia, Pennsylvania, USA','Philadelphia'),
(2,'Leonardo DiCaprio',44,NULL,'Hollywood, Los Angeles, California, USA','Los Angeles'),
(3,'Ben Kingsley',75,NULL,'Scarborough, Yorkshire, England, UK','Scarborough')

PowerShell MS SQL

Prepare Insert Statement Using Powershell

Gobikannan P

Related