Merge schema and insert old schema data

Transact-SQL
1

Hello,
I have an urgent requirement where I have existing table and the app creates a new table with schema where some of the columns have changed, added or removed. I need to merge these 2 schemas in such a way that it maintains the new schema column orders. Then I need to insert old data into the newly merged schema. How do I accomplish that?

For example,
Old Schema: ID, Name, Address, City
New Schema: ID, Name, Address1, Address2, State

Thank you for your time.

2

Why do you care about the column ordinal positions? Please tell me no one is counting on SELECT * to return the columns in some particular order!

What I would do:

  1. Create the new table
INSERT INTO NewTable( ID, Name, Address1, Address2, State)
    SELECT  ID, Name, Address, null, City
    FROM OldTable
3

Hello gbritton,
The order is very important so that the app can access what schema changed for the next time. I understand your step #2 but I have dozens of columns here and need to be done programmatically with generic code. Any solution?

Thanks.

4

The order is not important if the app selects by column name. SQL will return the columns in the order specified in the SELECT statement.

Step 2. Why? Surely this is a one-off. You might spend more time building and testing generic code than doing it by hand. Note that SSMS lets you select a table and then Script as ... to a new query window. Do this on the new table, script as INSERT INTO to a new window. Then do it on the old table, script as SELECT to clipboard. Paste the results of the second into the INSERT window, verify column order and away you go.

5

I don't think I can explain the situation any better than what I already have, but thanks for your time anyway.

6

really I want to help. If it were me, "dozens of columns" would take maybe 10 minutes to put together. Writing a generic solution would probably take an hour or two. So, I really want to understand why you want a generic solution here.

7

If you really want to help, then let's forget about doing anything in Management Studio. All the code is in .Net app. From the app, I am reading the Information_Schema to get column names like this:
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where table_name=OLDtablename
Then I collect columns from this query into a variable.
Then I write the same query to get columns from NEWtablename
Then I collect columns from this NewTableName query into another variable
Now I need to merge these column names into yet another variable name
Create a new table with the merged column names
insert data into this new table from OLDtablename

Thank you

8

wow! That's certainly the long way around! Here's an idea:

Post the schemas for the old and new tables. I'll post back a query to do the work. We should have done this yesterday! Then, you'd be done already.

9

I have now made it work, but thanks for your time.

10

Hello gbritton,
I need your help for another thing. The keyword "EXCEPT" will get me the rows for differences in tables while comparing. I am looking for only the exact data that changed instead of whole row.
For example: I have a row of columns: A, B, C
With EXCEPT, I get the whole row that changed
The only value that changed at this point is in column B
So all I want is column B, instead of whole row.
How do I accomplish this?

Thank you.

11

You have to compare column by column. No shortcut I'm afraid.

12

That is what I am doing now. Thanks.