Sql-server – Select Into removes IDENTITY property from target table

identitysql servert-sql

I am doing an exercise which removes the IDENTITY property from the target table when using a select into statement.

source table name: Sales.Orders
column name: orderid
column property: int type, not null, defined as a primary key, identity property is defined on the column)
Column name: custid
column property: int type, foreign key, null
target table name: Sales.MyOrders

In the 70-461 exam book – Querying Microsoft SQL Server 2012, chapter 10 p336:

  1. it gives code

    ISNULL (orderid + 0, -1) as orderid,
--this will remove IDENTITY property and make column not null

  2. Make custid column not null in target able

    ISNULL (custid, -1) as custid, --make column not null

I don't understand these two code items, though it works.

Could someone tell me the concepts behind the code?

The code provided in the book:

select isnull (orderid + 0, -1) as orderid, 
isnull (custid, -1) as custid, 
empid,
isnull (cast(orderdate as date), '19000101') as orderdate,
shipcountry,
freight
into Sales.MyOrders
from Sales.Orders
where shipcountry = N'Norway';

Best Answer

The behaviors you're curious about are explained in the documentation:

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
...
- The identity column is part of an expression.
...
If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property.

And in this thread from UC:

Metadata is determined based on the source column and expressions used in the SELECT list. Below are the rules:

Any expression that uses a built-in function like SUBSTRING, LEFT, RIGHT etc (except ISNULL) for example is considered as NULLable by the engine. So if you use CAST(somecol as char(8)) then the expression is NULLable.
...
So to make an expression or column in the SELECT list not null then use ISNULL around the column or expression.

Related Question