SQL Syntax For Reading sqlite_master Object

Hi Richard,

Fairly straightforward. To create a new empty table from an existing table:

CREATE TABLE new_table_name AS SELECT * FROM original_table_name

But I think you know this so am including for completeness.

To get the column names from the original table:

Dim SQL as SQL
Dim ResultsSet As Cursor
Dim ResultsString As String

ResultsString = ""

ResultsSet = SQL.ExecQuery("PRAGMA table_info(`original_table_name`)")

For i = 0 To ResultsSet.RowCount -1

ResultsSet.Position = i

ResultsString = ResultsString & ResultsSet.getString("name") & "," 'Build comma delimited list

Next

To add these in a single field as a comma delimited list in you New Table (you should remove the last comma from the list first):

SQLString = "INSERT OR REPLACE INTO new_table_name (id, fieldnames) VALUES (" & whateverID & ", '" & ResultsString & "')"

SQL.ExecuteNonQuery(SQLString)

Common Field Values are:

name = Column Name
cid = Position
type = Column Type (Text, Int, Date etc)

Hope this helps,

Mark S.

Thanks Mark.

On reflection I don't think the best way to do this is programaticly as it will only be done once. The lookup table should be put into place when the principle data is finalised so I will probably do it with an SQL manager during development.

Any offers as to a regular SQL script to complete the task ???