Understanding IDENTITY_INSERT

Understanding IDENTITY_INSERT in SQL Server

Many of us will get a basic question on whether the IDENTITY_INSERT is ON or OFF on a SQL Table. We also have some confusion between IDENTITY and IDENTITY_INSERT.

In this article i will explain the basic use of IDENTITY_INSERT and how it exactly works.

Before understanding the IDENTITY_INSERT we need to know what is an identity column. It is column which keeps on incrementing without supplying the value explicitly during insertion.

In the below table EID is an identity column which will be incremented by 1 when a new record is inserted.

 

Now, for example let say I have a requirement to insert a EID with value as ‘10’, just below of this value. You can insert a custom value in two ways:

1. Either you turn off the Identity property by going into design, and opting for column properties, i.e., using GUI. After inserting that value, again making it ON, so that it will continue incrementing it.
2. Or using T-SQL code, as shown below:

SET IDENTITY_INSERT dbo.Customers ON;

INSERT INTO dbo.Employee VALUES (10, 'EmpName10') you will get an error,

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table ‘dbo.Employee’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

so you need to mention the Columns explicitly during insert as show below:

INSERT INTO dbo.Employee(EID,ENAME) VALUES (10, 'EmpName10')
Now, if you do a select * from Employee you will get the result as shown below,

And now if you want to continue to insert the next identity numbers you shouldn't forget to make the IDENTITY_INSERT Property to OFF on that table,

SET IDENTITY_INSERT dbo.Customers OFF;

In Simple words,

If you make IDENTITY_INSERT ON on a table you have to insert value in the identity column explicitly during insertion. If the IDENTITY_INSERT is OFF on a table it mean the value in the identity column will be auto generated.

In case if you are trying to insert some custom values in identity column make sure you set the IDENTITY_INSERT property to OFF once you have done with your insertion else the values in the identity column will not be auto generated.

Happy Coding!

Read More

Venkat 10:27 AM  No comment

Generating Identity Column in SELECT

Generating Identity Column in SELECT statements at runtime

There are situations where we need to generate an identity column [run time] seeding 1 when inserting records into staging or another tables. For example, you have a table where you have a primary key and identity has not been set on this primary key column.

If you want to insert records into this table on daily basis you need to generate the value for identity column when you are trying to insert records using select statements.

There are two easy ways to generate an primary identity column under select statements:

1. If you are inserting values using INTO statement you can use IDENTITY(INT,1,1) function in your select statement to generate unique values as shown below:

SELECT
 IDENTITY(INT, 1,1) AS EmployeeID,
 VALUE INTO #Ranks
FROM YourTable
WHERE 1=0

INSERT INTO #Ranks SELECT SomeColumn FROM YourTable ORDER BY SomeColumn

SELECT * FROM #Ranks Order By Ranks

The EmployeeID will be generated with unique identity number.

2. Another way of doing this is using; ROW_NUMBER() function,

SELECT ROW_NUMBER() OVER (ORDER BY colA) AS Row, cola
                FROM table1
This will also generates column with unique value seeded by 1.

Hope this helps!

Read More

Venkat 1:21 AM  No comment

SQL Server Error:53 the network path was not found

ADDITIONAL INFORMATION:

Error:

Could not open connection to SQL Server Error:53 the network path was not found

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

When you are trying to connect to SQL Server from SSMS you may face this error because of following reasons.

There are many generic reasons for this, few are below:

1.  Incorrect connection string.
2.  Named Pipes was not enabled on the SQL instance.
3.  Remote connection was not enabled.
4.  Server not started, or point to not a real server in your connection string.
5.  Other reasons such as incorrect security context.
6. Check the firewall.

The main reason would be you are not providing the right instance name in SSMS when you are connection to Database Engine.

You can find the instance name by the following methods:

1. Click Start--> Run --> Type CMD
type:  hostname Press enter
Result would be you system name: HOME
2. Then Open the SQL Server Configuration Manager
Click Start --> Programs --> SQL Server 2012 --> Configuration tools --> SQL Server Configuration Manager

check for the SQL Server instance name inside the parenthesis in the configuration manager (Ex: SQLSERVER2012)
Now the instance in SSMS should be:
HOME\SQLSERVER2012

This will connect you to your database engine.

Hope this helps!

Read More

Venkat 1:53 AM  No comment

Content within this application coming from the website listed is being blocked by internet explorer

This error seems like problem with Internet Explorer, but you will be getting this error due to the setting in the Outlook in most of the cases.

To over come this error, you need to change the People Pane setting in the outlook.
Follow the below steps:
1. Open outlook.
2. Goto to the View tab
3. Click on the People Pane and make it "Off".

This works for me. :)

Read More

Venkat 1:53 AM  No comment

Identify Missing Sequence Numbers SQL Server

There are many scenarios where we may in need to identify the missing numbers in the provided range.

Here is the quick query which help in identifying using CTE [Common Table Expression]:

DECLARE @Test TABLE
(
Num INT
)

INSERT INTO @Test VALUES (1)
INSERT INTO @Test VALUES (2)
INSERT INTO @Test VALUES (4)
INSERT INTO @Test VALUES (5)
INSERT INTO @Test VALUES (8)
INSERT INTO @Test VALUES (9)
INSERT INTO @Test VALUES (10)

--Get the Missing Numbers from the sequence
;WITH Missing (minid, maxid)
AS
(
SELECT 0 AS minid, 10
UNION ALL
SELECT minid + 1, maxid FROM Missing
WHERE minid < maxid
)
SELECT minid
FROM Missing
LEFT OUTER JOIN @Test tt on tt.Num = Missing.minid
WHERE tt.Num is NULL
OPTION (MAXRECURSION 0);

Result:
minid
0
3
6
7

Read More

Venkat 1:52 AM  No comment