Copy a database table to another database table with a different structure

Question

I am aware this was asked here. However it doesn't answer my question. I have 10 tables in a database called "merged". I am taking a bunch of other databases with an identical structure as "merged" except that "merged" has an extra column that is a combination of two columns in the table. I am trying to transfer all this data into merged but I believe the extra column I believe is preventing the transfer.

    SqlCommand MergeDB = connDestination.CreateCommand();

        string sqlMergeDB = "";        

        int a= 0;
        for (a=0; a < tablenames.Length; a++){
      string sqlMergeDB = "INSERT INTO sql_merged.dbo." + tablenames[a] + " SELECT * FROM sourceForMerged.dbo." + tablenames[a];    
using (SqlDataReader reader = MergeDB.ExecuteReader()) {
        while(reader.Read()) 
        {
        MessageBox.Show("Transfered a table");
        }
        }
    }

The error occurs at the SqlDataReader row of the code above, which I believe means there is something wrong with the sql command. Please help. Thanks

You are missing a space after table name. Change the second string " SELECT..." — Sean Lange
– Sean Lange, Commented Jun 11, 2015 at 18:02
You are correct, but that is just on the post. The code has one — Ben C.
– Ben C., Commented Jun 11, 2015 at 18:04
You also say "The error occurs at the SqlDataReader row of the code above", what error message do you get? — Sean Lange
– Sean Lange, Commented Jun 11, 2015 at 18:06

Larry Lustig · Accepted Answer · 2015-06-11 18:04:28Z

5

If you name all the columns in both parts of the INSERT . . . SELECT statement you can map which source column gets inserted into which destination column.

If you imagine TargetTable (Name, ProductType, Date) and SourceTable (Date, Type, Name) then using:

INSERT INTO TargetTable (Name, ProductType, Date)
  SELECT Name, Type, Date FROM SourceTable

would move the three columns into the appropriate columns even though the order doesn't match.

If you have "extra" columns in one table or the other you can either leave them out or provide expressions to fill them in:

INSERT INTO TargetTable (Name, ProductType, Date, CombinedValues)
  SELECT Name, Type, Date, (ValCol1 + ' '  + ValCol2) FROM SourceTable

has four columns receiving data from four expressions, one of which concatenates two columns worth of data. (In real life, you may find that the concatenation expression is more complicated, but this is the basic idea).

answered Jun 11, 2015 at 18:04

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Ben C. Over a year ago

Each table has different columns and there are too many columns to write out. Which is also something I should have mentioned in the question. My bad. How do I account for this?

Ben C. Over a year ago

It is also technically a one column shift, so could I just say; add table[col] = tableDestination[col + 1]?

Larry Lustig Over a year ago

No, they do not have "too many columns to write out". You just don't want to write them out. Bite the bullet and do it the right way. Otherwise your code will be very fragile: if one table has columns in a different order you may not find it until you've been running your database with bad data for weeks.

Ben C. Over a year ago

Dude theres like 6 columns! You want me to write them all out?

cdhowie Over a year ago

Six is "too many to write out?" I've manually written similar queries for tables with at least two dozen columns.

|

bumble_bee_tuna · Accepted Answer · 2015-06-11 18:25:34Z

2

You cannot use a:

Insert Into [Table] Select * From [Table2]

unless the tables schemas are identical. You would have to list out the columns for the Insert Statement.

If possible you could drop the column on the destination table and then add it back after the insert.

You could do something like this to build up you insert code if the table is very wide:

SELECT 
 'cmd.Parameter.Add("@' + column_name + '", SqlDbType.' + data_type + ');', 
  column_name 'Column Name',
  data_type 'Data Type'
FROM information_schema.columns
WHERE table_name = 'TableName'

edited Jun 11, 2015 at 18:25

answered Jun 11, 2015 at 18:07

bumble_bee_tuna

3,5618 gold badges48 silver badges87 bronze badges

4 Comments

Ben C. Over a year ago

It is also technically a one column shift, so could I just say; add table[col] = tableDestination[col + 1]?

bumble_bee_tuna Over a year ago

@BenC. If you are moving data from one column to another you will definitely have to Define your Columns in the insert. You build you C# code using to sql to create the parameter blocks.

cdhowie Over a year ago

Nitpick: The schemas don't technically have to be identical. Table can have more columns than Table2 if the extra columns are nullable or have a default value, and the data types don't have to exactly match so long as the values in every row of Table2 are implicitly convertible to the data type of the corresponding column in Table.

bumble_bee_tuna Over a year ago

@cdhowie good point although it never seems to work that way for me :( lol

Collectives™ on Stack Overflow

Copy a database table to another database table with a different structure

2 Answers 2

11 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related