I am creating a generic data upload app where user creates the table structure and upload the data.
I have generic data table. with following structure.
--------------------------------------
tableId | ColumName | rowNo | CellData
now user will creates template and upload data in csv format for that template.
suppose user created following template.
----------------------------------------
colum1 | colum2 | colum3 | colum4
and insert data as follows
TemplateOne
colum1 | colum2 | colum3 | colum4
-----------------------------------
x1 | x2 | x3 | x4
A1 | A2 | A3 | A4
B1 | B2 | B3 | B4
I want to move this data to generic data table as follows.
tableId | ColumName | rowNo | CellData
--------------------------------------
TemplateOne | colum1 | 1 | X1
TemplateOne | colum1 | 2 | A1
TemplateOne | colum1 | 3 | B1
TemplateOne | colum2 | 1 | X2
TemplateOne | colum2 | 2 | A2
TemplateOne | colum2 | 3 | B2
TemplateOne | colum3 | 1 | X3
TemplateOne | colum3 | 2 | A3
TemplateOne | colum3 | 3 | B3
TemplateOne | colum4 | 1 | X4
TemplateOne | colum4 | 2 | A4
TemplateOne | colum4 | 3 | B4
I am trying to develop a dynamic sql for the same. Just want to know if there is any easier way of doing this.
If Yes please suggest.
I guess you can use INSERT INTO SELECT
statement to copy data from table1
to table2
.
INSERT INTO table2 (col1,col2 ...)
SELECT col1,col2... FROM table1;
The Document is here:
http://www.w3schools.com/sql/sql_insert_into_select.asp
INSERT INTO generic (tableId, ColumName,rowNo,CellData)
SELECT 'TemplateOne' AS tableId, 'colum1' AS ColumName, rowNo, colum1 AS CellData
FROM TemplateOne
UNION ALL
SELECT 'TemplateOne' AS tableId, 'colum2' AS ColumName, rowNo, colum2 AS CellData
FROM TemplateOne
UNION ALL
SELECT 'TemplateOne' AS tableId, 'colum3' AS ColumName, rowNo, colum3 AS CellData
FROM TemplateOne
UNION ALL
SELECT 'TemplateOne' AS tableId, 'colum4' AS ColumName, rowNo, colum4 AS CellData
FROM TemplateOne;
DEMO : http://sqlfiddle.com/#!2/f3e84a/1
Hope this helps.