动态表名的存储过程

zxdpxl 2015-09-29 04:50:35

我建立一个存储过程，以下是我写的存储过程。这样写存储过程我每增加一个表，就必须在存储过程中添加一条记录，这样不利于维护。我想改进这个存储过程，建立一个动态表名的存储过程。数据库的表名都是以‘usb’开头的。

ALTER PROCEDURE [dbo].[SPN]
(
@value VARCHAR(10)
)
AS
begin
truncate table alldata
insert into Alldata select * FROM USB1st WHERE PN=@value
insert into Alldata select * FROM USB2nd WHERE PN=@value
insert into Alldata select * FROM USB3rd WHERE PN=@value
insert into Alldata select * FROM USB4th WHERE PN=@value
insert into Alldata select * FROM USB5th WHERE PN=@value
insert into Alldata select * FROM USB6th WHERE PN=@value
insert into Alldata select * FROM USB7th WHERE PN=@value
insert into Alldata select * FROM USB8th WHERE PN=@value
insert into Alldata select * FROM USB9th WHERE PN=@value
insert into Alldata select * FROM USB10th WHERE PN=@value
insert into Alldata select * FROM USB11th WHERE PN=@value
insert into Alldata select * FROM USB12th WHERE PN=@value
insert into Alldata select * FROM USB13th WHERE PN=@value
insert into Alldata select * FROM USB14th WHERE PN=@value
insert into Alldata select * FROM USB15th WHERE PN=@value
insert into Alldata select * FROM USB16th WHERE PN=@value
insert into Alldata select * FROM USB17th WHERE PN=@value
insert into Alldata select * FROM USB18th WHERE PN=@value
insert into Alldata select * FROM USB19th WHERE PN=@value
insert into Alldata select * FROM USB20th WHERE PN=@value
insert into Alldata select * FROM USB21st WHERE PN=@value
insert into Alldata select * FROM USB22nd WHERE PN=@value
insert into Alldata select * FROM USB23rd WHERE PN=@value
insert into Alldata select * FROM USB24th WHERE PN=@value
insert into Alldata select * FROM USB25th WHERE PN=@value
insert into Alldata select * FROM USB26th WHERE PN=@value
insert into Alldata select * FROM USB27th WHERE PN=@value
insert into Alldata select * FROM USB28th WHERE PN=@value
insert into Alldata select * FROM USB29th WHERE PN=@value
insert into Alldata select * FROM USB30th WHERE PN=@value
insert into Alldata select * FROM USB31th WHERE PN=@value
insert into Alldata select * FROM USB32th WHERE PN=@value
insert into Alldata select * FROM USB33th WHERE PN=@value
insert into Alldata select * FROM USB34th WHERE PN=@value
insert into Alldata select * FROM USB35th WHERE PN=@value
insert into Alldata select * FROM USB36th WHERE PN=@value
insert into Alldata select * FROM USB37th WHERE PN=@value
insert into Alldata select * FROM USBHDD WHERE PN=@value
insert into Alldata select * FROM USBList1 WHERE PN=@value
insert into Alldata select * FROM USBList2 WHERE PN=@value
insert into Alldata select * FROM USBList3 WHERE PN=@value
end

...全文

391 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

等不到来世 2015-10-19

打赏
举报

引用 10 楼 zxdpxl 的回复:

[quote=引用 8 楼 szx1999 的回复:]


--要多加一对单引号...
declare @command1 nvarchar(4000)
set @command1='insert into Alldata select * FROM ? WHERE PN='''+rtrim(@value)+''''

exec sp_MSforeachtable @command1=@command1,
@whereand="and o.name like 'USB%'"

非常感谢您的存储过程。我有一个小问题我想把Where后面的（ PN='''+rtrim(@value)+''''）替换成（Check_MD5=@value or Check_MD5='0x'+@value）修。应该如何修改。谢谢！ [/quote]

declare @command1 nvarchar(4000)
set @command1='insert into Alldata select * FROM ? WHERE Check_MD5='''+rtrim(@value)+''' or Check_MD5=''0x'+rtrim(@value)+''''
 
exec sp_MSforeachtable @command1=@command1,
@whereand="and o.name like 'USB%'"

zxdpxl 2015-10-16

打赏
举报

引用 8 楼 szx1999 的回复:


--要多加一对单引号...
declare @command1 nvarchar(4000)
set @command1='insert into Alldata select * FROM ? WHERE PN='''+rtrim(@value)+''''

exec sp_MSforeachtable @command1=@command1,
@whereand="and o.name like 'USB%'"

非常感谢您的存储过程。我有一个小问题我想把Where后面的（ PN='''+rtrim(@value)+''''）替换成（Check_MD5=@value or Check_MD5='0x'+@value）修。应该如何修改。谢谢！

Haytor 2015-09-30

打赏
举报

今天有空我这里写个了通用版本的，可以参考一下。



IF OBJECT_ID('Pr_InsertData','P') IS NULL
	EXEC('CREATE PROCEDURE Pr_InsertData AS RETURN')
GO

ALTER PROCEDURE Pr_InsertData(
@TargetTable VARCHAR(128) = 'Test',		    -- 目标表
@SourceTable VARCHAR(MAX) = 'Test1;Test2',  -- 源表列表，表之间';'分割
@nType       INT = 0,						-- 1精确匹配,0模糊匹配
@HasIdentity TINYINT = 1,                   -- 是否包含自增字段
@TargetDB	 VARCHAR(128) = NULL,			-- 目标数据库
@SourceDB    VARCHAR(128) = NULL			-- 源数据库
)
AS
BEGIN
	SET NOCOUNT ON

	SET @TargetDB = ISNULL(@TargetDB,DB_NAME())
	SET @SourceDB = ISNULL(@SourceDB,DB_NAME())

    DECLARE 
		@sql		NVARCHAR(MAX), 
		@Parm       NVARCHAR(MAX),
		@ColumnList NVARCHAR(MAX),
		@UpdateList NVARCHAR(MAX),
		@TableList  XML,
		@TableName  VARCHAR(128),
		@RowID		INT,
		@RowCount	INT

	
	SET @TableList = '<row>' + REPLACE(@SourceTable,';','</row><row>')+'</row>'

	SELECT 
		T.c.value('.','nvarchar(max)') AS TableName
	INTO
		#SourceList
	FROM 
		@TableList.nodes('row') T(c)

	SELECT 
		IDENTITY(INT,1,1) AS RowID, 
		a.Name
	INTO
		#TableList
	FROM 
		sys.tables  a, #SourceList b
	WHERE 
		name <> @TargetTable
	AND (
			( @nType = 1 AND a.name = b.TableName) 
		 OR ( @nType = 0 AND a.name LIKE '%' + b.TableName + '%')
		)
    
	SELECT @RowCount = @@ROWCOUNT, @RowID = 1

	WHILE @RowID <= @RowCount
	BEGIN

		SELECT @TableName = name FROM #TableList WHERE RowID = @RowID

		SET @ColumnList = ''
		SET @UpdateList = ''
		SET @SQL = 'SELECT '
					+ '		@ColumnList = CASE WHEN @ColumnList = '''' THEN '''' ELSE @ColumnList + '','' END  + b.name, '
					+ '		@UpdateList = CASE WHEN @UpdateList = '''' THEN '''' ELSE @UpdateList + '','' END  + ''U.'' + b.name + ''=I.'' + b.name'
					+ '  FROM ' + @TargetDB + '.sys.tables a WITH(NOLOCK) INNER JOIN ' + @TargetDB + '.sys.columns b WITH(NOLOCK) ON a.object_id = b.object_id '
					+ ' WHERE a.name = ''' + @TableName + ''' '
					+ '   AND a.type=''U'' '
					+ '   AND b.is_computed = 0 '
					+ CASE WHEN @HasIdentity = 0 THEN '   AND b.is_identity = 0 ' ELSE '' END
					+ '   AND b.name IN (SELECT bb.name '
					+ '			        FROM ' + @SourceDB + '.sys.tables aa WITH(NOLOCK) '
					+ '		           INNER JOIN ' + @SourceDB + '.sys.columns bb WITH(NOLOCK) ON aa.object_id = bb.object_id ' 
					+ '		           WHERE aa.name = ''' + @TableName + ''' AND aa.type=''U'' '
					+ CASE WHEN @HasIdentity = 0 THEN '                   AND   bb.is_identity = 0 '  ELSE '' END
					+ '                   AND   bb.is_computed = 0 '
					+ '                  ) '
					+ ' ORDER BY b.column_id'
		
		SET @Parm = '@ColumnList NVARCHAR(MAX) OUTPUT,@UpdateList NVARCHAR(MAX) OUTPUT'
		EXEC sp_executesql @SQL,@Parm,@ColumnList = @ColumnList OUTPUT,@UpdateList = @UpdateList OUTPUT

		SET @sql = CASE WHEN @RowID = 1 THEN ' TRUNCATE TABLE ' + @TargetDB + '.dbo.' + @TargetTable + CHAR(13) + CHAR(10) ELSE '' END + CHAR(13) + CHAR(10)
				 + ' INSERT ' + @TargetDB + '.dbo.' + @TargetTable + '( ' + @ColumnList + ' ) ' + CHAR(13) + CHAR(10)
				 + ' SELECT ' + @ColumnList + CHAR(13) + CHAR(10)
				 + ' FROM '   + @SourceDB + '.dbo.' + @TableName + CHAR(13) + CHAR(10)
		PRINT @sql
        EXEC(@sql) 

		SET @RowID = @RowID + 1
	END
    
END
GO

等不到来世 2015-09-30

打赏
举报

declare @command1 nvarchar(4000)
set @command1='insert into Alldata select * FROM ? WHERE PN='+rtrim(@value)

exec sp_MSforeachtable @command1=@command1,
@whereand="and o.name like 'USB%'"

等不到来世 2015-09-30

打赏
举报


--要多加一对单引号...
declare @command1 nvarchar(4000)
set @command1='insert into Alldata select * FROM ? WHERE PN='''+rtrim(@value)+''''

exec sp_MSforeachtable @command1=@command1,
@whereand="and o.name like 'USB%'"

zxdpxl 2015-09-30

打赏
举报

引用 5 楼 wmxcn2000 的回复:


        exec @sql 
-- 改成下面这样，加个括号。
        exec (@sql )

我在执行exec sp_data '749548-283'，有错误提示如下，749548-283和807326-001在数据包中的格式都是varchar（10）怎么会出错呢，这个应该怎么解决。消息 245，级别 16，状态 1，第 1 行在将 varchar 值 '807326-001' 转换成数据类型 int 时失败。

卖水果的net 2015-09-30

打赏
举报


        exec @sql 
-- 改成下面这样，加个括号。
        exec (@sql )

zxdpxl 2015-09-30

打赏
举报

引用 2 楼 wmxcn2000 的回复:


-- 数据表，你自己建吧，我把过程写给你。
create table tab_list(tname varchar(30))
go
create table test (id int , name varchar(10))
go
create proc sp_data
as
begin
    declare @sql varchar(1000) , @tname varchar(30)
    declare sss cursor for select * from tab_list ;
    open sss
    fetch next from sss into @tname 
    while @@FETCH_STATUS = 0 
    begin
        set @sql = 'insert into test select * from ' + @tname 
        exec @sql 
        fetch next from sss into @tname 
    end
    close sss
    deallocate sss
end
go

非常感谢你的存储过程，我对存储过程做了一个小小的改动，以下是我改动过后的存储过程，在执行存储过程（exec sp_data '749548-283'）的时候sql有错误提示。这是什么问题？？？？ ALTER proc [dbo].[sp_data] ( @value VARCHAR(10) ) as begin truncate table alldata declare @sql varchar(1000) , @tname varchar(30) declare sss cursor for select * from tab_list ; open sss fetch next from sss into @tname while @@FETCH_STATUS = 0 begin set @sql = 'insert into Alldata select * from ' + @tname + ' WHERE PN=' + @value exec @sql fetch next from sss into @tname end close sss deallocate sss end sql 错误提示如下：消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB13th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB14th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB15th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB35th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB37th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USBList2 WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB12th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USBList1 WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB10th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB36th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USBHDD WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB9th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB8th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB7th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB6th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB5th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB4th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB3rd WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB34th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB33th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB32th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB31th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB30th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB29th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB28th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB27th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB26th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB25th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB24th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB23rd WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB22nd WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USBList3 WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB21st WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB20th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB1st WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB2nd WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB19th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB18th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB17th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB16th WHERE PN=749548-283'。消息 2812，级别 16，状态 62，过程 sp_data，第 15 行找不到存储过程 'insert into Alldata select * from USB11th WHERE PN=749548-283'。附上alldata的表格式。 No int Unchecked Project varchar(50) Checked PN varchar(10) Unchecked ISO_From varchar(50) Checked USB_stick varchar(50) Checked Original_MD5 nvarchar(80) Checked Ship_Date date Checked Air_No char(10) Checked ISO_Upload_Date datetime Checked Check_MD5 varchar(50) Checked Result varchar(5) Checked Size_GB decimal(18, 3) Checked ISO_Type varchar(26) Checked MD5_Update_Date datetime Checked File_Path varchar(53) Checked File_Status char(6) Checked Remask varchar(20) Checked

哋岼線 2015-09-29

打赏
举报

动态执行SQL语句,采用 sp_executesql 参数化执行

卖水果的net 2015-09-29

打赏
举报


-- 数据表，你自己建吧，我把过程写给你。
create table tab_list(tname varchar(30))
go
create table test (id int , name varchar(10))
go
create proc sp_data
as
begin
    declare @sql varchar(1000) , @tname varchar(30)
    declare sss cursor for select * from tab_list ;
    open sss
    fetch next from sss into @tname 
    while @@FETCH_STATUS = 0 
    begin
        set @sql = 'insert into test select * from ' + @tname 
        exec @sql 
        fetch next from sss into @tname 
    end
    close sss
    deallocate sss
end
go

gw6328 2015-09-29

打赏
举报

用动态可以实现 DECLARE @sql NVARCHAR(max)='' declare @i int =100 while @i>0 BEGIN SET @sql='insert into tb select * from tb'+CAST(@i AS varchar(10))+' where xxxx' EXEC(@sql); SET @i=@i-1; END 类似于这样