• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

only want to insert values into one column

only want to insert values into one column

insert into tablename (tablecolumn) values ('a','b','c','d')

Msg 110, Level 15, State 1, Line 1
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
0
rgb192
Asked:
rgb192
  • 4
  • 3
  • 3
  • +2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
insert into tablename (tablecolumn) values ( 'd')
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you are trying to insert 4 different values but in you taget columnlist there is only one column
0
 
rgb192Author Commented:
i have many values written like
 ('a','b','c','d')


how can i insert them
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Neil RussellTechnical Development LeadCommented:
You need 4 inserts or a loop
0
 
zadeveloperCommented:
insert into tablename (tablecolumn) values ( 'a')
insert into tablename (tablecolumn) values ( 'b')
insert into tablename (tablecolumn) values ( 'c')
insert into tablename (tablecolumn) values ( 'd')

:) - will this not work for you ?
0
 
Neil RussellTechnical Development LeadCommented:
Like i said, you need 4 inserts :D Or a loop
0
 
zadeveloperCommented:
:) - sorry - we must have posted around the same time.

If you really wanted to go crazy you your could do this:

insert into tablename
	SELECT MyValues
	FROM 
		(select 'a' as A, 'b' as B,'c' as C,'d' as D) source -- here is your ('a', 'b', 'c', 'd')
	UNPIVOT
	   (MyValues FOR I IN 
		  (a, b, c, d)
	)AS pTable

Open in new window

0
 
jaan33Commented:

try this on sql server 2008:

insert into tablename (tablecolumn)
values ('a')
,('b')
,('c')
,('d')








0
 
rgb192Author Commented:
insert into tablename
        SELECT MyValues
        FROM  
                (select 'a' as A, 'b' as B,'c' as C,'d' as D) source -- here is your ('a', 'b', 'c', 'd')
        UNPIVOT
           (MyValues FOR I IN  
                  (a, b, c, d)
        )AS pTable

does this work for more values than a b c d

I have alot to insert
0
 
Neil RussellTechnical Development LeadCommented:
If you have "A lot" to insert, what is the source of the data?
 
0
 
zadeveloperCommented:
yea it will.

It would help if we knew where the data were comming from - sounds like a select of other tables ?

0
 
zadeveloperCommented:
ok - so this is what you would do in that case (when you have lots of stuff)

At the moment I assume that your values come from some select statement that puts everything in a row like so:

select 'a' as a, 'b' as b, 'c' as c, 'd' as d, 'e' as e, 'f' as f, 'h' as h into from OtherTables t1 inner join SomeOMoreTables t2 on t1.a=t2.a

if so this is would I would do


--First put all that stuff into 1 temp table
select 'a' as a, 'b' as b, 'c' as c, 'd' as d, 'e' as e, 'f' as f, 'h' as h into #temp1 from OtherTables t1 inner join SomeOMoreTables t2 on t1.a=t2.a

insert into tablename
        SELECT 
			MyValues
        FROM  
			(select * from 
				(select  'a' as a, 'b' as b, 'c' as c, 'd' as d, 
						 'e' as e, 'f' as f, 'h' as h from OtherTables t1 inner join SomeOMoreTables t2 on t1.a=t2.a)
			)as source
        UNPIVOT
           (MyValues FOR I IN  
                  (a, b, c, d, e, f, h) -- select the fields you want (can just keep adding as many as you want - as long as they were in the temp1 table)
        )AS pTable
        
    drop table #temp1

Open in new window

0
 
rgb192Author Commented:
thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now