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

Calculate last day of month using sql server

There are couple of ways to calculate last day of a given month.

SQL Server 2012 on wards there is inbuilt function find the last day of month.

Let's see different ways with example:

Using SQL Server in built function.

select EOMONTH(getdate(),0) as curr_month

output:

curr_month

2020-07-31

select EOMONTH(getdate(),-1) as  Prev_month

output:

Prev_month

2020-06-30

Note: 0,-1, -2 can be used to navigate among months.

Let's see how to find last day of month with out using the function

EX1:

select DATEADD(MM,1+DATEDIFF(mm,0,DATEADD(m,0,getdate())),-1) as curr_month

EX2: (best approach)

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, getdate()) +1, 0)) as curr_month

curr_month

2020-07-31 00:00:00.000

Ex1:

select DATEADD(MM,1+DATEDIFF(mm,0,DATEADD(m,-1,getdate())),-1) as Prev_month

Ex2:

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, getdate()) , 0)) as Prev_month

Prev_month

2020-06-30 00:00:00.000

Highlighted 0,-1 is being used to navigate among months

The above script can be converted to t-sql scripts

Declare @date datetime

set @date='2020-07-21'

 select DATEADD(MM,1+DATEDIFF(mm,0,DATEADD(m,0,@date)),-1) as curr_month

or

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) +1, 0)) as curr_month

curr_month

2020-07-31 00:00:00.000

Refreshing a View

When create a view, SQL Server stores metadata information describing the view, its

columns, security, dependencies, and so on. Schema changes in underlying objects are not

reflected in the view’s metadata information. After applying such schema changes,

 it's a good practice to refresh the view's metadata information using the sp_refreshview stored procedure so that the changes are reflected in the view.

Example:

Create table:

USE tempdb;

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL DROP TABLE dbo.Table1;

CREATE TABLE dbo.Table1(col1 INT, col2 INT);

INSERT INTO dbo.Table1(col1, col2) VALUES(1, 2);

GO

Create View

IF OBJECT_ID('dbo.RefreshView', 'V') IS NOT NULL

DROP VIEW dbo.RefreshView;

Go

CREATE VIEW dbo.RefreshView

AS

SELECT * FROM dbo.Table1;

GO

select * from RefreshView

col1      col2

1          2

Add Column to the table

ALTER TABLE dbo.Table1 ADD col3 INT;

The schema change in T1 was not reflected in the view’s metadata information

select * from RefreshView

col1      col2

1          2

To refresh the view’s metadata information, run the sp_refreshview stored procedure

EXEC sp_refreshview 'dbo.RefreshView';

select * from RefreshView

col1      col2      col3

1          2          NULL

When done delete the table and view.

IF OBJECT_ID('dbo.RefreshView', 'V') IS NOT NULL

DROP VIEW dbo.RefreshView;

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL

DROP TABLE dbo.Table1;

Video link:

Order by in View

An ORDER BY clause without TOP or FOR XML specification is not allowed in the query

defining the view because a view is supposed to represent a table. A table is a logical entity

that has no order to its rows.

So names must be unique in valid table and the names must be unique.

Ex:

IF OBJECT_ID('VCustomers', 'V') IS NOT NULL

DROP VIEW VCustomers;

GO

CREATE VIEW VCustomers

AS

SELECT custid, custname

FROM Customers

order by custid

GO

It throws error below

Msg 1033, Level 15, State 1, Procedure VCustomers, Line 5 [Batch Start Line 3]

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

TOP and ORDER BY or ORDER BY and FOR XML are allowed in a view definition, whereas ORDER BY alone is not.

Now modify the view as below.

CREATE VIEW VCustomers

AS

SELECT top(10) percent custid, custname

FROM Customers

order by custid

GO

Now run the below script

When you are done run the below script to delete the view.

IF OBJECT_ID('VCustomers', 'V') IS NOT NULL

DROP VIEW VCustomers;

GO

For More details watch the video below.

SQL Query to convert Date from MM/DD/YYYY to YYYY-MM-DD Format

Date Format Conversion:

MM/DD/YYYY to YYYY-MM-DD

Declare @DateFormat varchar(20)
set @DateFormat='02/21/2019' --MM/DD/YYYY

Select @DateFormat as SourceFormat, CONVERT(Date,@DateFormat,101) as TargetFormat