Manual
Search…
⌃K

Invoke-SQLiteBulkCopy



Invoke-SQLiteBulkCopy

SYNOPSIS

Use a SQLite transaction to quickly insert data

SYNTAX

Invoke-SQLiteBulkCopy [-DataTable] <DataTable> [-DataSource] <String> [-Table] <String> [[-ConflictClause] <String>] [-NotifyAfter <Int32>] [-Force] [-QueryTimeout <Int32>] [-WhatIf] [-Confirm]
[<CommonParameters>]
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

-DataTable <DataTable>
Required? true
Position? 1
Default value
Accept pipeline input? false
Accept wildcard characters? false

DataSource

-DataSource <String>
Path to one ore more SQLite data sources to query
Required? true
Position? 2
Default value
Accept pipeline input? false
Accept wildcard characters? false

SQLiteConnection

-SQLiteConnection <SQLiteConnection>
An existing SQLiteConnection to use. We do not close this connection upon completed query.
Required? true
Position? 2
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false

Table

-Table <String>
Required? true
Position? 3
Default value
Accept pipeline input? false
Accept wildcard characters? false

ConflictClause

-ConflictClause <String>
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
Required? false
Position? 4
Default value
Accept pipeline input? false
Accept wildcard characters? false

NotifyAfter

-NotifyAfter <Int32>
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)
Required? false
Position? named
Default value 0
Accept pipeline input? false
Accept wildcard characters? false

Force

-Force [<SwitchParameter>]
If specified, skip the confirm prompt
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false

QueryTimeout

-QueryTimeout <Int32>
Specifies the number of seconds before the queries time out.
Required? false
Position? named
Default value 600
Accept pipeline input? false
Accept wildcard characters? false

WhatIf

-WhatIf [<SwitchParameter>]
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

Confirm

-Confirm [<SwitchParameter>]
Required? false
Position? named
Default value
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.