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

Question

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.

Yagnesh.Dixit

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.

naota

View more on Stack Overflow

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

Question

Popular Answer

Performance

Expression Evaluator