怎样把一个表中符合条件的所有列插入到另一个表中

[ccinlove]

——————————下面是要插入数据的数据源-------
SELECT @CJID = CJID,@XN = XN,@XQ = XQ,@YXSH = YXSH,@YXSMC = YXSMC,@ZYH = ZYH,@ZYMC = ZYMC,@ZYYWMC = ZYYWMC,@BH = BH,@BJMC = BJMC,@XJID = XJID,@XH = XH,@XM = XM,@KCID = KCID,@KCH = KCH,@KCMC = KCMC,@XS = XS,@XF = XF,@KHFS = KHFS,@JD = JD,@JSZGH = JSZGH,@JSXM = JSXM,@KSLX = KSLX,@CJLX = CJLX,@KSCJ = KSCJ,@PSCJ = PSCJ,@ZPCJ = ZPCJ,@BZ = BZ,@SFGD = SFGD,@SJBS = SJBS FROM [NHU_JWDB].[dbo].[XYL_CJB] WHERE XH = @XH
----------下面是对要插入的某些数据进行处理
SELECT @XB = XB FROM dbo.XYL_XYB WHERE XJID = @XJID
SELECT @KSLXM = KSLXM FROM dbo.DM_KSLX WHERE KSLXMC = @KSLX
SELECT @下面是插入数据----
INSERT INTO [NHU_JWDB].[dbo].[CJ_XSCJB] ([CJID],[XN],[XQ],[YXSH],[YXSMC],[ZYH],[ZYMC],[ZYYWMC],[BH],[BJMC],[XJID],[XH],[XM],[XB],[KCID],[KCH],[KCMC],[XS],[XF],[JD],[KHFS],[KSLXM],[KSLX],[CJLXM],[CJLX],[PSCJ],[KSCJ],[ZPCJ],[BZ],[SFGD],[SJBS],[JSZGH],[JSXM]) VALUES (NEWID(),@XN,@XQ,@YXSH,@YXSMC,@ZYH,@ZYMC,@ZYYWMC,@BH,@BJMC,@XJID,@XH,@XM,@XB,@KCID,@KCH,@KCMC,@XS,@XF,@JD,@KHFS,@KSLXM
,@KSLX,@CJLXM,@CJLX,@PSCJ,@KSCJ,@ZPCJ,@BZ,@SFGD,@SJBS,@JSZGH,@JSXM)


我遇到的困难就是如何把[NHU_JWDB].[dbo].[XYL_CJB]中符合条见的列插入到是插入数据----
INSERT INTO [NHU_JWDB].[dbo].[CJ_XSCJB]中，我是第一次提问，也是刚刚熟悉数据库，请各位高手指教！

[中国风]

用
if exists(...)判断
insert ....插入

[-狙击手-]

SELECT @XB = XB FROM dbo.XYL_XYB WHERE XJID = @XJID 
SELECT @KSLXM = KSLXM FROM dbo.DM_KSLX WHERE KSLXMC = @KSLX  

INSERT INTO [NHU_JWDB].[dbo].[CJ_XSCJB] ([CJID],[XN],[XQ],[YXSH],
[YXSMC],[ZYH],[ZYMC],[ZYYWMC],[BH],[BJMC],[XJID],[XH],[XM],[XB],[KCID],[KCH],[KCMC],
[XS],[XF],[JD],[KHFS],[KSLXM],[KSLX],[CJLXM],[CJLX],[PSCJ],[KSCJ],[ZPCJ],[BZ],[SFGD],[SJBS],
[JSZGH],[JSXM]) SELECT CJID,XN, XQ,YXSH,YXSMC,ZYH,ZYMC,ZYYWMC,BH,@XB, BJMC,XJID,XH,XM,
KCID,KCH,KCMC, XS,XF,KHFS,@KSLXM, JD,JSZGH,JSXM,KSLX,CJLX,KSCJ,PSCJ,ZPCJ,BZ,SFGD,SJBS 
FROM [NHU_JWDB].[dbo].[XYL_CJB] WHERE XH = @XH 

[huangqing_80]

[Quote=引用 2 楼 happyflystone 的回复:]
SQL codeSELECT @XB = XB FROM dbo.XYL_XYB WHERE XJID = @XJID
SELECT @KSLXM = KSLXM FROM dbo.DM_KSLX WHERE KSLXMC = @KSLX

INSERT INTO [NHU_JWDB].[dbo].[CJ_XSCJB] ([CJID],[XN],[XQ],[YXSH],
[YXSMC],[ZYH],[ZYMC],[ZYYWMC],[BH],[BJMC],[XJID],[XH],[XM],[XB],[KCID],[KCH],[KCMC],
[XS],[XF],[JD],[KHFS],[KSLXM],[KSLX],[CJLXM],[CJLX],[PSCJ],[KSCJ],[ZPCJ],[BZ],[SFGD],[SJBS],
[JSZGH],[JSXM]) SELECT C…
[/Quote]

[dawugui]

用if判断.
或是在结尾加where 条件判断.

[newtown555]

INSERT INTO [NHU_JWDB].[dbo].[CJ_XSCJB] ([CJID],[XN],[XQ],[YXSH],[YXSMC],[ZYH],[ZYMC],[ZYYWMC],[BH],[BJMC],[XJID],[XH],[XM],[XB],[KCID],[KCH],[KCMC],[XS],[XF],[JD],[KHFS],[KSLXM],[KSLX],[CJLXM],[CJLX],[PSCJ],[KSCJ],[ZPCJ],[BZ],[SFGD],[SJBS],[JSZGH],[JSXM]) 
select NEWID(),a.XN,a.XQ,a.YXSH,a.YXSMC,a.ZYH,a.ZYMC,a.ZYYWMC,a.BH,a.BJMC,a.XJID,a.XH,a.XM,b.XB,a.KCID,a.KCH,a.KCMC,a.XS,a.XF,a.JD,
a.KHFS,c.KSLXM,a.KSLX,a.CJLXM,a.CJLX,a.PSCJ,a.KSCJ,a.ZPCJ,a.BZ,a.SFGD,a.SJBS,a.JSZGH,a.JSXM 
from [NHU_JWDB].[dbo].[XYL_CJB] a inner join dbo.XYL_XYB b on a.XJID=b.XJID 
inner join dbo.DM_KSLX c on a.kslx=c.KSLXMC
where XH = @XH

[newtown555]

INSERT INTO [NHU_JWDB].[dbo].[CJ_XSCJB] ([CJID],[XN],[XQ],[YXSH],[YXSMC],[ZYH],[ZYMC],[ZYYWMC],[BH],[BJMC],[XJID],[XH],[XM],[XB],[KCID],[KCH],[KCMC],[XS],[XF],[JD],[KHFS],[KSLXM],[KSLX],[CJLXM],[CJLX],[PSCJ],[KSCJ],[ZPCJ],[BZ],[SFGD],[SJBS],[JSZGH],[JSXM]) 
select NEWID(),a.XN,a.XQ,a.YXSH,a.YXSMC,a.ZYH,a.ZYMC,a.ZYYWMC,a.BH,a.BJMC,a.XJID,a.XH,a.XM,b.XB,a.KCID,a.KCH,a.KCMC,a.XS,a.XF,a.JD,
a.KHFS,c.KSLXM,a.KSLX,a.CJLXM,a.CJLX,a.PSCJ,a.KSCJ,a.ZPCJ,a.BZ,a.SFGD,a.SJBS,a.JSZGH,a.JSXM 
from [NHU_JWDB].[dbo].[XYL_CJB] a inner join dbo.XYL_XYB b on a.XJID=b.XJID 
inner join dbo.DM_KSLX c on a.kslx=c.KSLXMC
where XH = @XH 

[xiaozhu1986]

学习关注中！

[ccinlove]

谢谢NEWTOWN555

[ccinlove]

问题以解决，以后大家遇到同样的问题，可以采用NEWTOWN555的建议，谢谢大家！

[ccinlove]

谢谢大家的帮忙哈

[panxuan]

[Quote=引用 2 楼 happyflystone 的回复:]
SQL codeSELECT @XB = XB FROM dbo.XYL_XYB WHERE XJID = @XJID
SELECT @KSLXM = KSLXM FROM dbo.DM_KSLX WHERE KSLXMC = @KSLX

INSERT INTO [NHU_JWDB].[dbo].[CJ_XSCJB] ([CJID],[XN],[XQ],[YXSH],
[YXSMC],[ZYH],[ZYMC],[ZYYWMC],[BH],[BJMC],[XJID],[XH],[XM],[XB],[KCID],[KCH],[KCMC],
[XS],[XF],[JD],[KHFS],[KSLXM],[KSLX],[CJLXM],[CJLX],[PSCJ],[KSCJ],[ZPCJ],[BZ],[SFGD],[SJBS],
[JSZGH],[JSXM]) SELECT C…
[/Quote]

[ccinlove]

6楼你提的建议我用了一下，但现在插入的记录是0条，不知道怎么回事啊

[newtown555]

用了内联接,所以如果你的

SELECT @XB = XB FROM dbo.XYL_XYB WHERE XJID = @XJID
SELECT @KSLXM = KSLXM FROM dbo.DM_KSLX WHERE KSLXMC = @KSLX 

这两句没有值得话,上面的语句就没有记录.
如果不管有没有值都要插入的话,就把插入语句中的inner join 全部改成了 left join

[pt1314917]

---try:

insert into [NHU_JWDB].[dbo].[CJ_XSCJB]([CJID],[XN],[XQ],[YXSH],[YXSMC],[ZYH],[ZYMC],[ZYYWMC],[BH],[BJMC],[XJID],[XH],[XM],[XB],[KCID],[KCH],[KCMC],[XS],[XF],[JD],[KHFS],[KSLXM],[KSLX],[CJLXM],[CJLX],[PSCJ],[KSCJ],[ZPCJ],[BZ],[SFGD],[SJBS],[JSZGH],[JSXM]) 
select newid(),a.XN,a.XQ,a.YXSH,a.YXSMC,a.ZYH,a.ZYMC,a.ZYYWMC,a.BH,a.BJMC,a.XJID,a.XH,a.XM,b.XB,a.KCID,a.KCH,a.KCMC,a.XS,a.XF,
a.JD,a.KHFS,c.KSLXM ,a.KSLX,a.CJLXM,a.CJLX,a.PSCJ,a.KSCJ,a.ZPCJ,a.BZ,a.SFGD,a.SJBS,a.JSZGH,a.JSXM
from [NHU_JWDB].[dbo].[XYL_CJB] a,XYL_XYB b,DM_KSLX c
where a.xh=@xh and a.XJID=b.XJID and a.KSLX=c.KSLXMC

[flycbx]

学习关注中！