Append data to an existing table
|Assignee:||Andy Dufilie||% Done:|
|Category:||Admin Console and Servlets||Estimated time:||(Total: 60.00 h)|
I want to be able to add data, such as for a more recent year to an existing table without deleting or modifying the existing data.
For example: I have student testing scores for 2006 - 2010 (50+ fields) which i have used to create visualizations. In the near future i am going to want to update that dataset to include 2011 data.
Right now the only way I can see to do this is delete the existing table, reload it with the additional fields and "hope" or if i remember, make an extra copy, so I can recreate all the exact field name aliases and the exact table name.
Any extra or less spaces, commas, colons etc. means the visualizations will not work.
I have attached a screen shot that shows Student test results over the years. The Year heading is where I specify the different years available.
I am going to want to append the 2011 data with the same name structure.
I do not want to re-enter all the title, year, max, min etc. information for the previous years that already exist.
I just want to append/add the new data to the existing table. So only the title, year, max, min values for the new data has to be updated.
#5 Updated by Susan Brunton about 5 years ago
I'm not sure i understand. I need a practical example.
I have a table with the years in a column, I would append the 2012 data to the table in the database.
School ID | Year | % Proficient
1 | 2008 | 75.2
1 | 2009 | 77.4
1 | 2010 | 73.6
1 | 2011 | 74.9
2 | 2008 | 65.2
2 | 2009 | 67.4
2 | 2010 | 63.6
2 | 2011 | 64.9
1 | 2012 | 74.9
2 | 2012 | 65.2
Where/when would I import the new years in a separate SQL table and how would Weave pick up that there are new records to be aliased and moved?
#7 Updated by Andy Dufilie about 5 years ago
When you say "without deleting or modifying the existing data" are you referring only to the Weave configuration data, or the data in the sql table as well?
If you want to replace the entire sql table, that would be doable, but there's no fool proof way we could determine which rows of SQL data are new and just insert those (for example, imagine that the SQL table has been modified after it was initially imported).
#9 Updated by Susan Brunton about 5 years ago
Here is an example
Database table for school enrollment:
city | year | total enrollment | grade1 | grade 2 ...
Boston | 2008 | 123 | 45 | 67
Boston | 2009 | 127 | 43 | 67
Boston | 2010 | 126 | 45 | 68
Boston | 2011 | 128 | 45 | 68
Based on the current filtering I have it stored in the Admin Console to display with alias names for each year as:
Total Enrollment, 2008
Total Enrollment, 2009
Total Enrollment, 2010
Total Enrollment, 2011
Grade 1 Enrollment, 2008
Grade 1 Enrollment, 2009
Grade 1 Enrollment, 2010
Grade 1 Enrollment, 2011
Grade 2 Enrollment, 2008
Grade 2 Enrollment, 2009
Grade 2 Enrollment, 2010
Grade 2 Enrollment, 2011
I can end up with hundreds of rows with alias names.
But now i want to add 2012 enrollment. I want to append the data directly into the database outside of Weave.
But how to I get it into the Admin Console without redoing the hundreds of alias names?
Total Enrollment, 2012
Grade 1 Enrollment, 2012
Grade 2 Enrollment, 2012
I'm just trying to think of ideas to make this possible. Maybe right click or a button to add a column (a delete column option would compliment this feature) that the administrator can populate new information. Basic information such as dataTable, keyType could be pre-populated and the administrator could fill in the new data information that would require being saved. Once saved a check could be run for obvious errors and then added to the table so it can be arranged in order etc.