how to insert data into table using query

Question

1.00/5 (1 vote)

See more:

i am using sql server 2008 R2.. i want to store data in sql table..i am facing a error in this syntax..

the error is ..
There are more 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.

I check for order of parameter and no. of paramtr in table and query..
please help..

thanks in advance

USE [SCJ]
GO
/****** Object: StoredProcedure [dbo].[spGetCHLDETAIL1] Script Date: 03/20/2014 22:30:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create>
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spGetCHLDETAIL1]
-- Add the parameters for the stored procedure here
@DistrictID VARCHAR(250),
@barcode VARCHAR(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@ChildTblName AS VARCHAR (125),
@FamilyTblName AS VARCHAR (125),
@vquery1 AS varchar(max),
@vQuery AS VARCHAR (max);

SET @ChildTblName = '[' + @DistrictID + 'Child_D' + ']'
SET @FamilyTblName = '[' + @DistrictID + 'Family_D' + ']'

VB

SET @vQuery = 'Child.distt,Child.block,Child.gpmctc,Child.village ,Child.habitation,Child.location, Child.residing,Child.mother_ton,Family.pincode,Family.hhno,Family.fname,Family.mname,Family.gname, Family.social_grp,Family.minority,Family.total_male,Family.total_fema,Family.seasonal_m,Family.migration_,Family.duration,Family.lp,Family.up,Family.sec,Family.catch_s_lp,Family.catch_s_up,Family.catch_s_se,
Child.cname, Child.sex,Child.dob, Child.disability,Child.sch_code,Child.class,Child.nereason,Child.droupout,Child.highclass
   FROM ' + @ChildTblName + ' As Child  WITH (NOLOCK) INNER JOIN ' + @FamilyTblName + ' AS Family WITH (NOLOCK) ON
   Child.barcode=Family.barcode WHERE LTRIM(RTRIM(Child.cname)) <> '''' AND Child.distt = ' + @DistrictID + '';

SQL

insert into CHLDINFO_LOG1
 (
     distic
     ,block
     ,GP
     ,village
     ,habitation
     ,location
     ,residing
     ,mother_ton
     ,Pincode
     ,House_no
     ,Father
     ,Mother
     ,Guardian
     ,social_grp
     ,Minority
     ,Male
     ,Female
     ,seasnal_mgrtn
     ,Mgrtn_Type
     ,durtn_Mgrtn
     ,LP
     ,UP
     ,SEC
     ,Ctchmnt_LP
     ,Ctchmnt_UP
     ,Ctchmnt_SEC
     ,Chld_Name
     ,SEX
     ,DOB
     ,Disblty_type
     ,Schl_CODE
     ,CLASS
     ,NEREASON
     ,DRPOUT_rsn
     ,HIGH_class
     ) values(@vQuery);

Posted 20-Mar-14 20:12pm

bindash

Add a Solution

2 solutions

Solution 2

Hai
u r try to insert less no of values when compare with column specified,

field name < values(@vQuery)
try to check what u get @vQuery, or first get @vQuery in one string,i meant select values and pass in @vQuery,don't pass select query directly to @vQuery.
just check @vQuery have 35 values or not

Posted 20-Mar-14 22:24pm

Aravindba

Comments

bindash 21-Mar-14 4:49am

I HAVE TRIED it's not working.. no. of parameter and order pf parameter is same..

Aravindba 21-Mar-14 4:53am

ok just try insert 2 values only,like 2 field name and 2 parameter only

bindash 21-Mar-14 4:56am

also not working 2 values also.. it's working for 1 value only.. if i place '@vquery' in values .. it's insert that column with @vquery ..

Aravindba 21-Mar-14 5:04am

hai actually what u get in @vQuery ? pls post values form @vQuery.
what u get values in @vQuery like this trichy,mainblock,hgp,townvillage....... upto 35 values ? with coma ?

bindash 21-Mar-14 6:33am

@vquery is working properly.. it is used to show data on a page.. it's working.. i want to store this data in CHILDINFO_LOG

Aravindba 21-Mar-14 6:39am

what did u show data in page or use that value in insert query in SP ?
ur try to insert query in ur SP like this
insert into CHLDINFO_LOG1 (distic,block.......,HIGH_class) values(@vQuery);
actually what u get in @vQuery ?
like this asd,ads,asd,assd..........upto 35 values or simply u get
only one values ?

just declare one variable and get values from @vQuery and pass declare variable to insert query.
don't pass select query (@vQuery) variable directly to insert query.
bcz u use @vQuery for select data,the select query in @vQuery not execute before insert query.

u need to pass correct field name and parameter,if any one more or less it show "match the number of columns specified in the INSERT statement"
so try get values form @vQuery and pass in another variable like @vvalue and pass this one in insert query.
@vuery=@vQuery
insert into CHLDINFO_LOG1 (distic,block.......,HIGH_class) values(@vuery);

Note :Before execute insert query u need to execute select query(@vQuery) first and passed to @vuery.then only u get 35 values in @vuery and can insert

bindash 21-Mar-14 6:43am

u r right.. i m try store this retrive data from @vquery in CHILDINFO_LOG.. I m getting 35 values from @vquery..

Aravindba 21-Mar-14 6:54am

ok declare new variable and pass @vQuery value to new variable,and use new variable in insert query.try like this
@vuery=@vQuery;
insert into CHLDINFO_LOG1 (distic,block.......,HIGH_class) values(@vuery);

can u post what values u get in @vQuery

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Vaselenko · Accepted Answer · 2014-03-20T22:01:00

Solution 1

Generate a complete INSERT statement in @vQuery and use exec (@vQuery) to execute the statement.

Pattern:

SQL

SET @vQuery = '
INSERT INTO CHLDINFO_LOG1
    (...)
SELECT
    ...
FROM
    ...
'

EXEC (@vQuery)

Posted 20-Mar-14 22:01pm

Sergey Vaselenko

Updated 20-Mar-14 22:36pm

v2

Comments

bindash 21-Mar-14 4:06am

i did't get it.. plz explain in detail.. plz plz..
i also try exec(@vquery).. it's also not working

Sergey Vaselenko 21-Mar-14 4:40am

Try to get a working SQL statement first.

bindash 21-Mar-14 6:29am

it's not working