SQLite User Forum

Moving data from SQLite3 to remote SQL Server
Login
Hierarchical

Moving data from SQLite3 to remote SQL Server

(1) By shwetha (shwethag) on 2022-07-20 15:59:51 [link] [source]

Hello: I have installed sqllite3 on Rasperry pi3 to store the data coming from a PLC. I would like to periodically move the data from SQllite3 to a remote SQL server without duplicating it. Could anyone please let me know what the best approach is?

Thank you, Shwetha.

(2) By Simon Slavin (slavin) on 2022-07-20 16:55:47 in reply to 1 [source]

Are you talking moving the data to a different computer, so that it can be examined using a copy of SQLite there ? Or moving the data to a computer that runs the Microsoft product "SQL Server" ?

If it's the first of those, a SQLite database is just a single file. Make sure nothing is changing the database, then do with it whatever you want: read it from another computer, put it on a network drive, send it via a file transfer system (SCP ?), or anything else. That's down to whatever your Raspberry Pi 3 can be set up to do.

The hard part is making sure nothing is changing the file while it's being copied to the server.

(3) By shwetha (shwethag) on 2022-07-20 17:11:17 in reply to 2 [link] [source]

Hello Thank you for your response. The data has to be moved from SQllite on the raspberry pi to a remote computer running a Microsoft SQL Server. Any ideas on how to do this?

(4) By Aask (AAsk1902) on 2022-07-20 18:03:31 in reply to 3 [link] [source]

EITHER Install the SQLite ODBC driver OR export the SQLite tables to CSV

Then

  1. Create your target SQL Server database.
  2. Launch SSMS (SQL SERVER MANAGEMENT STUDIO).
  3. In Object Explorer, right-click on the database you created.
  4. Select Tasks and then EIRHER Import Flat File (for CSV) OR Import Data (for importing tables)
  5. Follow the dialogue that pops up for each CSV or each table.

Alternatively, script the SQLite tables & data, edit the script to ensure compatibility with SQL Server's SQL dialect and run the scripts.

(5.1) By shwetha (shwethag) on 2022-07-20 18:08:14 edited from 5.0 in reply to 4 [link] [source]

Thank you. So, write the script to move data from SQLLite to CSV and from CSV to remote SQL Server, correct? Or, is it possible to write a python script to move directly from SQL lite to SQL server if SQL ODBC driver is installed? I am not a database expert. So wondering what is the best approach

Thank you, Shwetha

(6) By Aask (AAsk1902) on 2022-07-20 18:48:48 in reply to 5.1 [link] [source]

I do not have experience of Python.

Using SSMS, Tasks + Import Flat File gives you a direct means of migrating the data (which would be a one-off exercise).