Adding Identity Column in SELECT INTO Statement in SQL SERVER

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

Note: More than one identity column cannot be in table.

-- Create table
CREATE TABLE MyOrders3
(
    ProductName varchar(20)
);

-- Creating Copy of 'MyOrders3' with additional IDENTITY column
select IDENTITY(int, 1,1) AS Id,* INTO MyOrdersIdentity
from MyOrders3

insert into MyOrdersIdentity values ('Samsung')
select * from MyOrdersIdentity

Copying the Table and Data using SELECT INTO STATEMENT in SQL SERVER

It creates a new table in the destination, so if any table having same name in destination, then drop and continue, otherwise it throws error.

 -- Created table with Primary Key

CREATE TABLE MyOrdersPrimary
(
    OrderId int PRIMARY KEY NOT NULL,
    ProductName varchar(20)
);

-- Inserted some records

insert into MyOrdersPrimary values (1,'Samsung')
insert into MyOrdersPrimary values (2,'Nokia')
select * from MyOrdersPrimary

-- COPY THE (TABLE + DATA).

Select * into MyOrdersPrimaryCopy from MyOrdersPrimary

Note: the above wont copy the primary key to the copy table if you use 

SELECT INTO STATEMENT, only it copies table and data.

To Overcome the ABOVE problem:

If you want the complete Table structure,

CREATE Table by using source table script  and then use  "INSERT INTO" to copy data.