数据批量生成INSERT语句的存储方法
标签：
SQL Server
有时候，我们需要将某个表里的数据全部或者根据查询条件导出来，迁移到另一个相同结构的库中
目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的，只有借助第三方工具（third party tools）
这种脚本网上也有很多，但是网上的脚本还是欠缺一些规范和功能，例如：我只想导出特定查询条件的数据，网上的脚本都是导出全表数据
如果表很大，对性能会有很大影响
这里有一个存储过程（适用于SQLServer2005 或以上版本）
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)),''0'')+''''''),''+'
END
ELSE
IF @dataType = 'image'
BEGIN
SET @stringData = @stringData + '''''''''+
isnull(cast(convert(varbinary,' + @colName + ')
as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
SET @stringData = @stringData + '''''''''+
isnull(cast(' + @colName + ' as nvarchar(max)),''0'')+'''''',''+'
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('1','2')
这个脚本有一个缺陷
无论你的表的字段是什麽数据类型，导出来的时候只能是字符
表结构
CREATE TABLE [dbo].[customer](city int,region int)
导出来的insert脚本
INSERT INTO [dbo].[customer]([city],[region]) VALUES('1','2')
我这里演示一下怎麽用
有两种方式
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
('1','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')
点击查看更多内容
为 TA 点赞
评论
共同学习，写下你的评论
评论加载中...
作者其他优质文章
正在加载中
相关文章推荐
感谢您的支持，我会继续努力的～
扫码打赏，你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫，即可进行扫码打赏哦