Solving: Cannot insert explicit value for identity column in table ‘NewInventory’ when IDENTITY_INSERT is set to OFF.

 

 

SQL Server SET IDENTITY_INSERT

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

 

I was getting the message:

Msg 544, Level 16, State 1, Line 73
Cannot insert explicit value for identity column in table ‘NewInventory’ when IDENTITY_INSERT is set to OFF.

This was caused by a script trying to set multiple tables to “IDENTITY_INSERT ON”.

Using a recursive common table expression, to calculate totals and subtotals of children and grandchildren in a hierarchy

In SQL Server a common table expression can be used to calculate totals / subtotals of a table containing hierarchical data:

 

-- Create test data:
if object_id('tempdb..#Hierarchy') is not null
begin
drop table #Hierarchy
end
create table #Hierarchy (
Id int not null,
ParentId int null,
Amount int not null
)
insert into #Hierarchy (Id, ParentId, Amount) values
(1, null, 100)
,    (2, 1, 100)
,   (3, 1, 100)
,   (4, 1, 100)
,   (5, 4, 100)
,   (6, 4, 100)
,   (7, 4, 100)
,   (8, 7, 100)
;with CTE (ParentId, Id, Amount, IsParent)
as
(
-- Add all records as "anchor", because foreach record we are going to calculate the total amount.
select    h.ParentId
,        h.Id
,        h.Amount
,        0 as IsParent
from    #Hierarchy h
union all
-- Recursively add parent records until root parent foreach record in "anchor".
select    h.ParentId
,        h.Id
,        h.Amount
,        1 as IsParent
from    CTE c
join    #Hierarchy h on c.ParentId = h.Id
)
select        c1.Id
,            c1.ParentId
,            sum(c1.Amount) as TotalAmount
,            case when sum(c1.IsParent) > 0 then 1 else 0 end as IsParent
from        CTE c1
group by    c1.Id, c1.ParentId

Result

image

How to add multiple constraints to one column, when creating a table with T-SQL

 

If you want to add a “named” default and a “named” unique constraint to one column, when creating a table with T-SQL, you can use the “inline syntax”:

 

Here we create a table with a column “Code”, that has a “named” default and a “named” unique constraint:

if object_id('dbo.Product') is null
begin
create table dbo.Product
(
Id int identity(1,1) not null constraint PK_dbo_Product_Id primary key,
Code uniqueidentifier not null constraint UQ_dbo_Product_Code unique(Code)
constraint DF_dbo_Product_Code default newid()
)
end
go

Change varchar column length, when column does not match given length in T-SQL.

The following code will update a column to varchar(max) only, when it does not already have the varchar(max) length.

-- Update column 'Name' length, it should be varchar(max).
-- Note: varchar(max) has length '-1'.
if (    
(
select character_maximum_length
from information_schema.columns
where table_name=object_name(object_id('dbo.Table1'))
and COLUMN_NAME = 'Name'
) <> -1
)
begin
alter table dbo.Table1 alter column Name varchar(max) not null
end

How to get the amount of elapsed minutes in current quarter in T-SQL

If you want to know the amount of elapsed minutes in the current quarter, you can use the following T-SQL code:

declare @CurrentDateTime datetime = '2014-03-27 10:56:10'
declare @MinutesPassedInHour int = convert(int, datepart(minute, @CurrentDateTime))
declare @quarterCount int = @MinutesPassedInHour / 15
declare @MinutesPassedInQuarter int = (@MinutesPassedInHour - (@quarterCount * 15))
select @MinutesPassedInQuarter
-- Result: 11

How to get the day of week of a all dates between start and end date in T-SQL

If you want to get a list of all dates between a start and end date and their corresponding day of week in T-SQL, you can use the following code:

set datefirst 1
declare @StartDate date = '2014-01-20'
declare @EndDate date = '2014-01-27'
;with cte (dt, dw) as
(
select cast(@StartDate as date), datepart(dw, @StartDate)
union all
select dateadd(day, 1, dt), datepart(dw, dateadd(day, 1, dt))
from cte
where dateadd(day, 1, dt) <= @EndDate
)
select dt, dw
from cte

Result

image

How to validate a value can be cast to an integer in T-SQL

Validating if a value is an integer can be done by using TRY_CAST in SQL Server 2012 or a combination of ISNUMERIC and cast to float in <= SQL Server 2008.

declare @value as varchar(255) = '1.6'
-- SQL Server 2008
select case        when isnumeric(@value) = 1
then 'Cast succeeded, value is: ' + cast(cast(cast(@value as float) as int) as varchar(255))
else 'Cast failed.'
end as    Result
-- SQL Server 2012
select    case    when try_cast(@value as float) is not null 
then 'Cast succeeded, value is: ' + cast(cast(cast(@value as float) as int) as varchar(255))
else 'Cast failed.'
end as    Result

image

 

If you use try_cast(@value as int) the cast will fail on a float ‘1.6’, like:

 

image

Just a reminder: null = null, is not true in T-SQL

If you have a nullable column "Description" in one table and a nullable column "Description" in an other table and you join both tables on this column, records with both null in the field "Description" will not match.

if object_id('[dbo].[Product1]') is null
begin
create table [dbo].[Product1]
(
Id int not null identity(1,1) constraint PK_Product1_Id primary key,
[Description] varchar(max) null
)
end
go
if object_id('[dbo].[Product2]') is null
begin
create table [dbo].[Product2]
(
Id int not null identity(1,1) constraint PK_Product2_Id primary key,
[Description] varchar(max) null
)
end
go
insert into Product1 values (null)
insert into Product1 values ('')
insert into Product1 values ('gevuld 1')
insert into Product2 values (null)
insert into Product2 values ('')
insert into Product2 values ('gevuld 1')
select        p1.*, p2.*
from        Product1 p1
inner join    Product2 p2 on p1.[Description] = p2.[Description]

Results in

image

 

and you might have expected

image

 

Solution is to use isnull function, like:

 

select        p1.*, p2.*
from        Product1 p1
inner join    Product2 p2 on isnull(p1.[Description], 'Not a value') = isnull(p2.[Description], 'Not a value')

T-SQL tip: log error details in catch

If you want to log detailed information on an error in a catch block, you can use the following code:

 

BEGIN CATCH
declare @Message varchar(2048) = 
'<< Your custom error text here. >>' + 
'Number: ' + cast(isnull(error_number(), '') as varchar(50)) + ' ' +
'Severity: ' + cast(isnull(error_severity(), '') as varchar(50)) + ' ' +
'State: ' + cast(isnull(error_state(), '') as varchar(100)) + ' ' +
'Procedure: ' + cast(isnull(error_procedure(), '') as varchar(1024)) + ' ' +
'Line: ' + cast(isnull(error_line(), '') as varchar(10)) + ' ' +
'Message: ' + cast(isnull(error_message(), '') as varchar(1024)) + ' '
ROLLBACK TRANSACTION    
RAISERROR (@Message, 16, 1)

 

END CATCH