CODEWITHSUNDEEP
sqldatabasebashshellsqlite
adayzdone
adayzdone

Reputation: 11238

insert datetime() from .import

How can I insert datetime() into a column that is imported with .import? If I populate the field of the csv with datetime(), it is populated in the table as a string "datetime()", not the time the file was imported. I am populating the table with:

insert into foo(date_entered,cName, cAddress) select * from tempTable;

Do I have to populate the value of the date_entered field in the csv file with a placeholder and then update table afterwards or can I directly import the datetime() function?

Upvotes: 1

Views: 97

Answers (2)

adayzdone
adayzdone

Reputation: 11238

I ended up importing the file into a temporary table tmp(cName, cAddress). I then then ran

insert into Main(date_entered, cName, cAddress) select datetime(), * from tmp;

Upvotes: 0

T.P.
T.P.

Reputation: 2995

I'm not sure this is the best way to approach it for your situation, but a using a trigger could provide a solution.

I used the following data from a file called data.csv

data.csv

1,datetime(),Row1
2,datetime(),Row2
3,datetime(),Row3
4,datetime(),Row4
5,datetime(),Row5
6,datetime(),Row6
7,datetime(),Row7
8,datetime(),Row8
9,datetime(),Row9

...and ran the following script using ".read demo.sql" which dropped/recreated the schema objects, set the data file delimiter, ran the import and then selected from the "demo" table.

demo.sql

drop table demo;

create table demo (
  id  integer,
  dt  text,
  tag text
);

create trigger my_trigger after insert on demo
begin
  update demo set dt = datetime() where dt = 'datetime()';
end;

delete from demo;

.sep ","

.imp data.csv demo

select * from demo;

And which produced the following results in the demo table after the import operation.

$ sqlite3 my.db
SQLite version 3.8.0.2 2013-09-03 17:11:13
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read demo.sql
Error: near line 1: no such table: demo
1,2013-10-09 23:47:14,Row1
2,2013-10-09 23:47:14,Row2
3,2013-10-09 23:47:14,Row3
4,2013-10-09 23:47:14,Row4
5,2013-10-09 23:47:14,Row5
6,2013-10-09 23:47:14,Row6
7,2013-10-09 23:47:14,Row7
8,2013-10-09 23:47:14,Row8
9,2013-10-09 23:47:14,Row9

Note: I was running the latest SQLite3 binary on WinXP.

Upvotes: 1

Related Questions