SQL Server Error 120 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Server Error 120

Author

Topic

Al-Ag
Starting Member

3 Posts

Posted - 2010-01-31 : 14:21:07

Hello,

I am getting the following error msg:
"Msg 120, Level 15, State 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
"

The code is:

-----------------------------------
declare @var varchar(100),
@id bigint,
@fname nvarchar(255),
@lname nvarchar(255),
@datemodified datetime

set @fname='John'
set @lname='Doak'
set @datemodified='06-01-2010'
set @id=11223344554

Set @var=convert( varchar(50),@id , 105)+','+char(39)+@fname+char(39)set @var=@var+','+char(39)+@lname + char(39)+','+char(39)+convert( set @var=@var+varchar(100),@datemodified , 105)+char(39)

insert into table1 values (ID,FName,LName, DateModified)
select @var

The table data definition:
ID bigint
FName nvarchar(255)
LName nvarchar(255)
DateModified Datetime

When I run the statement separate in different query window like this it works fine:

insert into table1 values (ID,FName,LName, DateModified)
select 11223344554,'John','Doak','01-06-2010'

Thanks
Al

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-31 : 14:51:19

don't even try to do it that way. just do this:

insert into table1 values (ID, FName, LName, DateModified)
select @id, @fname, @lname, @datemodified

or this

insert into table1 values (ID, FName, LName, DateModified)
Values(@id, @fname, @lname, @datemodified)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-31 : 14:55:17

This must be done using dynamic sql.
But I cannot see a reason for this...
Why not like this:
insert into table1 ~~values~~ (ID,FName,LName, DateModified)
select
@id,
@fname,
@lname,
@datemodified

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-31 : 14:56:33

Argh - a bit late

No, you're never too old to Yak'n'Roll if you're too young to die.

Al-Ag
Starting Member

3 Posts

Posted - 2010-01-31 : 14:57:54

Thanks for your reply.
The reason why I am doing that way because @var actually is coming from a parsed string:

the string is fomratted this way:
@str='11223344554-John-Doak-01/06/2010'

It is coming from another table that has a lot of records with same format. I am using a loop to go through @str so I parse the string (delimiter -) and put it in the format of an insert sql to be inserted in the table.

-Al

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-31 : 15:06:56

Then maybe like this?
insert into table1 (ID,FName,LName, DateModified)
select
<your parse command to extract id>,
<your parse command to extract fname>,
<your parse command to extract lname>,
<your parse command to extract datemodified>
from your_source_table

No, you're never too old to Yak'n'Roll if you're too young to die.

Al-Ag
Starting Member

3 Posts

Posted - 2010-01-31 : 16:38:56

Thanks a lot it helped.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-31 : 17:08:04

welcome