move data from one table to other table with different table structure

Question

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.

Do you have `rowNo` on `TemplateOne`? If so it is much easier. — naota, May 19 '14 at 10:31
yes I have identity column. If not I can use a table variable for that — Yagnesh.Dixit, May 19 '14 at 11:16

score 0 · Answer 1 · answered May 19 '14 at 11:37

0

If I understand correctly, you can use unpivot

select 'TemplateOne' as tableid, ColumnName, celldata
from TemplateOne
unpivot (celldata for ColumnName in (colum1, colum2, colum3, colum4)
        ) as unpvt;

To do this in general, you need to use dynamic SQL.

answered May 19 '14 at 11:37

Gordon Linoff

1,242,037
58
646
786

I will try unpivot but I am unable to relate my example with your code (might be lack of unpivot knowledge). – Yagnesh.Dixit May 19 '14 at 12:59

score 0 · Answer 2 · answered May 19 '14 at 13:15

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

Your SQL could go something like this:

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.

move data from one table to other table with different table structure

2 Answers2