Как вставить строку после соответствия шаблону

Introduction

Steps for Data Manipulation and Insertion Using PowerShell

1. Load the .NET Assembly: We will start by loading the necessary .NET assembly that provides us with the required classes for database operations.
2. Create a DataTable Object: A DataTable object will be instantiated to mimic the structure of the SQL Server table where the data will be inserted.
3. Define the Schema: We’ll define the columns of our DataTable, which should match the schema of the target SQL Server table.
4. Generate Random Data: We’ll programmatically generate random data to populate our DataTable with rows that simulate the actual data.
5. Establish a Database Connection: A connection to the SQL Server database will be established using the necessary credentials and connection string.
6. Prepare for Bulk Insert: We will prepare a SqlBulkCopy object, which is optimized for bulk data operations and will handle the insertion of data from our DataTable to the SQL Server table.
7. Execute the Bulk Insert: Finally, we will execute the bulk insert operation, transferring all the data from the DataTable to the SQL Server table in an efficient manner.

Step 1: Loading the ADO.NET Assembly

First, we need to load the necessary .NET assembly which allows us to work with databases:

Add-Type -AssemblyName "System.Data"

Add-Type is a PowerShell cmdlet used to load .NET assemblies into your session, which is necessary for accessing classes that interact with SQL Server.

Step 2: Creating the DataTable

With the assembly loaded, we can now create a DataTable object:

$dataTable = New-Object System.Data.DataTable

The New-Object cmdlet is used here to create an instance of the DataTable class. DataTable is an in-memory representation of a single database table.

We then define the columns that our DataTable will have:

$dataTable.Columns.Add((New-Object System.Data.DataColumn 'ID',([int])))
$dataTable.Columns.Add((New-Object System.Data.DataColumn 'Value',([string])))

Each DataColumn object represents a column in the table, where ‘ID’ is of type int and ‘Value’ is of type string.

Step 3: Populating the DataTable

Next, we populate the DataTable with random data:

$random = New-Object System.Random
for ($i = 1; $i -le 10; $i++) { $row = $dataTable.NewRow() $row['ID'] = $i $row['Value'] = "RandomValue_" + $random.Next(1, 100) $dataTable.Rows.Add($row)
}

Here we’re using a for loop to create 10 rows of data. NewRow creates a new row that matches the schema of the DataTable. We fill the ‘ID’ column with a sequential number and the ‘Value’ column with a random number prefixed by a string.

Step 4: Establishing a SQL Server Connection

Now, we’ll establish a connection to the SQL Server:

$connectionString = "Server=myServerName;Database=myDatabaseName;Integrated Security=True;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString

The connection string contains the information needed to connect to the database. Integrated Security=True is used for Windows Authentication. Replace myServerName and myDatabaseName with your server and database name.

Step 5: Using SqlBulkCopy to Insert Data

Finally, we use SqlBulkCopy to insert the data from our DataTable into the SQL Server table:

$sqlBulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConnection)
$sqlBulkCopy.DestinationTableName = "myTableName"

SqlBulkCopy is a class that allows for efficient bulk operations. We specify the destination table name with $sqlBulkCopy.DestinationTableName.

To execute the bulk copy:

try { $sqlConnection.Open() $sqlBulkCopy.WriteToServer($dataTable) Write-Host "Data has been successfully exported to SQL Server table."
} catch { Write-Host "Error: $_"
} finally { $sqlConnection.Close()
}

Before we execute the script we need to make sure our destination table exists. Here is how you’d create our sample table using TSQL:

CREATE TABLE myTableName ( ID int NOT NULL PRIMARY KEY, Value nvarchar(255) NOT NULL
)

The script is provided in its entirety below for your convenience:

# Load ADO.NET assembly
Add-Type -AssemblyName "System.Data"
# Create a DataTable
$dataTable = New-Object System.Data.DataTable
$dataTable.Columns.Add((New-Object System.Data.DataColumn 'ID',([int])))
$dataTable.Columns.Add((New-Object System.Data.DataColumn 'Value',([string])))
# Populate DataTable with random data
$random = New-Object System.Random
for ($i = 1; $i -le 10; $i++) { $row = $dataTable.NewRow() $row['ID'] = $i $row['Value'] = "RandomValue_" + $random.Next(1, 100) $dataTable.Rows.Add($row)
}
# SQL Server connection string
$connectionString = "Server=myServerName;Database=myDatabaseName;Integrated Security=True;"
# Establish SQL Server connection
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString
# SQL Bulk Copy to insert data into the table
$sqlBulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConnection)
$sqlBulkCopy.DestinationTableName = "myTableName"
try { # Open SQL Server connection $sqlConnection.Open() # Write from the source to the destination $sqlBulkCopy.WriteToServer($dataTable) Write-Host "Data has been successfully exported to SQL Server table."
}
catch { Write-Host "Error: $_"
}
finally { # Close the SQL Server connection $sqlConnection.Close()
}

Cmdlets and Classes Used

• Add-Type: Loads a .NET assembly into the PowerShell session.
• New-Object: Instantiates a .NET class. Used to create the DataTable, DataColumn, SqlConnection, and SqlBulkCopy objects.
• System.Data.DataTable: Represents one table of in-memory data.
• System.Data.DataColumn: Defines the schema of a column in a DataTable.
• System.Random: A .NET class that generates random numbers.
• System.Data.SqlClient.SqlConnection: Establishes a connection to SQL Server.
• System.Data.SqlClient.SqlBulkCopy: Allows for the bulk transfer of data into SQL Server.

By understanding these concepts and cmdlets, you can manipulate data structures in PowerShell and interact with SQL Server to perform a variety of data operations, making your scripting tasks more efficient and powerful.

Conclusion

And that’s it! You’ve now learned how to create a DataTable in PowerShell, populate it with data, and insert that data into a SQL Server table with SqlBulkCopy. This process is particularly useful for automating the insertion of large volumes of data into a database, making your data processing tasks more efficient. We hope it also helps extend your understanding of PowerShell as well.