SELECT INTO FROM and IDENTITY column

It is very common scenario when we want to create temporary or standard table based on TSQL statement. If we want to achieve something like this we can use SELECT INTO FROM syntax. It has very interesting feature – it gives us opportunity to create column with IDENTITY automatically! How? – i will show you in this post based on very simple example.

First of all we need to choose table or statement that will be used as a source – it could be Employees from Northwind database. After that we can choose six attributes from this table and EmployeeID column which is Primary Key of this table. Our destination table will be named IdentityTest and will be associated with default schema – dbo.

After that we will check metadata of destination table using sys.columns:

IS_IDentity column

As you can see – column that was defined with IDENTITY in source table inherited this property in destination table also – it is normal behaviour of SELECT INTO FROM statement which copies metadata from source table to destination.There is also similiar way to achieve the same thing. We can create IDENTITY column on purpose without using source column. It is very simple just look at the example:

After that when we read data from sys.columns we receive something like this:

IDENTITY FROM SELECT INTO

As you can see our EmployeeID column is incremented automatically. In this short post i try to show you this interesting feature becuase in some cases it can speed up your work especially because it works in the same way with temporary tables.

Leave a Comment

Your email address will not be published. Required fields are marked *