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

Question

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

insert into tblTable1 (
  [Week],
  20001,
  20002,
  20003,
  20004,
  20006,
  20005,
  W/c
)
select *
from tblTable1_link (
  [Week],
  20001,
  20002,
  20003,
  20004,
  20006,
  20005,
  W/c
)

I am getting the following error :

Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '20001'. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'Week'.

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

Please see the structure below:

sp_columns tblTable1_link

database_name   USERACCOUNT tblTable1 Week 4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 1 YES 38
database_name   USERACCOUNT tblTable1 20001 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 2 YES 39
database_name   USERACCOUNT tblTable1 20002 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 3 YES 39
database_name   USERACCOUNT tblTable1 20003 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 4 YES 39
database_name   USERACCOUNT tblTable1 20004 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 5 YES 39
database_name   USERACCOUNT tblTable1 20006 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 6 YES 39
database_name   USERACCOUNT tblTable1 20005 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 7 YES 39
database_name   USERACCOUNT tblTable1 W/c 11 smalldatetime 16 16 0 NULL 1 NULL NULL 9 3 NULL 8 YES 111

database_name   dbo tblTable1_Link Week 4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 1 YES 38
database_name   dbo tblTable1_Link 20001 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 2 YES 39
database_name   dbo tblTable1_Link 20002 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 3 YES 39
database_name   dbo tblTable1_Link 20003 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 4 YES 39
database_name   dbo tblTable1_Link 20004 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 5 YES 39
database_name   dbo tblTable1_Link 20006 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 6 YES 39
database_name   dbo tblTable1_Link 20005 -9 nvarchar 255 510 NULL NULL 1 NULL NULL -9 NULL 510 7 YES 39
database_name   dbo tblTable1_Link W/c 11 smalldatetime 16 16 0 NULL 1 NULL NULL 9 3 NULL 8 YES 111

Please help! I cannot drop the source table, i want to insert data without droping

This query resulted :

select * into tblTable from tblTable_Link

Error:

Server: Msg 2714, Level 16, State 6, Line 1 There is already an object named 'tblTable' in the database.

Answer 1

You have to reference column names that are numbers with brackets:

INSERT INTO tblTable1 (
  [Week],
  [20001],
  [20002],
  [20003], 
  etc

Additionally, you should not use SELECT * in a subquery for an insert statement. Enumerate the columns to be sure they're in the right order for the insert.

Also I have no idea what you're trying to accomplish with the "FROM tblTable_Link ([Week],etc" part. That is not valid SQL syntax. If you're trying to select only those columns from the table, they come after the SELECT keyword, and before the FROM keyword.

Answer 2

insert into tblTable1(
[Week]
,[20001]
,[20002]
,[20003]
,[20004]
,[20006]
,[20005]
,[W/c])
select [Week]
,[20001]
,[20002]
,[20003]
,[20004]
,[20006]
,[20005]
,[W/c]
 from tblTable1_link

Raj