如何使用查询将数据插入表中 [英] how to insert data into table using query

问题描述

我正在使用sql server 2008 R2 ..我想在sql table中存储数据..我在这种语法中遇到错误..





错误是..

INSERT语句中的列多于VALUES子句中指定的值。 VALUES子句中的值的数量必须与INSERT语句中指定的列数相匹配。



我检查参数的顺序和否。表和查询中的paramtr ..

请帮助..



提前感谢



USE [SCJ]

GO

/ ******对象：StoredProcedure [dbo]。[spGetCHLDETAIL1]脚本日期：03/20 / 2014 22:30:19 ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

- ===================================== ========

- 作者：< author ,, name>

- 创建日期：< create>

- 描述：< description ,,>

- =========================== ==================

ALTER PROCEDURE [dbo]。[spGetCHLDETAIL1]

- 为参数添加存储过程在这里

@DistrictID VARCHAR（250），

@barcode VARCHAR（50）



AS

BEGIN

- 添加SET NOCOUNT ON以防止来自

的额外结果集 - 干扰SELECT语句。

设置NOCOUNT ON;

DECLARE

@ChildTblName AS VARCHAR（125），

@FamilyTblName AS VARCHAR（125），

@ vquery1 AS varchar（ max），

@vQuery AS VARCHAR（最大）;





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

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

  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，Chi ld。 class ，Child.nereason，Child.droupout，Child.highclass 
 FROM ' < span class =code-comment> + @ChildTblName +'As Child WITH（NOLOCK）INNER JOIN'+ @ FamilyTblName +'AS Family WITH（NOLOCK）ON  
 Child.barcode = Family.barcode在哪里LTRIM（RTRIM（Child.cname））<> ''' 'AND Child.distt ='+ @DistrictID +'';  

  insert  进入 CHLDINFO_LOG1 
（
 distic 
，阻止
，GP 
，村庄
，居住
，地点
，居住
，mother_ton 
，Pincode 
，House_no 
，父亲
，母亲
，监护人
，social_grp 
，少数民族
，男性
，女性
，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 
）值（  @ vQuery ）; 

解决方案

在@vQuery中生成完整的INSERT语句并使用exec（@vQuery）来执行语句。



模式：

  SET   @ vQuery  = '  
 INSERT INTO CHLDINFO_LOG1 
（...）
 SELECT 
 ... 
 FROM 
 ... 
' 
 
  EXEC （ @ vQuery ）

Hai

ur在与指定的列比较时尝试插入较少的值，



字段名称<值（@vQuery）

尝试检查你得到@vQuery，或者首先在一个字符串中获取@vQuery，我的意思是选择值并传入@vQuery，不要直接将select查询传递给@ vQuery。

只检查@vQuery是否有35个值

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' + ']'

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 + '';

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);

解决方案

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

Pattern:

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

EXEC (@vQuery)

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

这篇关于如何使用查询将数据插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！