Copying data from table1 to table2, with slightly different datatypes, names and additional column

insert mysql sql sqlbulkcopy

Question

Im running a task where i need to copy data from table1 to table2. table1 is generated from a very old application which has a tendency of creating unusual datatypes, such as decimal etc. (these are unnecessary).

So the structures are as described:

table1:

ordernumber - decimal(11,0)
var3 - varchar(4)
var4 - varchar(30)
var5 - int(50)
...

table2:

id int() <- PK, AI
order_number - int(xx)
var3 - varchar(x)
var4 - varchar(x)
var5 - int(x)
....

So as shown, theres an additional column, id, in table2 and ordernumber is modified to order_number along with the datatype which is changed from decimal to int. The x is defined as it is not relevant in this context.

Is there a clean way to copy the data?

1
1
9/21/2012 3:54:29 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

You can use INSERT INTO...SELECT statament. Try

INSERT INTO table2 (orderNumber,var3, var4, var5)
SELECT orderNumber,var3, var4, var5
FROM table1
2
9/21/2012 3:56:14 PM

Related Questions

When copying data in sql, add a column

You could just add it to be returned by your SELECT:...SELECT *, GETDATE() AS CurrentDate from dbo.source ...And then just add another ColumnMapping for it.
sql sqlbulkcopy sqldatareader sql-server-2008
asked by AdaTheDev

Is it possible to transfer data across servers using sql bulk copy?

Yes. In fact, in the demo code shown ...here..., they copy to a table within the same database.
sql sqlbulkcopy sql-server
asked by Joe Stefanelli

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

"ERROR: extra data after last expected column" when using PostgreSQL COPY

An empty table won't do. You need table that matches the structure of input data. Something like:...CREATE TABLE raw_data ( col1 int , col2 int ... ); ...You don't need to declare ...tab... as ...DELIMITER... since that's the default:...COPY raw_data FROM '/home/Projects/TestData/raw_data.txt'; ...800 columns you say? That many columns would ty...
database-design dynamic-sql postgresql sqlbulkcopy
asked by Erwin Brandstetter

how to add column mapping in sqlbulkcopy in c# where column names contains White Space

found solution need to add Square brackets around the fields only where the White space involved in column name
.net sql sqlbulkcopy
asked by Pritam

SqlBulkCopy is not copying all the data in a column over

My guess would be it is coversion errors. OLEDB will infer the datatype of a column based on the first 8 rows (I think) so if your first rows are:...SomeColumn ---------- 1 2 3 4 5 6 7 8 9 apple ...This initially looks like an integer column, so this is what it is mapped to, but then when it gets to "apple" i...
.net sql sqlbulkcopy vb.net visual-studio-2012
asked by GarethD

Bind default value if excel sheet column value null in sql bulk copy upload

Your problem must be that the column allows NULLS....In case your column allows NULL and you provide a NULL value on the insert the NULL ...WILL... be inserted. If no value is provided for the column the DEFAULT will be considered....Setup your column to NOT NULL....ALTER TABLE [dbo].[tbl_HEI_student] ALTER COLUMN [Date_of_Birth] DATE NOT NULL ...H...
asp.net asp.net-mvc-4 c# sqlbulkcopy sql-server
asked by mxix

Postgres: Copying large subsets of data(10k+rows) from one schema to another for immediate use

Since you are copying from a bunch of tables in one schema of a database to another schema of the same database, and cannot start until your fastest way to do so is going to be by using ...INSERT INTO ... SELECT FROM..., run as a user who has access to both schema, e.g.:...INSERT INTO schema2.table_y ( field, field, field ) SELECT field, field, fie...
postgresql sqlbulkcopy
asked by FuzzyChef

Redshift copy creates different compression encodings from analyze

COPY doesn't currently recommend ZSTD which is why the recommended compression settings are different. ...If you're looking to apply compression on permanent tables where you want to maximize compression (use least amount of space), setting ZSTD across the board will give you close to optimal compression. ...The reason RAW is coming back on some co...
amazon-redshift amazon-s3 data-compression sql sqlbulkcopy
asked by Tony Gibbs

Keeping Data Columns by name and removing the others within a DataTable

You can't change a collection while you are enumerating on it. You can change your code to a standard for loop with a backward indexing like this...for(int x = dataTable.Columns.Count - 1; x >= 0; x--) { DataColumn dc = dataTable.Columns[x]; if(dc.ColumnName != "Cat" && dc.ColumnName != "Dog" && dc.ColumnName != "Turtle " && dc.ColumnN...
c# datatable sqlbulkcopy
asked by Steve

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