CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Jaison
Jaison

Reputation:

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

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

I cannot drop the source table, I want to insert data without dropping

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.

Upvotes: 0

Views: 6888

Answers (2)

Welbog
Welbog

Reputation: 60418

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.

Upvotes: 2

Raj
Raj

Reputation: 10853

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

Upvotes: 2

Related Questions