IDENTITY_INSERT Scope level

Some time we need to insert in the identity field also but what will be the impact if we want to Allow the  identity insert, see the below example:

Create a Test Table :
————————————–

CREATE TABLE sample(
id int IDENTITY(1,1)
,name VARCHAR(100)
,address VARCHAR(100)
,phoneNo VARCHAR(15)
)

In One Session, I am going to Allow the Identity Insert
————————————————————————–

SET IDENTITY_INSERT dbo.sample ON;

INSERT INTO sample(id,name,address,phoneNo)
SELECT 10,'user-2','address-2','014002555-2'


SELECT * FROM sample

In another session i am going to insert normally without ID value:
——————————————————————-
INSERT INTO dbo.sample(name,address,phoneNo)
SELECT 'user','address','014002555'

SELECT * FROM sample

—————————-

So the Conclusion is IDENTITY_INSERT will be Enabled on Session Level only. You can insert with identity value from one session and another session will be working normally with auto identity value.

UPDATE with top clause

If you want update a table with top clause see the below script:

UPDATE top (1) TABLE1
SET status ='ready'
where status ='hold'

At the same time if you want to return the Row IDs of updated table:

UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid
where status ='hold'

—————— result—————-
rowid
1

Same thing if you want to store in variable

DECLARE @UpdatedIds table (id int)
DECLARE @rowId int


UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid INTO @UpdatedIds
where status ='hold'

SELECT @rowId=id FROM @UpdateIds
SELECT @rowId

HTTP Get method from MSSQL

If you want to call a URL and store the response of that url use this function:

ALTER function [dbo].[GetHttp]
(
@url varchar(8000)
)
returns varchar(8000)
as
BEGIN
DECLARE @win int
DECLARE @hr  int
DECLARE @text varchar(8000)

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win


EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

RETURN @text


END

Http Post from MSSQL

Call the HTTP post using MSSQL script :


ALTER function [dbo].[POSTHttp]
(
@url varchar(8000),
@data varchar(2000)
)
returns varchar(8000)
as

BEGIN
DECLARE @win int
DECLARE @hr  int
DECLARE @text varchar(8000)


EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'Open',NULL,'POST',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'setRequestHeader',NULL, 'Content-type'
, 'application/x-www-form-urlencoded'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send',null,@data
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

RETURN @text

END

CTE Function

CTE fucntion can be used on this way:

IF OBJECT_ID('tempdb..#dataList') IS NOT NULL
DROP TABLE #dataList

CREATE TABLE #dataList(
id            INT
,name        VARCHAR(50)
,parent        INT
,ip            INT
)


INSERT #dataList
SELECT 1    id, 'Group1'        Name, 0 Parent, 0 ip UNION ALL
SELECT 2    id, 'SubGroup1'        Name, 1 Parent, 0 ip UNION ALL
SELECT 3    id, 'Item1'            Name, 2 Parent, 1 ip UNION ALL
SELECT 4    id, 'Item2'            Name, 2 Parent, 1 ip UNION ALL
SELECT 5    id, 'Item3'            Name, 2 Parent, 1 ip UNION ALL
SELECT 6    id, 'Item4'            Name, 2 Parent, 1 ip UNION ALL
SELECT 7    id, 'SubGroup2'        Name, 1 Parent, 0 ip UNION ALL
SELECT 8    id, 'Item5'            Name, 7 Parent, 1 ip UNION ALL
SELECT 9    id, 'Item6'            Name, 7 Parent, 1 ip UNION ALL
SELECT 10    id, 'Item7'            Name, 7 Parent, 1 ip UNION ALL
SELECT 11    id, 'Item8'            Name, 7 Parent, 1 ip

--SELECT * FROM #dataList


DECLARE @id INT = 1
;WITH CTE(id, name, parent, ip)
AS
(
SELECT * FROM #dataList WHERE id = @id
UNION ALL
SELECT d.* FROM #dataList D
INNER JOIN CTE c ON d.parent = c.id
)

SELECT * FROM CTE WHERE ip = 1 ORDER BY id ASC;

Dynamic PIVOT

Sample for Dynamic PIVOT Query:

Some time we need complex cross tab result for report, that time we can use the PIVOT Query, for example we have 3 Tables and we have data like this :


IF OBJECT_ID('tempdb..#customer') IS NOT NULL
DROP TABLE #customer

CREATE TABLE #customer (
CustomerId INT,
CustomerName VARCHAR(50)
)


INSERT #customer
SELECT 1, 'Ram'
INSERT #customer
SELECT 2, 'Hari'
INSERT #customer
SELECT 3, 'Depesh'
INSERT #customer
SELECT 4, 'Sailesh'

--Select * from #customer



————————————————————————–

IF OBJECT_ID('tempdb..#LoanType') IS NOT NULL
DROP TABLE #LoanType

CREATE TABLE #LoanType (
LoanId INT,
LoanName VARCHAR(50)
)


INSERT #LoanType
SELECT 1, 'Loan-1'
INSERT #LoanType
SELECT 2, 'Loan-2'
INSERT #LoanType
SELECT 3, 'Loan-3'
INSERT #LoanType
SELECT 4, 'Loan-4'
INSERT #LoanType
SELECT 5, 'Loan-5'
INSERT #LoanType
SELECT 6, 'Loan-6'

--Select * from #LoanType


——————————————————————————

IF OBJECT_ID('tempdb..#LoanCustomer') IS NOT NULL
DROP TABLE #LoanCustomer


CREATE TABLE #LoanCustomer (
CustomerId INT,
LoanId INT,
Amount MONEY
)


INSERT #LoanCustomer
SELECT 1, 1, 500
INSERT #LoanCustomer
SELECT 2, 1, 5300
INSERT #LoanCustomer
SELECT 3, 3, 1500
INSERT #LoanCustomer
SELECT 2, 1, 2500
INSERT #LoanCustomer
SELECT 1, 6, 5500
INSERT #LoanCustomer
SELECT 2, 4, 1100
INSERT #LoanCustomer
SELECT 2, 3, 3200
INSERT #LoanCustomer
SELECT 2, 3, 3200

--Select * from #LoanCustomer


——————————————————————————–

Combine Result of three table from PIVOT Query:


DECLARE
@sql VARCHAR(MAX)
,@LoanType VARCHAR(MAX)


SELECT
@LoanType = ISNULL(@LoanType + ', ', '') + '[' + LoanName + ']'
FROM (
SELECT
DISTINCT
l.LoanName
FROM #LoanCustomer lc
INNER JOIN #LoanType l ON lc.LoanId = l.LoanId
) x


SET @sql = '
SELECT
CustomerName
,' + @LoanType +
Sample for Dynamic PIVOT


FROM (
SELECT
c.CustomerName,
l.LoanName,
lc.Amount
FROM #LoanCustomer lc
INNER JOIN #customer c ON lc.CustomerId = c.CustomerId
INNER JOIN #LoanType l ON l.LoanId = lc.LoanId
) dataList
PIVOT
(
SUM(Amount)
FOR LoanName IN(' + @LoanType+ ')
) X '


EXEC (@sql)


 

This Script is Prepared by Netra Prasad Acharya (MSSQL Developer)

MSSQL Hash Algorithms

MSSQL Hash Algorithms:

There are two forms of encryption algorithms: one-way and two-way. Two-way algorithms allow you to encrypt and decrypt data. One-way algorithms will only encrypt data without any ability to decrypt.

A hash algorithm is the simplest way to encrypt data, however, once encrypted, you can’t decrypt the data. Your data remains secure, ensuring that in order to “decrypt” the contents of a column you would first have to know the original data value.

--Different hash algorithms produce different hash values
DECLARE @TEST_VAL varchar(100)
SELECT @TEST_VAL = 'SQL Server'
SELECT HashBytes('MD5', @TEST_VAL)
SELECT @TEST_VAL = 'SQL Server'
SELECT HashBytes('SHA1', @TEST_VAL)
GO

--Hash values are case sensitive
DECLARE @TEST_VAL varchar(100)
SELECT @TEST_VAL = 'sql'
SELECT HashBytes('SHA1', @TEST_VAL)
SELECT @TEST_VAL = 'SQL'
SELECT HashBytes('SHA1', @TEST_VAL)
GO

Follow

Get every new post delivered to your Inbox.