USE [culcleasing]
GO
/****** Object: StoredProcedure [dbo].[copy_table] Script Date: 07/09/2018 23:16:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
--将从临时表导到计划表中
-- =============================================
ALTER PROCEDURE [dbo].[copy_table]
@tableA varchar(50), --数据来源表
@fromCondtion varchar(200), --查询来源表的条件
@tableB varchar(50), --目标数据表
@toCondtion varchar(200), --删除目标数据表记录的条件
@ExcludeField varchar(200), --排除的列名
@OtherColumn varchar(500), --其他列名
@OtherValue varchar(500) --其他列名对应的列值
AS
BEGIN
declare @toColumn as varchar(3900);
declare @CopySql as varchar(8000);
declare @DeleteSql as varchar(200);
if(datalength(@toCondtion)>2)
begin
set @DeleteSql='delete from '+@tableB +' where '+@toCondtion
--删除原数据
exec(@DeleteSql)
end
print @DeleteSql
select @toColumn= named from (
select [named]=stuff((select ','+ A.name from syscolumns A
inner join syscolumns A1 on A.name=A1.name
where A.id=B.id
and charindex(A.name,@ExcludeField)=0
and A1.id=object_id(@tableA) --排除来源表没有的列名
and A.name in (select name from syscolumns where id=object_id(@tableA))--排除来源表没有的列名
for xml path('')), 1, 1, '')
from sysobjects B
where id = object_id(@tableB)
) a
print @toColumn+@OtherColumn
set @CopySql='insert into '+@tableB +'('+@toColumn+@OtherColumn+') select '+@toColumn+@OtherValue+' from '+ @tableA +' where '+@fromCondtion
--插入新数据
print @CopySql
exec(@CopySql)
END
GO
/****** Object: StoredProcedure [dbo].[copy_table] Script Date: 07/09/2018 23:16:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
--将从临时表导到计划表中
-- =============================================
ALTER PROCEDURE [dbo].[copy_table]
@tableA varchar(50), --数据来源表
@fromCondtion varchar(200), --查询来源表的条件
@tableB varchar(50), --目标数据表
@toCondtion varchar(200), --删除目标数据表记录的条件
@ExcludeField varchar(200), --排除的列名
@OtherColumn varchar(500), --其他列名
@OtherValue varchar(500) --其他列名对应的列值
AS
BEGIN
declare @toColumn as varchar(3900);
declare @CopySql as varchar(8000);
declare @DeleteSql as varchar(200);
if(datalength(@toCondtion)>2)
begin
set @DeleteSql='delete from '+@tableB +' where '+@toCondtion
--删除原数据
exec(@DeleteSql)
end
print @DeleteSql
select @toColumn= named from (
select [named]=stuff((select ','+ A.name from syscolumns A
inner join syscolumns A1 on A.name=A1.name
where A.id=B.id
and charindex(A.name,@ExcludeField)=0
and A1.id=object_id(@tableA) --排除来源表没有的列名
and A.name in (select name from syscolumns where id=object_id(@tableA))--排除来源表没有的列名
for xml path('')), 1, 1, '')
from sysobjects B
where id = object_id(@tableB)
) a
print @toColumn+@OtherColumn
set @CopySql='insert into '+@tableB +'('+@toColumn+@OtherColumn+') select '+@toColumn+@OtherValue+' from '+ @tableA +' where '+@fromCondtion
--插入新数据
print @CopySql
exec(@CopySql)
END