一个表的数据插入到另一个表

来源：互联网发布：c语言中default 编辑：程序博客网时间：2024/05/16 09:53

create proc YSCX_insert
@SCUserId int ,
@SCYY nvarchar(500),
@JMBH varchar(50) ,
@result int output
as
declare @sqlInsert varchar(500),
          @sqlDelete varchar(500)
begin tran
select @sqlInsert += 'insert into dbo.YSCX select JMBH, WJH, LWDW, LWRQ, BLSX, MJ, SFGD, GDLB, UserId, LRSJ, WJBT, SJDH, YFDH, SFBJ, BJSJ, NGR, QFR,'+CONVERT(varchar(20),@SCUserId)+',GETDATE(),'+@SCYY+' from dbo.WJDJ where ID in ('+@JMBH+')'
exec (@sqlInsert)
select @sqlDelete ='delete WJDJ where JMBH in ('+@JMBH+')'
exec (@sqlDelete)
if @@ERROR <> 0
begin
     rollback tran
  set @result = 0
  return
end
    else
     begin
       commit tran
       set @result = 1
     end
go

1.如果insert into 表名后面没有列名，那么就需要插入所有的数据，如上面所示

2.然后insert into 表名后面有列名，那么就只需要插入所需的列名

insert into dbo.YDZX ( factoryName, companyName, HGZCDJBM, ZXPZSJ, BZ, DQ, QYXZ, LXR, LXDH, scsj, scyh) select factoryName, companyName, HGZCDJBM, ZXPZSJ, BZ, DQ, QYXZ, LXR, LXDH ,GETDATE() ,305 from dbo.YDZX_test