Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise PowerShell Extensions Forum Performance monitor - Merge SQLite databases if running from separate regions - Just Sharing
    • Sign In

    • State Not Answered
    • Replies 1 reply
    • Subscribers 66 subscribers
    • Views 229 views
    • Users 0 members are here
    • powershell
    • Performance
    • ProjectWise
    • SQL

    Performance monitor - Merge SQLite databases if running from separate regions - Just Sharing

    Chris Spencer
    Offline Chris Spencer 3 months ago

    Maybe someone has a PowerShell script to do the same thing or a neater way? (Merging multiple sqlite databases to one - Machines were from different regions with no access to a central db) For this I required sqlite3.exe from the sqlite tools bundle and piped the SQL

    --Bentley Performance database merge to one for sqlite databases.
    --Usage from command line: sqlite3.exe global.sqlite < MergeSQL.sql
    --Where this file is MergeSQL.sql and global.sqlite is a copy of any db designated as master global
    
    --Empty tables
    delete from PWPerfEnvironment;
    delete from PWPerfFolder;
    delete from PWPerfGeneral;
    delete from PWPerfUser;
    delete from PWPerfDocument;
    
    --Merge in database 1
    attach 'C:\path-to-db1\PW_Perf_db1.sqlite' as toMerge;           
    BEGIN; 
    insert into PWPerfDocument select * from toMerge.PWPerfDocument;
    insert into PWPerfEnvironment select * from toMerge.PWPerfEnvironment; 
    insert into PWPerfFolder select * from toMerge.PWPerfFolder; 
    insert into PWPerfGeneral select * from toMerge.PWPerfGeneral; 
    insert into PWPerfUser select * from toMerge.PWPerfUser; 
    COMMIT; 
    detach toMerge;
    
    --Merge in database 2 and so on....
    attach 'C:\path-to-db2\PW_Perf_db2.sqlite' as toMerge;           
    BEGIN; 
    insert into PWPerfDocument select * from toMerge.PWPerfDocument;
    insert into PWPerfEnvironment select * from toMerge.PWPerfEnvironment; 
    insert into PWPerfFolder select * from toMerge.PWPerfFolder; 
    insert into PWPerfGeneral select * from toMerge.PWPerfGeneral; 
    insert into PWPerfUser select * from toMerge.PWPerfUser; 
    COMMIT; 
    detach toMerge;

    • Sign in to reply
    • Cancel

    Top Replies

    • Kevin van Haaren
      Offline Kevin van Haaren Wed, Jun 28 2023 10:28 AM +1
      I use the SimplySQL powershell module to work with SQLite from powershell but I've never tried to do any table merges from 2 different datasources with it. GitHub - mithrandyr/SimplySql: PowerShell module…
    Parents
    • Kevin van Haaren
      0 Offline Kevin van Haaren Wed, Jun 28 2023 10:28 AM

      I use the SimplySQL powershell module to work with SQLite from powershell but I've never tried to do any table merges from 2 different datasources with it.

      GitHub - mithrandyr/SimplySql: PowerShell module for querying various SQL databases

      The Invoke-SQLBulkCopy might make it easier:

      NAME
          Invoke-SqlBulkCopy
          
      SYNOPSIS
          Executes a bulk copy between two connections.
          
          
      SYNTAX
          Invoke-SqlBulkCopy [-SourceConnectionName <String>] [-DestinationConnectionName <String>] -SourceTable <String> 
          [-DestinationTable <String>] [-ColumnMap <Hashtable>] [-BatchSize <Int32>] [-BatchTimeout <Int32>] [-Notify] [-NotifyAction 
          <ScriptBlock>] [<CommonParameters>]
          
          Invoke-SqlBulkCopy [-SourceConnectionName <String>] [-DestinationConnectionName <String>] -SourceQuery <String[]> 
          [-SourceParameters <Hashtable>] -DestinationTable <String> [-ColumnMap <Hashtable>] [-BatchSize <Int32>] [-BatchTimeout <Int32>] 
          [-Notify] [-NotifyAction <ScriptBlock>] [<CommonParameters>]
          
          
      DESCRIPTION
          Executes a bulk copy operation between two connections.  This is highly
          optimized if the destination has a managed bulkcopy implemenation, otherwise
          it is only generally optimized.  For example, SQL Server has a bulk copy
          class (SqlBulkCopy) that is easily implemented and provides an efficient
          means of inserting data into SQL Server.
          
          The default implemenation, if the provider does not provider a managed 
          bulk copy mechanism is to prepare the sql insert, and wrap multiple inserts
          into a single transaction (batching).  This provides a significant
          performance improvement over looping with Invoke-SqlUpdate.
          
          CONSIDERATIONS
          * You must specify either a SourceConnectionName or DestinationConnectionName,
              whichever one is not specified will use 'default', not specifying either
              will cause an error.     
          * If you donâ?Tt specify DestinationTable, it will use SourceTable; however
              DestinationTable is required if you use SourceQuery.
          * If you specify ColumnMap and Source Table, then the select against the
              SourceConnection will be limited to the columns you specified in ColumnMap.
          
          Returns number of rows copied.
          
      
      PARAMETERS
          -SourceConnectionName <String>
              User defined name for connection where data will be queried from.
              
          -DestinationConnectionName <String>
              User defined name for connection where data will be inserted to.
              
          -SourceTable <String>
              The name of the table in the source connection.
              
          -SourceQuery <String[]>
              The query to determine the source data, instead of specifying a table.
              
          -SourceParameters <Hashtable>
              Parameters needed for the source query.
              
          -DestinationTable <String>
              The name of the table to write to in the destination connection.
              If not specified, will be taken from SourceTable parameter.
              
          -ColumnMap <Hashtable>
              Key is the column name in the source connection.
              Value is the column name in the destination connection.
              
          -BatchSize <Int32>
              How many inserts are batched together at one time.
              
          -BatchTimeout <Int32>
              How long, in seconds, that each batch can take.
              Defaults to the command timeout for the source connection.
              
          -Notify [<SwitchParameter>]
              If present, as each batch completes a progress notification will be
              generated with the total number of rows inserted so far.
              
          -NotifyAction <ScriptBlock>
              If specified, then on the completion of each batch, this action will be invoked.
              The first argument will have the rows completed so far, either use $args[0]
              or specify a param block.
              
          <CommonParameters>
              This cmdlet supports the common parameters: Verbose, Debug,
              ErrorAction, ErrorVariable, WarningAction, WarningVariable,
              OutBuffer, PipelineVariable, and OutVariable. For more information, see 
              about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216). 
          
      REMARKS
          To see the examples, type: "get-help Invoke-SqlBulkCopy -examples".
          For more information, type: "get-help Invoke-SqlBulkCopy -detailed".
          For technical information, type: "get-help Invoke-SqlBulkCopy -full".
      

       

      • Cancel
      • Vote Up +1 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    Reply
    • Kevin van Haaren
      0 Offline Kevin van Haaren Wed, Jun 28 2023 10:28 AM

      I use the SimplySQL powershell module to work with SQLite from powershell but I've never tried to do any table merges from 2 different datasources with it.

      GitHub - mithrandyr/SimplySql: PowerShell module for querying various SQL databases

      The Invoke-SQLBulkCopy might make it easier:

      NAME
          Invoke-SqlBulkCopy
          
      SYNOPSIS
          Executes a bulk copy between two connections.
          
          
      SYNTAX
          Invoke-SqlBulkCopy [-SourceConnectionName <String>] [-DestinationConnectionName <String>] -SourceTable <String> 
          [-DestinationTable <String>] [-ColumnMap <Hashtable>] [-BatchSize <Int32>] [-BatchTimeout <Int32>] [-Notify] [-NotifyAction 
          <ScriptBlock>] [<CommonParameters>]
          
          Invoke-SqlBulkCopy [-SourceConnectionName <String>] [-DestinationConnectionName <String>] -SourceQuery <String[]> 
          [-SourceParameters <Hashtable>] -DestinationTable <String> [-ColumnMap <Hashtable>] [-BatchSize <Int32>] [-BatchTimeout <Int32>] 
          [-Notify] [-NotifyAction <ScriptBlock>] [<CommonParameters>]
          
          
      DESCRIPTION
          Executes a bulk copy operation between two connections.  This is highly
          optimized if the destination has a managed bulkcopy implemenation, otherwise
          it is only generally optimized.  For example, SQL Server has a bulk copy
          class (SqlBulkCopy) that is easily implemented and provides an efficient
          means of inserting data into SQL Server.
          
          The default implemenation, if the provider does not provider a managed 
          bulk copy mechanism is to prepare the sql insert, and wrap multiple inserts
          into a single transaction (batching).  This provides a significant
          performance improvement over looping with Invoke-SqlUpdate.
          
          CONSIDERATIONS
          * You must specify either a SourceConnectionName or DestinationConnectionName,
              whichever one is not specified will use 'default', not specifying either
              will cause an error.     
          * If you donâ?Tt specify DestinationTable, it will use SourceTable; however
              DestinationTable is required if you use SourceQuery.
          * If you specify ColumnMap and Source Table, then the select against the
              SourceConnection will be limited to the columns you specified in ColumnMap.
          
          Returns number of rows copied.
          
      
      PARAMETERS
          -SourceConnectionName <String>
              User defined name for connection where data will be queried from.
              
          -DestinationConnectionName <String>
              User defined name for connection where data will be inserted to.
              
          -SourceTable <String>
              The name of the table in the source connection.
              
          -SourceQuery <String[]>
              The query to determine the source data, instead of specifying a table.
              
          -SourceParameters <Hashtable>
              Parameters needed for the source query.
              
          -DestinationTable <String>
              The name of the table to write to in the destination connection.
              If not specified, will be taken from SourceTable parameter.
              
          -ColumnMap <Hashtable>
              Key is the column name in the source connection.
              Value is the column name in the destination connection.
              
          -BatchSize <Int32>
              How many inserts are batched together at one time.
              
          -BatchTimeout <Int32>
              How long, in seconds, that each batch can take.
              Defaults to the command timeout for the source connection.
              
          -Notify [<SwitchParameter>]
              If present, as each batch completes a progress notification will be
              generated with the total number of rows inserted so far.
              
          -NotifyAction <ScriptBlock>
              If specified, then on the completion of each batch, this action will be invoked.
              The first argument will have the rows completed so far, either use $args[0]
              or specify a param block.
              
          <CommonParameters>
              This cmdlet supports the common parameters: Verbose, Debug,
              ErrorAction, ErrorVariable, WarningAction, WarningVariable,
              OutBuffer, PipelineVariable, and OutVariable. For more information, see 
              about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216). 
          
      REMARKS
          To see the examples, type: "get-help Invoke-SqlBulkCopy -examples".
          For more information, type: "get-help Invoke-SqlBulkCopy -detailed".
          For technical information, type: "get-help Invoke-SqlBulkCopy -full".
      

       

      • Cancel
      • Vote Up +1 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    Children
    No Data

    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies