views:

1226

answers:

3

In SQL server, I am trying to insert values from one table to another by using the below query :

delete from tblTable1

insert into tblTable1 select * from tblTable1_Link

I am getting the following error :

Column name or number of supplied values does not match table definition.

I am sure that both table having same structure,column names and same data type:

Please help!

+3  A: 

They don't have the same structure... I can guarantee they are different

I know you've already created it... There is already an object named ‘tbltable1’ in the database

What you may want is this (which also fixes your other issue):

Drop table tblTable1

select * into tblTable1 from tblTable1_Link
gbn
+1  A: 

for inserts it is always better to specify the column names see the following

DECLARE @Table TABLE(
     Val1 VARCHAR(MAX)
)

INSERT INTO @Table SELECT '1'

works fine, changing the table def to causes the error

DECLARE @Table TABLE(
     Val1 VARCHAR(MAX),
     Val2 VARCHAR(MAX)
)

INSERT INTO @Table SELECT '1'

Msg 213, Level 16, State 1, Line 6 Insert Error: Column name or number of supplied values does not match table definition.

But changing the above to

DECLARE @Table TABLE(
     Val1 VARCHAR(MAX),
     Val2 VARCHAR(MAX)
)

INSERT INTO @Table (Val1)  SELECT '1'

works. You need to be more specific with the columns specified

supply the structures and we can have a look

astander
A: 

the column prefix does not match with a table name or alias name used in the query.

I was also working to solve this error

Nw i found why it is comming just needs to make small changes in query

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html