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

sql sqlbulkcopy sql-server-2008

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.

Popular Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why