利用SQL查询插入数据

最新推荐文章于 2024-08-01 09:46:22 发布

huang7914

最新推荐文章于 2024-08-01 09:46:22 发布

阅读量9.2k

点赞数 1

本文链接：https://blog.csdn.net/huang7914/article/details/4676251

版权

如何将sql查询结果（不止一项）插入到一张表中，我们一般可以用如下形式的sql

insert into table1

select field1,field2,... from table2

where ...

它要求对应的插入项类型必须一致。

另外，当select的查询结果是table1的所有字段的时候，可以省略table1的列字段，写成如下形式：

insert into table1(field1,field2,...)

select field1,field2,... from table2

where ...

在实际当中我们可能遇到进行表单填报数据时,这时表单里面的值是变量

strSql.Append("insert into tny_surveyArea(");
                        strSql.Append("surveyAreaId,TaskID,biologyId,biologyType,generationId,wormAgeId,statusId,damageCodeId,occurArea,deadTreeCount,Remark,SurveyTypeId,SurveyDate,surveyUserID,inputUserId,CheckStatus,GPSCoor,IsCheckData,SmallFieldID,Geometry, Geometry_XLO, Geometry_YLO, Geometry_XHI, Geometry_YHI");
                        strSql.Append(")");
                        strSql.Append(" select ");
                        strSql.Append("'" + model.surveyAreaId + "',");
                        strSql.Append("" + model.TaskID + ",");
                        strSql.Append("'" + model.biologyId + "',");
                        strSql.Append("'" + model.biologyType + "',");
                        strSql.Append("'" + model.generationId + "',");
                        strSql.Append("'" + model.wormAgeId + "',");
                        strSql.Append("'" + model.statusId + "',");
                        strSql.Append("" + model.damageCodeId + ",");
                        strSql.Append("" + model.occurArea + ",");
                        strSql.Append("" + model.deadTreeCount + ",");
                        strSql.Append("'" + model.Remark + "',");
                        strSql.Append("" + model.SurveyTypeId + ",");
                        strSql.Append("'" + model.SurveyDate + "',");
                        strSql.Append("'" + model.surveyUserID + "',");
                        strSql.Append("'" + model.inputUserId + "',");
                        strSql.Append("" + model.CheckStatus + ",");
                        strSql.Append("'" + model.GPSCoor + "',");
                         strSql.Append("" + model.IsCheckData + ",");
                        strSql.Append("'" + model.SmallFieldID + "',");
                        string temp = " Geometry,Geometry_XLO, Geometry_YLO, Geometry_XHI, Geometry_YHI from tr_SmallField WHERE smallFieldId = '{0}' ";
                        temp = string.Format(temp, model.SmallFieldID);
                        strSql.Append(temp);
                        strSql.Append(";");

当执行到这里时,

然后就变成了这里面 model数值就成了常量;

insert into tny_surveyArea
(surveyAreaId,
   TaskID,
   biologyId,
   biologyType,
   generationId,
   wormAgeId,
   statusId,
   damageCodeId,
   occurArea,
   deadTreeCount,
   Remark,
   SurveyTypeId,
   SurveyDate,
   surveyUserID,
   inputUserId,
   CheckStatus,
   GPSCoor,
   IsCheckData,
   SmallFieldID,
   Geometry,
   Geometry_XLO,
   Geometry_YLO,
   Geometry_XHI,
   Geometry_YHI)
select 'ebd83ca8a3564823a8d5d0effedaf30f',
         1,
         '402001',
         '虫',
         '3fbecf367f8e4b3c864cebd9c9d3fbce',
         'd7f9779d711f460c88d0c3b17d0218d7',
         '9f649e78d499469bbc8ccc45972a50c2',
         3,
         231,
         0,
         '213',
         7,
         '2009-10-15 16:26:07',
         '7075DC89F0T2D886E040A8C065A71E70',
         '7075DC89F0T2D886E040A8C065A71E70',
         0,
         '',
         0,
         '3070',
         Geometry,
         Geometry_XLO,
         Geometry_YLO,
         Geometry_XHI,
         Geometry_YHI
     from tr_SmallField
   WHERE smallFieldId = '3070';