DbaClientX/Module/Examples/Example.CopyTableData.ps1 at master · EvotecIT/DbaClientX · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
param(
    [int] $RowCount = 100,
    [switch] $CopyToSqlServer,
    [string] $Server = $(if ($env:DBACLIENTX_SQLSERVER) { $env:DBACLIENTX_SQLSERVER } else { 'localhost' }),
    [string] $Database = $(if ($env:DBACLIENTX_SQLDATABASE) { $env:DBACLIENTX_SQLDATABASE } else { 'tempdb' }),
    [switch] $KeepFiles,
    [switch] $KeepSqlTable
)

# Use this example when you need a repeatable table-copy shape:
# SQLite source -> SQLite destination by default, or SQLite -> SQL Server
# with -CopyToSqlServer. It maps column names, drops a helper column, converts
# simple types, clears the destination, and verifies the copied row count.
# Example:
#   .\Example.CopyTableData.ps1 -RowCount 100
#   .\Example.CopyTableData.ps1 -CopyToSqlServer -Server localhost -Database tempdb -RowCount 100

Import-Module DbaClientX -Force

$temporaryPath = [System.IO.Path]::GetTempPath()
$sourceDatabase = Join-Path $temporaryPath ('DbaClientXCopySource-' + [guid]::NewGuid() + '.db')
$sqliteDestination = Join-Path $temporaryPath ('DbaClientXCopyDestination-' + [guid]::NewGuid() + '.db')
$sqlTable = 'dbo.DbaClientXCopy_' + ([guid]::NewGuid().ToString('N').Substring(0, 10))
$sqlConnectionString = "Server=$Server;Database=$Database;Encrypt=True;TrustServerCertificate=True;Integrated Security=True"

try {
    Invoke-DbaXSQLite -Database $sourceDatabase -Query @"
CREATE TABLE SourceRows
(
    Id INTEGER NOT NULL PRIMARY KEY,
    DisplayName TEXT NOT NULL,
    IsEnabled INTEGER NOT NULL,
    ScoreText TEXT NOT NULL,
    Helper TEXT
);
"@ -ErrorAction Stop | Out-Null

    Invoke-DbaXSQLite -Database $sqliteDestination -Query @"
CREATE TABLE DestinationRows
(
    Id INTEGER NOT NULL PRIMARY KEY,
    Name TEXT NOT NULL,
    IsEnabled INTEGER NOT NULL,
    ScoreValue INTEGER NOT NULL
);
"@ -ErrorAction Stop | Out-Null

    1..$RowCount | ForEach-Object {
        $enabled = $_ % 2
        Invoke-DbaXSQLite -Database $sourceDatabase -Query "INSERT INTO SourceRows (Id, DisplayName, IsEnabled, ScoreText, Helper) VALUES ($_, 'Row $_', $enabled, '$($_ * 10)', 'skip');" -ErrorAction Stop | Out-Null
    }

    $sqliteCopy = Copy-DbaXTableData `
        -SourceProvider SQLite `
        -SourceConnectionString "Data Source=$sourceDatabase" `
        -SourceTable SourceRows `
        -DestinationProvider SQLite `
        -DestinationConnectionString "Data Source=$sqliteDestination" `
        -DestinationTable DestinationRows `
        -OrderBy Id `
        -ColumnMap @{ DisplayName = 'Name'; ScoreText = 'ScoreValue' } `
        -ExcludeColumn Helper `
        -BooleanColumn IsEnabled `
        -Int32Column ScoreValue `
        -PageSize 1000 `
        -BatchSize 500 `
        -ClearDestination `
        -PassThru `
        -ErrorAction Stop

    $result = [ordered]@{
        SourceDatabase = $sourceDatabase
        SQLiteDestination = $sqliteDestination
        SQLiteCopiedRows = $sqliteCopy.CopiedRows
        SQLiteVerified = $sqliteCopy.Verified
    }

    if ($CopyToSqlServer) {
        Invoke-DbaXNonQuery -Server $Server -Database $Database -TrustServerCertificate -Query @"
CREATE TABLE $sqlTable
(
    Id int NOT NULL PRIMARY KEY,
    Name nvarchar(100) NOT NULL,
    IsEnabled bit NOT NULL,
    ScoreValue int NOT NULL
);
"@ -ErrorAction Stop | Out-Null

        $sqlCopy = Copy-DbaXTableData `
            -SourceProvider SQLite `
            -SourceConnectionString "Data Source=$sourceDatabase" `
            -SourceTable SourceRows `
            -DestinationProvider SqlServer `
            -DestinationConnectionString $sqlConnectionString `
            -DestinationTable $sqlTable `
            -OrderBy Id `
            -ColumnMap @{ DisplayName = 'Name'; ScoreText = 'ScoreValue' } `
            -ExcludeColumn Helper `
            -BooleanColumn IsEnabled `
            -Int32Column ScoreValue `
            -PageSize 1000 `
            -BatchSize 500 `
            -TableLock `
            -ClearDestination `
            -PassThru `
            -ErrorAction Stop

        $result['SqlServerTable'] = $sqlTable
        $result['SqlServerCopiedRows'] = $sqlCopy.CopiedRows
        $result['SqlServerVerified'] = $sqlCopy.Verified
    }

    [pscustomobject]$result
}
finally {
    if ($CopyToSqlServer -and -not $KeepSqlTable) {
        Invoke-DbaXNonQuery -Server $Server -Database $Database -TrustServerCertificate -Query "DROP TABLE IF EXISTS $sqlTable;" -ErrorAction SilentlyContinue | Out-Null
    }

    if (-not $KeepFiles) {
        foreach ($path in @($sourceDatabase, $sqliteDestination)) {
            if ($path -and (Test-Path -LiteralPath $path)) {
                Remove-Item -LiteralPath $path -Force
            }
        }
    }
}