Logical Processing Order of the SELECT statement

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. 

However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order may vary from this list.

Dynamic Incremental load using sql query and merge join

 Incremental load from source table to destination table can be done using sql query and merge join.

Since it is a dynamic approach since source tables are not having same table name or column name every load.

Before executing below script needs to be followed below points.

1. Source table and destination table should have same number of columns and data types.

2.  Source table should have primary key and we are considering destination table is an intermediate table.

3. If it is an bigger table then better to avoid delete operation.

Find out below script for reference: Hope it helps!

Declare
	@source_table varchar(500),
	@dest_table varchar(500),
	@dest_business_key varchar(max),
	@source_business_key varchar(100),
	@target_columns varchar(max),
	@source_columns varchar(max),
	@setstatement nvarchar(max),
	@finalstatement nvarchar(max)

set @dest_business_key=''

select @dest_business_key=@dest_business_key+ 'taget.'+ c.Name
	from sys.indexes i
	inner join sys.index_columns ic on i.object_id=ic.object_id and i.index_id=ic.index_id
	inner join sys.columns c on ic.object_id=c.object_id and ic.column_id=c.column_id
	inner join sys.objects o on i.object_id=o.object_id
	inner join sys.schemas sc on o.schema_id=sc.schema_id
where i.is_primary_key=1
	and o.name=@dest_table
order by o.name,i.name,ic.key_ordinal

set @dest_business_key=LEFT(@dest_business_key,len(@dest_business_key)-4)
set @source_columns=''

select @source_columns=@source_columns+'source.'+'['+COLUMN_NAME+']'+','
	from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@source_table

set @source_columns=LEFT(@source_columns,len(@source_columns)-1)
set @setstatement=''

select @setstatement=@setstatement+'['+COLUMN_NAME+']'+'source.'+'['+COLUMN_NAME+']'+','
	from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@dest_table

set @setstatement=LEFT(@setstatement,len(@setstatement)-1)
set @finalstatement=''

select @finalstatement=@finalstatement+@setstatement

Declare @sql nvarchar(max)
set @sql='merge '+@dest_table+' as target using '+@source_table+' as source on '+@dest_business_key+
' when matched then Update set ' +@finalstatement+
' when not matched by target then insert values ('+@source_columns+')
when not matched by source then
delete'
;

--exec sp_executesql @sql