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

how to update execution time of sql agent scheduler using sql script

 I have come across a scenario where it required to update sql agent scheduler execution date time every month in a different  date.
We can achieve this in couple of ways.
1. We can store the execution date time in a table and fetch that value and update it.
2. We can update the date time every month by running the script manually.
Please check below the execution script.
We need to update the MSDB.DBO.sysschedules table.

--You can use other columns as per your requirement

select name,enabled,active_start_date,active_end_date,date_modified 

from MSDB.DBO.sysschedules\

Declare @dt_active_start datetime

set @dt_active_start=GETDATE() --Fetch the value from table if it stored

update MSDB.DBO.sysschedules

set active_start_date=CONVERT(char(8),@dt_active_start,112)

,enabled=1

,date_modified=GETDATE()

where name='Scheduler Name'

You can use other fields for updating as per the requirement.. 

A Cursor with the name already exists

 While executing cursor inside Stored procedure it is throwing error:

A cursor with the name 'cursorName' already exists

declare cursorName CURSOR FOR
        select ...;

    open cursorName;

        Body of cursor...
        close cursorName;
        deallocate cursorName;

This is because we are using global cursor that will be defined each time you are calling this procedure and give you the same error.

Define a local cursor. Just put the keyword LOCAL after CURSOR

This will look like

declare cursorName CURSOR LOCAL FOR
...
This will resolve the issue.