CODEWITHSUNDEEP
sql-server-2008insert
GLP
GLP

Reputation: 3685

How to insert data from one table to anther table?

I have two tables, 

tblA(id, num, col1, col2, col3), 
tblB(col1, col2, col3)

col1, col2 and col3 are the same in both tables. Now I have following sql:

declare @num...(same type as num)

insert into tblA
select @num, * from tblB

id in tblA is an indentity column.

But I got following error, Column name or number of supplied values does not match table definition.

Can anyone help me to fix it?

Upvotes: 1

Views: 132

Answers (4)

Manoj Gupta
Manoj Gupta

Reputation: 298

It simply based on your column name they should be of same type:

insert into tblA(col1,col2,col3)
select col1,col2,col3
from tblB

Upvotes: 0

Ken White
Ken White

Reputation: 125757

Just INSERT using named columns, and skip the identity column - it will be filled automatically:

INSERT INTO tblA (num, col1, col2, col3) SELECT @Num, col1, col2, col3 FROM tblB

Upvotes: 2

FIre Panda
FIre Panda

Reputation: 6637

Can you please try providing the column names as well,

declare @num...(same type as num)

insert into tblA(num, col1, col2, col3)
select @num, * from tblB

Please don't worry about identity column as it will get filled automatically.

Upvotes: 3

Massimiliano Peluso
Massimiliano Peluso

Reputation: 26737

I think the error message is quite explicative: the SELECT and the INSERT has to have the same number of columns.

in your case 

declare @num...(same type as num)

insert into tblA(num,col1, col2, col3)
select @num,col1, col2, col3  from tblB

if the key on tblA is not auto-generated you have to consider it in the INSERT

more info here

Upvotes: 0

Related Questions