Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating a SQL Server Linked Server to SQLite to Import Data


By:   |   Read Comments (11)   |   Related Tips: More > Linked Servers


Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


Problem

You have data in SQLite databases on mobile devices you would like to import into SQL Server.  In this tip we walk through the steps on how to import this data into SQL Server.

Solution

This describes a simple method of importing data from a SQLite database into SQL Server using a linked server. You can find other methods of course, so see the links below in "Next Steps" for additional options.

Overview

These are the steps we will follow:

  1. Download an ODBC driver for SQLite
  2.  Install the driver
  3.  Create a System DSN for the database
  4.  Create a linked server in SQL Server
  5.  Select the data from the source and insert it into a SQL Server database table

1. Download an ODBC driver for SQLite

Go to this SQLite ODBC driver source page.

Configuring the correct driver is sometimes the most difficult part because of that I suggest downloading both the 32 and 64 bit drivers.

2. Install the driver

Run either the 32 bit or 64 bit driver exe file. Choose the one that suits the operating system you are using.

Download an ODBC driver for SQLite

Configuring the correct driver is sometimes the most difficult part

Install the driver

Run either the 32 bit or 64 bit driver exe file.

Choose the one that suits the operating system you are using.

3. Create a System DSN for the database

Click Start Run and type odbcad32 and press return for the 64 bit administrator.

Create a System DSN for the database

Click Start Run and type C:\Windows\SysWOW64\odbcad32.exe and press return for the 32 bit administrator.

Click Start Run and type C:\Windows\SysWOW64\odbcad32.exe and press return for the 32 bit administrator.

Click on the System DSN tab.

Click on the System DSN tab.

Click add.

Select the appropriate driver.

Select the appropriate driver. If you do not know which one to use then try them in turn.

f you do not know which one to use then try them in turn.

Enter your SQLite database path. Note that some of the options are documented at the driver site. I suggest leaving them as they are initially.

Enter your SQLite database path.

Notice the 32 bit driver is only editable from a 32 bit administrator and the 64 bit driver is only editable from the 64 bit administrator.

Notice the 32 bit driver is only editable from a 32 bit administrator and the 64 bit driver is only editable from the 64 bit administrator.

Notice the remove and configure buttons greyed out.

Notice the remove and configure buttons greyed out.

4. Create a linked server in SQL Server

If you are new to linked servers then you may wish to check these Linked Server tips at MSSQLTips first.

I suggest you simply use this T-SQL to create the linked server to your SQLite database.

There are no login accounts or any security context with this linked server.

USE [master]
GO
EXEC sp_addlinkedserver 
   @server = 'Mobile_Phone_DB_64', -- the name you give the server in SSMS 
   @srvproduct = '', -- Can be blank but not NULL
   @provider = 'MSDASQL', 
   @datasrc = 'Mobile_Phone_DB_64' -- the name of the system dsn connection you created
GO

5. Select the data from the source and insert it into a SQL Server database table.

Now click on the linked server stem and expand it all the way to the tables.

Then simply query the tables.

I suggest this SQLite browser if you want to view the tables or export the data as SQL.

Select *
from openquery(Mobile_Phone_DB_64 , 'select * from db_notes')
GO

You can create a table in your destination SQL Server with this sort of TSQL:

Select * into SQLite_Data -- This creates a table
from openquery(Mobile_Phone_DB_64 , 'select * from db_notes')
GO

Then modify the data types in your destination SQL Server database table using alter commands.

Check these suggestions by Michelle Gutzait or you may decide to not even import the data.

Data Types

Data types in SQLite are found at SQLite.org.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Graham Okely Graham Okely is a contract SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, December 08, 2016 - 7:34:41 AM - PHALGUNI RATHOD Back To Top

 Hello Sir,

I'm new to android and sqlite both and have some basic knowledge about mySQL. 

I'm facing a problem and couldn't find the appropriate answer anywhere.

I'm building a patient feedback system app, to save all the data I'm using sqlite3 but I read that it stores locally on our phone and not in a cloud or some server. 

My requirement is that, it must store the data/information in the NGO's (for whom I'm making this) IT System.

How can I do that?

I have searched a lot but didn't get anything! Please Help!!

Thanks in advance

Phalguni Rathod. (rathod.rulzzz@gail.com)


Friday, July 08, 2016 - 7:32:16 AM - dason Back To Top

 Good idea! 

The sqlite3 db file associated with this System DSN can not be deleted at once after you execute the sql "Select * from openquery(Mobile_Phone_DB_64 , 'select * from db_notes')". I must wait a few minutes to delete the file. Why? Can you help me? Thank you very much!

 

 


Thursday, April 07, 2016 - 11:01:47 PM - Graham Okely Back To Top

Hello Joe

Try simplifying.

Can you simplify your query? Can you use one select statement over one table and still show the error?

Regards

Graham


Thursday, March 24, 2016 - 2:55:57 PM - Joe Back To Top

When i query my sqllite data, specficlly an integer I get the following, 2147483647 instead of the correct answer.

I have googled the answer and am aware of the 32bit / 64bit conflict but can not find the answer.

Joe

Select

dv.id, dv.Name AS WSID, na.[ip_address] AS IP, dv.operating_system AS OS, dv.version As OSVer, dv.bios_version AS BIOS ,dv.bios_date AS BiosDate,

dv.manufacturer As Mfg, dv.model, dv.serial_number AS SN, Substring(dv.[current_user],9,8) As EmpID,

dv.processor_type,substring(pd.size,1,3)+' GB' As size

From 

Openquery(Spiceworkslink, 'Select * From Devices') As dv

Inner

Join Openquery(Spiceworkslink, 'Select * From [physical_disks]') As pd

On

dv.id = pd.computer_id Join Openquery(Spiceworkslink, 'Select * From [Network_Adapters]') As na

On

dv.id = na.computer_id

Where

Substring(dv.name,1,2) = 'WS' And pd.name = '0' -- and dv.name = 'ws3141'

Order

By dv.name


Sunday, November 15, 2015 - 10:41:25 PM - Graham Okely Back To Top

I have only extracted data from a SQLite database. I have not found any working example of inserting data into a SQLite database via SQL Server Management Studio.

I have researched this and here are some things I found:

A SQL Server SQLite sync tool: http://sqlite-sync.com/

A conversion tool that has some good comments: http://www.codeproject.com/Articles/26932/Convert-SQL-Server-DB-to-SQLite-DB

A thread on the topic: http://www.sqlservercentral.com/Forums/Topic866972-149-1.aspx

Note the performance difference when pushing and pulling data: http://sqlblog.com/blogs/linchi_shea/archive/2010/12/01/linked-server-and-performance-impact-direction-matters.aspx So pulling data from a SQLite database may be better for performance but the question remains how that can be done using SSMS.

Regards

Graham Okely


Friday, September 11, 2015 - 10:17:14 AM - Greg Back To Top

 

I'd be grateful if anyone could post some tips about inserting data (from SQL Server) back into SQLite. Especially into TEXT columns.


Saturday, November 01, 2014 - 12:49:05 AM - Graham Okely Back To Top

Hello Vishal Nayan

Connect your mobile phone to your windows computer. You may need to enable the connection from the phone. Each phone has its own process for doing that.

Note that an iPhone will not let you get to the SQLite databases without using some extra software of some kind. I do not recommend hacking your iPhone if you have one.

Once connected properly your phone should then appear as a drive letter on your windows computer. If it does then browse that drive with windows explorere to find the path to the SQLite database. Then enter that path and database name into step three.

All the  best

Regards Graham Okely


Tuesday, October 28, 2014 - 11:24:45 AM - Vishal Nayan Back To Top

Hi ,

 

While entering path of SQLite database, in example above it shows that database is located in C drive.So how to give the path if the database is in mobile.

 

Thanks in Anticipation.


Friday, May 16, 2014 - 1:59:50 PM - DaveBoltman Back To Top

Excellent - thanks for taking the time to make this. Helped a great deal...


Friday, May 09, 2014 - 7:30:12 PM - Adel Back To Top

Excellent guide! Thank you!


Wednesday, December 04, 2013 - 10:00:21 AM - Srinivas Back To Top

Awesome, Thanks

 

 

 


Learn more about SQL Server tools