• Welcome to SQLitening Support Forum.
 

Is there an easy way to do split a master table into many smaller tables

Started by Andrew Lindsay, October 18, 2014, 10:45:26 AM

Previous topic - Next topic

Andrew Lindsay

Hello, I have a situation now where the amount of data generated is getting quite large and I would like to be able to spilt the data from a large table into smaller tables.
My database looks like the following

Table1
ID1 as INTEGER
FixedID as STRING *12
FreeID as STRING * 12
Data1 as REAL
Data2 as REAL
.
.
.
Data86 as REAL

For each FixedID, I have lots of FreeID elements, so I would like to create tables where each table would have the FreeID and subsequent data as the elements of the table.

So if I had 36 rows of records with three (3) unique FixedIDs, I'd like to put the associated FreeID records into the three tables.

I hope this makes sense.

Best regards

Andrew

cj

It  can probably be done with a SELECT DiSTiNCT or GROUP BY FixedID.

In the meantime this works.
The Select * can be replaced with just the columns you want in the new tables.

SUB DoIt(sBigTable AS STRING, sNewTable AS STRING, sFixedID AS STRING)
  slexe "drop table if exists " + sNewTable
  slExe "create table " + sNewTable  + " AS Select * from " + sBigTable + " where fixedid = " + sFixedID
  'slExe "create table " + sNewTable  + " AS Select * from " + sBigTable + " where fixedid = '" + sFixedID + "'" 'if string

END SUB


Create master table, insert records, split master into 3 tables, display 3 tables:

#DIM ALL 'split.bas
#INCLUDE "sqlitening.inc"
'-------------------------------------------------------------------------
FUNCTION PBMAIN () AS LONG'
  LOCAL sTableToSplit,sNewTable,sFixedID,s AS STRING, x AS LONG
  sTableToSplit = "TableToSplit"
  slopen "test.db3","C"
  slExe "drop table " + sTableToSplit
  slexe "Create table if not exists " + sTableToSplit + " (F1 integer primary key,FixedID,Other)"
  '--------------------------------------------------------------------------------------
  s = "begin exclusive;"  'create test records
  FOR x = 1 TO 5
    s+= "insert into " + sTableToSplit + " values(null,100,'Apple');"
    s+= "insert into " + sTableToSplit + " values(null,200,'Beat');"
    s+= "insert into " + sTableToSplit + " values(null,300,'Carrot');"
  NEXT'
  s+= "end"'
  slexe s
  '3 splits  ------------------------------------------------------------------------------------
  sNewTable = "TableA": sFixedID  = "100": Splitter sTableToSplit,sNewTable,sFixedID  'first  copy
  sNewTable = "TableB": sFixedID  = "200": Splitter sTableToSplit,sNewTable,sFixedID  'second copy
  sNewTable = "TableC": sFixedID  = "300": Splitter sTableToSplit,sNewTable,sFixedID  'third  copy
  'Display 3 new tables  -------------------------------------------------------------------------
  LOCAL sArray() AS STRING
  slSelAry "SELECT * from " + "TableA",sArray(),"Q9"  :s =JOIN$(sArray(),$CRLF) + $CRLF + $CRLF
  slSelAry "SELECT * from " + "TableB",sArray(),"Q9c" :s+=JOIN$(sArray(),$CRLF) + $CRLF + $CRLF
  slSelAry "SELECT * from " + "TableC",sArray(),"Q9c" :s+=JOIN$(sArray(),$CRLF) + $CRLF + $CRLF
  ? s,, "3 tables"
END FUNCTION
'-------------------------------------------------------------------------
SUB Splitter(sTableToSplit AS STRING, sNewTable AS STRING, sFixedID AS STRING)
  slexe "drop table if exists " + sNewTable
  slExe "create table " + sNewTable  + " AS Select * from " + sTableToSplit + " where fixedid = " + sFixedID
END SUB
"No email alerts being received"  Please often check back.