How to insert Identity values and not Identity values on table have Identity column?

Error:
Cannot insert explicit value for identity column in table 'TestIdentity' when IDENTITY_INSERT is set to OFF.

Some times we have scenario like we have source table and destination table
we have to insert source to destination table(have identity on both)

1) Insert identity values as per destination(Ex: 1,5,8,9,10 etc)

2) Insert values without consider identity values as per source.(Ex: 1,2,3,4,5 etc)

1) Insert Identity values in Source as per destination

Two methods:

SSMS Method:

Let see an example

See the source and destination table

So now u can use SSMS(SQL Server Management Studio) method

Just right click table and click Modify or Design and change the identity of column to be set to off

as see below and save the table.

 Now insert some 10 records into destination table as see below:

So table looks like this

T-SQL Method:

We can use this method when Identity column should be ON status.
you  can ensure identity column should be on

but when u insert values it will throw as see below

Use code as see below


SET IDENTITY_INSERT SchemaName.Tablename ON

after that u can check property of table identity column is Now insert values as see below

2) We should insert values without consider identity values as per source.

When identity column is ON status u can insert values but u should not mention identity column in select list then u can get identity values as see below.

Additional Info:

An explicit value for the identity column in table 'products_copy' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Solution:

we have to specify full column list

insert into tablename(specify here)
select spectify here from tablename

(or)