New user loading data

[Dean]

Can someone help me with loading data to a table please?

I'm a new user to H2 and have seen that I can CSVread a csv file into
a table but can do it.
I've tried to read the help but don't know where to store the csv
file. I've tried the csvwrite to see where H2 stored it but can find
the output file.

I'd also like to be able to "Backup" a table before adding new data
with the csvread just incase but again it does not seem to be doing
anything.

Yes I am logged in as the Admin user.

Help please?

Thanks
Dean

[Brian]

You can specify the full path as an argument surrounded by single
quotes.
If you wanted to specify only the file name - it would assume it was
in the directory where you launched H2 from.

CREATE TABLE FILEDATA1 AS SELECT * FROM CSVREAD('c:\temp
\my_file.csv');

The downside of this is that it assumes everything is a huge varchar
column - so after the initial load it's a good idea to create the
table ahead of time with the proper datatypes and sizes, then do an
INSERT INTO PRECREATEDTBL SELECT field,list,here from CSVREAD('c:\temp
\my_file.csv');

Table rotation could be done with ALTER TABLE RENAME and then re-
creating the table with the original name to accept the new incoming
csv.

[Dkso]

Thank you.  I said I was new to this.  I forgot to mention that the DB is on a server and not my local machine.

As it is I've been doing some testing on a local db and have been able to do it with your help but I still can't get it into the DB on the server.  Have I got to FTP the data there first or can it be done through H2?

 

Dean

[Thomas Mueller]

Hi,

You could create an in-memory database (locally on your machine) using the database URL jdbc:h2:mem:test, and then create a linked table ("create linked table...") to your target database (where you need the data). Then import to this linked table using "insert into linked_table select * from csvread". This is quite a bit slower than local import, but possibly it is easier than to move the data to the target machine first.

Have I got to FTP the data there first or can it be done through H2?

People usually transfer the data using the operating system (it doesn't have to be FTP). But I understand this is a bit complicated if you have a client-server application.

Regards,
Thomas