Best method to insert data into 500 tables in SQLITE

[rex]

hi every one,

i am working on database project,in which i need to create 500 different tables and update some students data into it. previously i had done a data base project but with only 50 tables now it is 500 tables..
this is how i did previously to create 50 tables in connection.h

QString dbstr("create table if not exists Data( Time_Interval INTEGER, Channel1 varchar(250),Channel2 varchar(250),Channel3 varchar(250),Channel4 varchar(250),Channel5 varchar(250),Channel6 varchar(250),Channel7 varchar(250),
Channel8 varchar(250),Channel9 varchar(250),Channel10 varchar(250),Channel11 varchar(250),Channel12 varchar(250),Channel13 varchar(250),Channel14 varchar(250),Channel15 varchar(250),Channel16 varchar(250),Channel17 varchar(250),Channel18 varchar(250),Channel19 varchar(250),Channel20 varchar(250),Channel21 varchar(250),Channel22 varchar(250),Channel23 varchar(250)
)");
query.exec(dbstr);

QString dbstr("create table if not exists Data( Time_Interval INTEGER, Channel1 varchar(250),Channel2 varchar(250),Channel3 varchar(250),Channel4 varchar(250),Channel5 varchar(250),Channel6 varchar(250),Channel7 varchar(250),
Channel8 varchar(250),Channel9 varchar(250),Channel10 varchar(250),Channel11 varchar(250),Channel12 varchar(250),Channel13 varchar(250),Channel14 varchar(250),Channel15 varchar(250),Channel16 varchar(250),Channel17 varchar(250),Channel18 varchar(250),Channel19 varchar(250),Channel20 varchar(250),Channel21 varchar(250),Channel22 varchar(250),Channel23 varchar(250)
)");
query.exec(dbstr);

To copy to clipboard, switch view to plain text mode 

and this part of code to update data into the tables.

m_query.exec("begin transaction Trans");
for (int i = 0; i < radata2.count(); ++i)
{
QString sQuery = "insert into Data";
sQuery += " values (";for (int j = 0; j < radata2[i]->count(); j++)	sQuery += QString("%1").arg("'" + radata2[i]->at(j) + "'") + ", ";sQuery += ")";
QSqlQuery q;
q.exec(sQuery);
}

m_query.exec("begin transaction Trans");
for (int i = 0; i < radata2.count(); ++i)
{
QString sQuery = "insert into Data";
sQuery += " values (";for (int j = 0; j < radata2[i]->count(); j++)	sQuery += QString("%1").arg("'" + radata2[i]->at(j) + "'") + ", ";sQuery += ")";
QSqlQuery q;
q.exec(sQuery);
}

To copy to clipboard, switch view to plain text mode 

can some one suggest me what is the best method while dealing with so many tables.

thanks

[tbscope]

I hope this isn't a wrong question but why do you need 500 tables?

[rex]

i wanted it to store some data for 500 different students .

[tbscope]

Your problem can be simplified because you will not need 500 tables.

[rex]

Thanks a lot for your reply tbscope.. i have to create 500 tables for 500 different channels i am working on a student example first so i told student.. say Channel 1 to Channel 500. I have the data also for it ready i have it done. i am in a fix on how to go about inserting data for 500 Channels into 500 different tables. .

[vcernobai]

A fast way would be to put all CREATE and INSERT statements between database.transaction() and database.commit() statements (database is an QSqlDatabase object).

[rex]

can you pls show me a small example.. that will be of help for me.
thank you

[ChrisW67]

If your aim is to produce an independent sample data set in an Sqlite database for each of 500 students then by far the easiest way to do this is create a single sample data set in one Sqlite database and duplicate the whole database. Each student has their own data and cannot possibly stuff up any other student's data.

A more normal design for this situation would create a single table which each records carrying a channel number column. The channel number column would contain an identifier for the channel the row's data belonged to.

If you persist with creating 500 tables in one database then it is a trivial exercise to loop 500 times creating and executing "CREATE TABLE" statements.

[rex]

Hello Sir ,
This is what i want to achieve. I am trying to create 500 Channels from Channel 1 to Channel 500 and Time Stamp in the first column, insert data into it every second i have all the channel data processed in a QVector<QString> i need to insert it into data dase. I read in the Detailed Description of QString, how do i overcome this problem cause i will have 500 columns but here i can use only upto 99 place markers.

If there is no place marker (%1, %2, etc.), a warning message is output and the result is undefined. Note that only placeholders between %1 and %99 are supported.

If there is no place marker (%1, %2, etc.), a warning message is output and the result is undefined. Note that only placeholders between %1 and %99 are supported.

To copy to clipboard, switch view to plain text mode 

Pls give me a small hint or code Snippet for the best way to insert data into db with so many tables.

thanks a lot

[rex]

some one pls help me out with the SQLite data creation and insertion for many number of channels..

thanks

[tbscope]

I personally think that you should start with a good book about database design.

[ChrisW67]

You start the thread with:

i need to create 500 different tables

and then reinforce that with:

i am in a fix on how to go about inserting data for 500 Channels into 500 different tables.

Ultimately it seems that you want 500 columns in a single table:

This is what i want to achieve. I am trying to create 500 Channels from Channel 1 to Channel 500 and Time Stamp in the first column, insert data into it every second i have all the channel data processed in a QVector<QString> i need to insert it into data dase. I read in the Detailed Description of QString, how do i overcome this problem cause i will have 500 columns but here i can use only upto 99 place markers.

The obvious solution to the problem as stated is to build the row with a series of queries rather than one. You may also be able to make this work using parameterised queries and bind parameters in QSqlQuery (Sqlite limit seems to be 999 parameters by default).

The far superior solution is to sit down and design the database properly as tbscope advises. Sqlite will allow more than 500 columns but that certainly does not make doing so a good idea. I think a table with three columns will do it and will even adapt easily to the arrival of a 501st channel.

[rex]

Hello sir, thank you for your time.

i am sorry that was a mistake. i meant 500 columns not tables.

i am in a fix on how to go about inserting data for 500 Channels into 500 different tables.

I think a table with three columns will do it and will even adapt easily to the arrival of a 501st channel

I think a table with three columns will do it and will even adapt easily to the arrival of a 501st channel

To copy to clipboard, switch view to plain text mode 

but i might have different data for each column for Channel , so i though i must have 500 columns to save each channels data. How could i use a table of only three columns to accommodate 500 different channels of data.?
I did go through sqlite faq to find some answer but din't find any.

thank you

[ChrisW67]

I am with tbscope:

I personally think that you should start with a good book about database design.

Although I suspect that even a rudimentary overview would suffice.

Ponder this:

CREATE TABLE channelData (
  sampletime integer,
  chNumber integer,
  chData varchar(250)
);

CREATE TABLE channelData (
  sampletime integer,
  chNumber integer,
  chData varchar(250)
);

To copy to clipboard, switch view to plain text mode 

Every sample:

BEGIN TRANSACTION
INSERT INTO channelData ( sampletime, chNumber, chData )
VALUES (strftime('%s', 'now'), 0, "some ch0 data");
INSERT INTO channelData ( sampletime, chNumber, chData )
VALUES (strftime('%s', 'now'), 1, "some ch1 data");
INSERT INTO channelData ( sampletime, chNumber, chData )
VALUES (strftime('%s', 'now'), 2, "some ch2 data");
...
INSERT INTO channelData ( sampletime, chNumber, chData )
VALUES (strftime('%s', 'now'), 499, "some ch499 data");
COMMIT;

BEGIN TRANSACTION
INSERT INTO channelData ( sampletime, chNumber, chData )
VALUES (strftime('%s', 'now'), 0, "some ch0 data");
INSERT INTO channelData ( sampletime, chNumber, chData )
VALUES (strftime('%s', 'now'), 1, "some ch1 data");
INSERT INTO channelData ( sampletime, chNumber, chData )
VALUES (strftime('%s', 'now'), 2, "some ch2 data");
...
INSERT INTO channelData ( sampletime, chNumber, chData )
VALUES (strftime('%s', 'now'), 499, "some ch499 data");
COMMIT;

To copy to clipboard, switch view to plain text mode 

and later this:

SELECT * from channelData where chNumber = 2 order by sampletime;

SELECT * from channelData where chNumber = 2 order by sampletime;

To copy to clipboard, switch view to plain text mode