Best method to insert data into 500 tables in SQLITE

[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