Invoke-SQLiteBulkCopy
SYNOPSIS
Use a SQLite transaction to quickly insert data
SYNTAX
Datasource (Default)
Invoke-SQLiteBulkCopy [-DataTable] <DataTable> [-DataSource] <String> [-Table] <String>
[[-ConflictClause] <String>] [-NotifyAfter <Int32>] [-Force] [-QueryTimeout <Int32>] [-WhatIf] [-Confirm]
[<CommonParameters>]
Connection
Invoke-SQLiteBulkCopy [-DataTable] <DataTable> [-SQLiteConnection] <SQLiteConnection> [-Table] <String>
[[-ConflictClause] <String>] [-NotifyAfter <Int32>] [-Force] [-QueryTimeout <Int32>] [-WhatIf] [-Confirm]
[<CommonParameters>]
DESCRIPTION
Use a SQLite transaction to quickly insert data. If we run into any errors, we roll back the transaction.
The data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.
EXAMPLES
EXAMPLE 1
#
#Create a table
Invoke-SqliteQuery -DataSource "C:\Names.SQLite" -Query "CREATE TABLE NAMES (
fullname VARCHAR(20) PRIMARY KEY,
surname TEXT,
givenname TEXT,
BirthDate DATETIME)"
Build up some fake data to bulk insert, convert it to a datatable
$DataTable = 1..10000 | %{
\[pscustomobject\]@{
fullname = "Name $_"
surname = "Name"
givenname = "$_"
BirthDate = (Get-Date).Adddays(-$_)
}
} | Out-DataTable
Copy the data in within a single transaction (SQLite is faster this way)
Invoke-SQLiteBulkCopy -DataTable $DataTable -DataSource $Database -Table Names -NotifyAfter 1000 -ConflictClause Ignore -Verbose
PARAMETERS
-DataTable
{{ Fill DataTable Description }}
Type: System.Data.DataTable
Parameter Sets: (All)
Aliases:
Required: True
Position: 1
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
-DataSource
Path to one ore more SQLite data sources to query
Type: System.String
Parameter Sets: Datasource
Aliases: Path, File, FullName, Database
Required: True
Position: 2
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
-SQLiteConnection
An existing SQLiteConnection to use. We do not close this connection upon completed query.
Type: System.Data.SQLite.SQLiteConnection
Parameter Sets: Connection
Aliases: Connection, Conn
Required: True
Position: 2
Default value: None
Accept pipeline input: True (ByPropertyName)
Accept wildcard characters: False
-Table
{{ Fill Table Description }}
Type: System.String
Parameter Sets: (All)
Aliases:
Required: True
Position: 3
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
-ConflictClause
The conflict clause to use in case a conflict occurs during insert. Valid values: Rollback, Abort, Fail, Ignore, Replace
See https://www.sqlite.org/lang_conflict.html for more details
Type: System.String
Parameter Sets: (All)
Aliases:
Required: False
Position: 4
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
-NotifyAfter
The number of rows to fire the notification event after transferring. 0 means don't notify. Notifications hit the verbose stream (use -verbose to see them)
Type: System.Int32
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False
-Force
If specified, skip the confirm prompt
Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False
-QueryTimeout
Specifies the number of seconds before the queries time out.
Type: System.Int32
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: 600
Accept pipeline input: False
Accept wildcard characters: False
-WhatIf
Shows what would happen if the cmdlet runs. The cmdlet is not run.
Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases: wi
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
-Confirm
Prompts you for confirmation before running the cmdlet.
Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases: cf
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
CommonParameters
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.
INPUTS
System.Data.DataTable
OUTPUTS
None
Produces no output
NOTES
This function borrows from: Chad Miller's Write-Datatable jbs534's Invoke-SQLBulkCopy Mike Shepard's Invoke-BulkCopy from SQLPSX