Mass import into MS SQL database script with Powershell

Mass import into MS SQL database script with Powershell

While I've been testing failover cluster of Microsoft SQL Server 2012 (more details: Step-by-step deploying AlwaysOn on MS SQL 2012) I was needed to generate mass import of some data into database.

As soon as I'm very bad in T-SQL, I decided to write script on Powershell for this task.

We will need:

  • Database itself
  • Table in it for testing purposes

You should already have database. It's better to create new empty database, now we need to create a table. I created it manually in Designer in SQL Server Management Studio.

Mass import into MS SQL database script with Powershell

  • GUID - key field for data
  • TIMESTAMP - timestamp :)

Here is the script. It accept two mandatory parameters: total working time and number of INSERT queries per second.

param(	[Parameter(Mandatory=$true)][int] $Duration, 
		[Parameter(Mandatory=$true)][int] $StringsPerSecond
	 );


$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=ae1-tst-listene.domain.local; Initial Catalog=DAD_TST; Integrated Security=SSPI");
$conn.open();
$cmd = $conn.createcommand();

$start_time = Get-Date;
$end_time = $start_time.AddMinutes($Duration);
$current_time = Get-Date;
$counter = 0;

while ($end_time -gt $iteration_end_time) {
	$iteration_start_time = Get-Date;
	$guid = [guid]::NewGuid();
	
	if ($iteration_start_time.DateTime -gt $current_time.DateTime) {
		$counter = 0;
		echo $current_time.DateTime;
	}
	
	if ($counter -lt $StringsPerSecond -and $iteration_end_time.DateTime -eq $iteration_start_time.DateTime) {
		
		if ($conn.State -eq "Closed") {
			echo "Re-Opening Connection !!!";
			try {
				$conn.open();
			} catch {
				echo "Re-connection error !!!";
			}
		}
		$current_time_string = Get-Date -Format "yyyyMMddHHmmss";
		$current_time = Get-Date;
		$cmd.commandtext = "INSERT [dad_tst] (GUID, TIMESTAMP) VALUES ('$($guid)', $current_time_string)";
		try {
			$result = $cmd.executenonquery();
		} catch {
			echo "Error sending a query !!!";
		}
		$counter++;
	}
	
	$iteration_end_time = Get-Date;
}

$conn.close();

It's better to run the script from computer which is closer to the server, as network latency will slow down performance greatly.

As the result you will get something like this:

Mass import into MS SQL database script with Powershell

 

script (en), powershell (en), ms sql server (en)

  • Hits: 3771
Add comment

Related Articles