Styjun

I have created a SQL Server database table and have many data entries to enter into the table. In this particular scenario I have two columns, Technology Questions and time_entered.

I have tried to combine the existing data values into a single INSERT INTO statement, however I have not been able to find a way to scale it up so I don't have spend all day copying over basic data.

This query works however note that it has only 4 data elements, two for each column. I'd like to scale this up so I can insert hundreds of lines at once. The insert statement below I derived from the existing data below (from a SQLITE3 table and I am trying to insert the data into a SQL Server database).

INSERT INTO questiontype([Technology Questions], time_entered)
VALUES ('Technology Question', '2018-10-29 13:31'),
 ('Technology Question', '2018-10-29 14:11')

The above example works great but I don't know the best way to scale this up.

Example of existing data (There are hundreds of rows like below)

Technology Question|2019-03-23 10:40
Technology Question|2019-03-23 10:40
Technology Question|2019-03-23 13:24
Technology Question|2019-03-23 13:55
Technology Question|2019-03-23 13:55
Technology Question|2019-03-23 16:23
Technology Question|2019-03-23 16:26
Technology Question|2019-03-23 16:59
Technology Question|2019-03-24 13:34
Technology Question|2019-03-24 15:11

Is there a more efficient way by chance?

(1) Linked Server approach

Since you are reading data from SQLite3, you can create a linked server from SQL Server and use a simple insert statement to import data:

INSERT INTO questiontype([Technology Questions], time_entered)
SELECT [Technology Questions], time_entered
FROM openquery(sqlite_linked_server, 'select * from sqlitetable')

Additional information

• Creating a SQL Server Linked Server to SQLite to Import Data

(2) SSIS approach

If you are familiar with SQL Server integration Services, it is an efficient way to transfer data from a data source to another, you can use it to transfer data from SQLite through ODBC into SQL Server.

• CONNECTING TO SQLITE THROUGH SSIS

(3) Using ad-hoc query approach

I haven't tried this approach before, i don't know if it is efficient or not.

You can connect to SQLite database through ODBC without adding a linked server, you can do this using an ad-hoc query using OPENROWSET

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

INSERT INTO questiontype([Technology Questions], time_entered)
SELECT [Technology Questions], time_entered
FROM OPENROWSET('MSDASQL', 'DSN=<odbc DSN>', 
 'SELECT * FROM sqliteTable')

Additional information

• Import data from SQLite to Microsoft SQL Server