将表里的数据批量生成INSERT语句的存储过程增强版

阅读原文时间：2024年08月20日阅读：1

将表里的数据批量生成INSERT语句的存储过程增强版

有时候，我们需要将某个表里的数据全部或者根据查询条件导出来，迁移到另一个相同结构的库中

目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的，只有借助第三方工具（third party tools）

这种脚本网上也有很多，但是网上的脚本还是欠缺一些规范和功能，例如：我只想导出特定查询条件的数据，网上的脚本都是导出全表数据

如果表很大，对性能会有很大影响

这里有一个存储过程（适用于SQLServer2005 或以上版本）

-- Author: <桦仔>
-- Blog: http://www.cnblogs.com/lyhabc/
-- Create date: <2014/10/18>
-- Description: <根据查询条件导出表数据的insert脚本>
-- =============================================
CREATE PROCEDURE InsertGenerator
(
@tableName NVARCHAR(MAX),
@whereClause NVARCHAR(MAX)
)
AS

--Then it includes a cursor to fetch column specific information (column name and the data type thereof)
--from information_schema.columns pseudo entity and loop through for building the INSERT and VALUES clauses
--of an INSERT DML statement.

DECLARE @string NVARCHAR(MAX) --for storing the first half of INSERT statement  
DECLARE @stringData NVARCHAR(MAX) --for storing the data (VALUES) related statement  
DECLARE @dataType NVARCHAR(MAX) --data types returned for respective columns  
DECLARE @schemaName NVARCHAR(MAX) --schema name returned from sys.schemas  
DECLARE @schemaNameCount int--shema count  
DECLARE @QueryString  NVARCHAR(MAX) -- provide for the whole query, 

set @QueryString=' '

 --如果有多个schema，选择其中一个schema  
SELECT @schemaNameCount=COUNT(\*)  
FROM    sys.tables t  
        INNER JOIN sys.schemas s ON t.schema\_id = s.schema\_id  
WHERE   t.name = @tableName

WHILE(@schemaNameCount>0)  
BEGIN

--如果有多个schema，依次指定  
select @schemaName = name  
from  
(  
    SELECT ROW\_NUMBER() over(order by  s.schema\_id) RowID,s.name  
    FROM    sys.tables t  
            INNER JOIN sys.schemas s ON t.schema\_id = s.schema\_id  
    WHERE   t.name =  @tableName  
) as v  
where RowID=@schemaNameCount

--Declare a cursor to retrieve column specific information  
--for the specified table  
DECLARE cursCol CURSOR FAST\_FORWARD  
FOR  
    SELECT  column\_name ,  
            data\_type  
    FROM    information\_schema.columns  
    WHERE   table\_name = @tableName  
            AND table\_schema = @schemaName

OPEN cursCol  
SET @string = 'INSERT INTO \[' + @schemaName + '\].\[' + @tableName + '\]('  
SET @stringData = ''

DECLARE @colName NVARCHAR(500)

FETCH NEXT FROM cursCol INTO @colName, @dataType

PRINT @schemaName  
PRINT @colName  
IF @@fetch\_status <> 0  
    BEGIN  
        PRINT 'Table ' + @tableName + ' not found, processing skipped.'  
        CLOSE curscol  
        DEALLOCATE curscol  
        RETURN  
    END

WHILE @@FETCH\_STATUS = 0  
    BEGIN  
        IF @dataType IN ( 'varchar', 'char', 'nchar', 'nvarchar' )  
            BEGIN  
                   SET @stringData = @stringData + '''''''''+  
                        isnull(' + @colName + ','''')+'''''',''+'  
            END  
        ELSE  
            IF @dataType IN ( 'text', 'ntext' ) --if the datatype  
                             --is text or something else  
                BEGIN  
                    SET @stringData = @stringData + '''''''''+  
      isnull(cast(' + @colName + ' as nvarchar(max)),'''')+'''''',''+'  
                END  
            ELSE  
                IF @dataType = 'money' --because money doesn't get converted  
                   --from varchar implicitly  
                    BEGIN  
                        SET @stringData = @stringData  
                            + '''convert(money,''''''+  
    isnull(cast(' + @colName  
                            + ' as nvarchar(max)),''0.0000'')+''''''),''+'  
                    END  
                ELSE  
                    IF @dataType = 'datetime'  
                        BEGIN  
                            SET @stringData = @stringData  
                                + '''convert(datetime,''''''+  
    isnull(cast(' + @colName + ' as nvarchar(max)),'''')+''''''),''+'  
                        END  
                    ELSE  
                        IF @dataType = 'image'  
                            BEGIN  
                                SET @stringData = @stringData + '''''''''+  
   isnull(cast(convert(varbinary,' + @colName + ')  
   as varchar(6)),'''')+'''''',''+'  
                            END  
                        ELSE --presuming the data type is int,bit,numeric,decimal  
                        BEGIN  
                                SET @stringData = @stringData + '''''''''+  
      isnull(cast(' + @colName + ' as nvarchar(max)),'''')+'''''',''+'  
                            END

        SET @string = @string + '\[' + @colName + '\]' + ','

        FETCH NEXT FROM cursCol INTO @colName, @dataType  
    END  复制

--After both of the clauses are built, the VALUES clause contains a trailing comma which needs to be replaced with a single quote. The prefixed clause will only face removal of the trailing comma.

DECLARE @Query NVARCHAR(MAX) -- provide for the whole query,  
                          -- you may increase the size  
PRINT @whereClause  
IF ( @whereClause IS NOT NULL  
     AND @whereClause <> ''  
   )  
    BEGIN  
        SET @query = 'SELECT ''' + SUBSTRING(@string, 0, LEN(@string))  
            + ') VALUES(''+ ' + SUBSTRING(@stringData, 0,  
                                          LEN(@stringData) - 2)  
            + '''+'')''  复制

FROM ' +@schemaName+'.'+ @tableName + ' WHERE ' + @whereClause
PRINT @query
-- EXEC sp_executesql @query --load and run the built query
--Eventually, close and de-allocate the cursor created for columns information.
END
ELSE
BEGIN
SET @query = 'SELECT ''' + SUBSTRING(@string, 0, LEN(@string))
+ ') VALUES(''+ ' + SUBSTRING(@stringData, 0,
LEN(@stringData) - 2)
+ '''+'')''
FROM ' + @schemaName+'.'+ @tableName

    END

CLOSE cursCol  
DEALLOCATE cursCol

SET @schemaNameCount=@schemaNameCount-1  
IF(@schemaNameCount=0)  
BEGIN  
   SET @QueryString=@QueryString+@query  
END  
ELSE  
BEGIN  
    SET @QueryString=@QueryString+@query+' UNION ALL '  
END  
PRINT convert(varchar(max),@schemaNameCount)+'---'+@QueryString  
END  
EXEC sp\_executesql @QueryString --load and run the built query  复制

--Eventually, close and de-allocate the cursor created for columns information.

这里要声明一下，如果你有多个schema，并且每个schema下面都有同一张表，那么脚本只会生成其中一个schema下面的表insert脚本

比如我现在有三个schema，下面都有customer这个表

CREATE TABLE dbo.[customer](city int,region int)

CREATE SCHEMA test
CREATE TABLE test.[customer](city int,region int)

CREATE SCHEMA test1
CREATE TABLE test1.[customer](city int,region int)

在执行脚本的时候他只会生成dbo这个schema下面的表insert脚本

INSERT INTO [dbo].[customer]([city],[region]) VALUES('','')

这个脚本有一个缺陷

无论你的表的字段是什麽数据类型，导出来的时候只能是字符

表结构

CREATE TABLE [dbo].[customer](city int,region int)

导出来的insert脚本

INSERT INTO [dbo].[customer]([city],[region]) VALUES('','')

我这里演示一下怎麽用

有两种方式

1、导全表数据

InsertGenerator 'customer', null

或

InsertGenerator 'customer', ' '

2、根据查询条件导数据

InsertGenerator 'customer', 'city=3'

或者

InsertGenerator 'customer', 'city=3 and region=8'

点击一下，选择全部

然后复制

新建一个查询窗口，然后粘贴

其实SQLServer的技巧有很多

最后，大家可以看一下代码，非常简单，如果要支持SQLServer2000，只要改一下代码就可以了

补充：创建一张测试表

CREATE TABLE testinsert (id INT,name VARCHAR(100),cash MONEY,dtime DATETIME)

INSERT INTO [dbo].[testinsert]
( [id], [name], [cash], [dtime] )
VALUES ( 1, -- id - int
'nihao', -- name - varchar(100)
8.8, -- cash - money
GETDATE() -- dtime - datetime
)

SELECT * FROM [dbo].[testinsert]

测试

InsertGenerator 'testinsert' ,''

InsertGenerator 'testinsert' ,'name=''nihao'''

InsertGenerator 'testinsert' ,'name=''nihao'' and cash=8.8'

datetime类型会有一些问题

生成的结果会自动帮你转换

INSERT INTO [dbo].[testinsert]([id],[name],[cash],[dtime]) VALUES('','nihao',convert(money,'8.80'),convert(datetime,'02 8 2015 5:17PM'))

群里的人共享的另一个脚本

IF OBJECT_ID('spGenInsertSQL','P') IS NOT NULL
DROP PROC spGenInsertSQL
GO
CREATE proc spGenInsertSQL (@tablename varchar(256),@number BIGINT,@whereClause NVARCHAR(MAX))
as
begin
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ('
set @sqlValues = 'values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
from
(select case
when xtype in (48,52,56,59,60,62,104,106,108,122,127)

                 then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'

            when xtype in (58,61,40,41,42)

                 then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'

           when xtype in (167)

                 then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

            when xtype in (231)

                 then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'

            when xtype in (175)

                 then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'

            when xtype in (239)

                 then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'

            else '''NULL'''

          end as Cols,name

     from syscolumns 

    where id = object\_id(@tablename)

  ) T  复制

IF (@number!=0 AND @number IS NOT NULL)
BEGIN
set @sql ='select top '+ CAST(@number AS VARCHAR(6000))+' ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
print @sql
END
ELSE
BEGIN
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
print @sql
END

PRINT @whereClause
IF ( @whereClause IS NOT NULL AND @whereClause <> '')
BEGIN
set @sql =@sql+' where '+@whereClause
print @sql
END

exec (@sql)
end
GO

调用示例

--非dbo默认架构需注意
--支持数据类型：bigint，int， bit，char，datetime，date，time，decimal，money， nvarchar(50)，tinyint， nvarchar(max)，varchar(max)，datetime2
--调用示例如果top行或者where条件为空，只需要把参数填上null

spGenInsertSQL 'customer' --表名
, 2 --top 行数
, 'city=3 and didian=''大连'' ' --where 条件

--导出全表 where条件为空
spGenInsertSQL 'customer' --表名
, null --top 行数
,null --where 条件

INSERT INTO [Department] ([DepartmentID],[Name],[GroupName],[Company],[ModifiedDate]) values (1,N'售后部',N'销售组',N'中国你好有限公司XX分公司','05 5 2015 5:58PM')
INSERT INTO [Department] ([DepartmentID],[Name],[GroupName],[Company],[ModifiedDate]) values (2,N'售后部',N'销售组',N'中国你好有限公司XX分公司','05 5 2015 5:58PM')

如有不对的地方，欢迎大家拍砖o(∩_∩)o

本文版权归作者所有，未经作者同意不得转载。

手机扫一扫

移动阅读更方便