How do I move data between two tables with different schemas?

×

Sometimes you need to move data between two different tables and the schema of the two tables doesn't match.

  1. Generate starting INSERT script
    1. In MSSMS right-click the table Script table as>Insert To>New Query Editor Window
    2. This will give you a pre-built script to insert a single record using values
  2. Write a SELECT statement for the data you want to move
    1. Below the Values listing of fields write a SELECT statement to retrieve the data from the other table paying close attention to the column order and data types
    2. For any required data that doesn't exist in your source table supply default values remembering that you can use GETID() to generate GUIDs and GETDATE() to generate dates
    3. Remember that you can remove any NULLABLE columns that you don't need, but you must remove them from the column list in the INSERT statement as well as the SELECT statement
    4. Run the SELECT statement to verify that it gives you the correct records in the correct format
  3. Remove the VALUES keyword and column list
  4. Run the INSERT query to move your data

 

Here is an example query moving data from the table OldTable to NewTable supplying defaults for ID, CreatedDate, and Column3 and only pulling records from 2019 or later:

INSERT INTO NewTable (ID,CreatedDate,Column1,Column2,Column3)
SELECT GETID(),GETDATE(),OldCol1,OldCol2,'Fixed Value'
FROM OldTable
Where OldDate>='2019-01-01'