How to Merge Tables and prevent IDs clashing

Question

I want to merge the IndirectFlights table to the PriceTable. I do not have IDs entered in the SourceTable (IndirectFlights) and I haven't set a PK for it yet. The ID column for the PriceTable is an Identity (1,1) column and is also the Primary Key.

Qs1 How do I enter IDs in Source column so that they dont clash with target table (PriceTable) IDs? I was thinking of using a sequence but It potentially could clash in future. Qs2 Can I choose what columns to merge or must I merge all the columns from the Source table?

Target Table (PriceTable) Columns
IDAirport_ICAO_Code,Airline_ICAO_Code,Departure,Price,RouteStatus,DateRowModified

Source Table (IndirectFlights) Columns
IDAirport_ICAO_Code,Destination,Airline,Airline_ICAO_Code,RouteStatus,Connecting Airport

Edit: I have just run the following Union All statement as an alternative to using Merge.

Select ID,Airport_ICAO_Code,Airline_ICAO_Code,RouteStatus
From RoughworkPriceTable
Union All
Select ID,Airport_ICAO_Code,Airline_ICAO_Code,RouteStatus
From RoughworkIndirectFlights;

The code worked but i noticed that the ID column accepted the Null values from IndirectFlights.ID eventhough I have the ID columns set to Not Null.

Can anyone explain this. Also can someone expalin how I could create a new permanent table from this Union All statement.

Answer 1

You can create a new table with something like

     Select * into newTmpTable from (
        Select ID,Airport_ICAO_Code,Airline_ICAO_Code,RouteStatus From RoughworkPriceTable 
    Union All 
    Select ID,Airport_ICAO_Code,Airline_ICAO_Code,RouteStatus From RoughworkIndirectFlights) 
as mergedData;