0

I have an SQLite3 database. I also have an SQL Server database with the same structure. I need to export the data from SQLite and insert it into the SQL Server database.

The export from SQLite and the modification of the generated export needs to be 100% scripted. Inserting into the SQL Server database will be done manually through SQL Server Management Studio.

I have a mostly good dump of the database through this answer here. I can modify most of the script as needed with sed.

The one thing I'm stuck on right now is that the SQLite database stores timestamps as number of seconds since UNIX epoch. The equivalent column in SQL Server is DATETIME. As far as I know, inserting an integer into a DateTime won't work.

Is there a way to specify that certain fields be converted a certain way upon dumping from SQLite? Meaning, specify that the integer fields be dumped as proper DateTime strings that SQL Server will understand?

Or, is there something I can run on the Linux command line that will somehow find these Integer timestamps and convert them?

EDIT: Anything that runs in a Bash script on Ubuntu is acceptable.

User51610
  • 453
  • 5
  • 18
  • I think doing this in the dump files is going to be risky. Is it possibly to convert them after pushing the data up into sql server? Like the dump files will push to a stage schema/database and then everything can then be pushed up to your target schema/database and can be transformed during that step? Alternatively, converting them before dumping them might be an option? – JNevill Oct 03 '19 at 19:09
  • *"The one thing I'm stuck on right now is that the SQLite database stores timestamps as number of seconds since UNIX epoch. The equivalent column in SQL Server is DATETIME"* No the equivalent data type in SQL Server is simply `INT` – Raymond Nijland Oct 03 '19 at 19:11
  • So, something like [this](https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server) except transformed on dump rather than on input to SQL Server? – alttag Oct 03 '19 at 19:35
  • Or just get the ODBC driver for SQLite and use SSIS or SQL Server import - export wizard to move the data from SQLite to SQL Server. Any modification can be done in the package. – Shadow Oct 03 '19 at 20:59

1 Answers1

0

Three basic solutions: (1) modify the data before the dump; (2) manipulate the file after the dump, or (3) modify the data on import. Which you choose will depend on how much freedom you have to modify schemas.

If you wish to do it in SQLite, I'd suggest adding text columns with the dates stored as needed for import to SQL Server, then ignore or remove the original columns on dump. The SQLite doc page for datetime() may help, as might answers to this question.

Or, you can write a function in SQL Server that handles the import. Perhaps set it on an insert trigger.

Otherwise, a script that manipulates your dump file would work too. It sounds like you have a good handle on how to do this.

alttag
  • 1,163
  • 2
  • 11
  • 29