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 195 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 2 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
      Online 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…

    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