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.

1
0
5/19/2014 10:21:48 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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.

0
5/19/2014 1:15:04 PM

Related Questions

To Insert/Update in Main Table from Staging Table in SQL Server 2008

I would probably do something like this:...make your transformations on that staging table (add additional columns as needed etc.)...do the INSERT/UPDATE into the actual data table using the ...SQL Server 2008 ...MERGE... command... which is ideally suited for just this - update some existing rows, insert some new rows (and possibly delete some old...
join sqlbulkcopy sql-server-2008
asked by marc_s

How to copy and validate data from one table (all varchar) to another (typed) in C#?

Do it in T-SQL with a ...linked server.... ...I.e.: ...--begin a transaction to wrap validation and load BEGIN TRAN --Validate that no tickets are set to closed without a completion date SELECT * FROM bigTableOnLocalServer with (TABLOCKX) -- prevent new rows WHERE ticketState = '1' /* ticket closed */ and CompletionDate = 'open' --if validatio...
c# entity-framework sqlbulkcopy
asked by Mitch

Bulk copy of SQL data from table on disk to an in-memory table

create a class.....public class MemoryObject { public Int64 Id {get; set;} public string OtherProperty {get; set;} } ...then, use a SQLDataReader......public List<MemoryObject> GetMemoryObjects() { using (SqlConnection connection = new SqlConnection("...")) { string sql = "query"; SqlCommand command = new Sql...
c# database sql sqlbulkcopy sql-server
asked by christiandev

Excel Data to new SQL Server table using C# - new table not being created?

Hi think you are not checking your table name before inserting excel data into data table. you can go through the below code....class excelTwosheetsBulk1 { private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); static void Main(string[] args) { log...
c# sqlbulkcopy sql-server tsql
asked by Naresh.boddu

Sql Server - Insert data in one table along with the new auto-incremented inserted ids of another table

Insert you first table with the new keys (leave the pk blank on insert) and make a (temporary) col in the DB1 table for the old key. lookup (join) your second insert on the old key column to get your new fk. When your done delete the old key column and your done....Here is the sql:...CREATE TABLE DB1.Group( GroupID [int] IDENTITY(1,1) NOT NULL, [Co...
bulkinsert sqlbulkcopy sql-server
asked by Tristan

SqlBulkCopy rows from one table to another but with different columns order

I have two tables in two different servers. That tables have same columns but in different order and I cant perform bulk copy;...I'm tried to do... var copier = new SqlBulkCopy(destination_connection); copier.DestinationTableName = destination_table_name; foreach (DataColumn column in source_table.Columns) { var ...
.net sqlbulkcopy sql-server
asked by atomAltera

Validating data in my Data Table before attempting to write to the database with SQLBulkCopy

No, there is not....But you can as a programmer do validation before inserting. Not exactly that hard, you know. And there is no need to have a HEAVY data table at all - use normal objects and hammer them into the SqlBulkDCopy instance using your own implementation of the required interface ;)
c# datatable sql sqlbulkcopy
asked by TomTom

SqlBulkCopy Wcf Rest Moving Data between 2 sql server tables

Based on the info you gave, your solution would be something like the sample code I inserted. Pointers for the SQLBulkCopy: ...BCopyTutorial1..., ...BCopyTutorial2... SQLBulkCopy with transaction scope examples: ...TrasactionBulkCopy...My version is a simplified version of these, based on your question....Interface:...[ServiceContract] public inter...
c# rest sqlbulkcopy wcf
asked by DDan

Python Postgres Best way to insert data from table on one DB to another table on another DB

Simpliest way is to use FDW (foreign data wrappers) for connect both servers (...https://www.postgresql.org/docs/9.5/static/postgres-fdw.html...). And operate by both table in one server. ...Second way is use dblink (...https://www.postgresql.org/docs/9.6/static/dblink.html...). ...By both this way data directly pass from one server to second (no v...
bulkinsert plpgsql postgresql python-3.x sqlbulkcopy
asked by Roman Tkachuk

Create procedure that will insert or update data from one temporary table to other 4 tables

You cannot use a variable to track all the changes, but you track all the changes with OUTPUT....if object_id('dbo.[MSPCompanies]') is not null drop table dbo.[MSPCompanies]; if object_id('dbo.[MSPTempTable]') is not null drop table dbo.[MSPTempTable]; create table dbo.[MSPTempTable] ( MSP_Companies_ID int identity(1,1), INN ...
database sqlbulkcopy sql-server stored-procedures
asked by ildanny

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow