sql query to insert values to auto increament column

Question

I want to populate values for an auto-increment column when inserting values for a table from another table.

Here is query im using and it throws error:

create table test12
(
    Id int,
    name varchar(255),
    dept varchar(255)
)

insert into test12 
values (1, 'f', 'cs'), (2, 'b', 'cse'), (3, 'c', 'cs'),
       (4, 'd', 'cse'), (5, 'e', 'cs'), (6, 'f', null)

select * from test12                                                            

create table test34
(
    seq int identity(1,1) not null,
    name varchar(255) not null,
    dept varchar(255) default('cs')
)

insert into test34 (seq, name, dept) 
values (1, (select name from test12),
(select case when dept='cse' then 'Y' else 'N' end as dept from test12))          

Please let me know what my mistake is.

Answer 1

you need not to give values for Identity column, it is auto added, just exclude identity column form insert as:

INSERT INTO test34 (name, dept) 
SELECT 
    name,
    CASE WHEN dept = 'cse' THEN 'Y' ELSE 'N' END AS dept 
FROM test12

If you really want to add Identity values manually try below SET statements.

SET IDENTITY_INSERT test34 ON;

INSERT INTO test34 (seq, name, dept) 
SELECT 
    ID
    name,
    CASE WHEN dept = 'cse' THEN 'Y' ELSE 'N' END AS dept 
FROM test12

SET IDENTITY_INSERT test34 OFF;

Answer 2

You can turn ON or OFF the IDENTITY_INSERT

SET IDENTITY_INSERT test34 ON

insert into test34(seq,name,dept) values 
(1,(select name from test12),
(select case when dept='cse' then 'Y' else 'N' end as dept from test12))

SET IDENTITY_INSERT test34 OFF