程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 將表裡的數據批量生成INSERT語句的存儲過程 增強版

將表裡的數據批量生成INSERT語句的存儲過程 增強版

編輯:關於SqlServer

有時候,我們需要將某個表裡的數據全部或者根據查詢條件導出來,遷移到另一個相同結構的庫中

目前SQL Server裡面是沒有相關的工具根據查詢條件來生成INSERT語句的,只有借助第三方工具(third party tools)

這種腳本網上也有很多,但是網上的腳本還是欠缺一些規范和功能,例如:我只想導出特定查詢條件的數據,網上的腳本都是導出全表數據

如果表很大,對性能會有很大影響

這裡有一個存儲過程(適用於SQLServer2005 或以上版本)

-- Author: <桦仔>
-- Blog: <http://www.cnblogs.com/lyhabc/>
-- Create date: <//>
-- 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>)
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()
FETCH NEXT FROM cursCol INTO @colName, @dataType
PRINT @schemaName
PRINT @colName
IF @@fetch_status <> 
BEGIN
PRINT 'Table ' + @tableName + ' not found, processing skipped.'
CLOSE curscol
DEALLOCATE curscol
RETURN
END
WHILE @@FETCH_STATUS = 
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)),''.'')+''''''),''+'
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()),'''')+'''''',''+'
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, , LEN(@string))
+ ') VALUES(''+ ' + SUBSTRING(@stringData, ,
LEN(@stringData) - )
+ '''+'')'' 
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, , LEN(@string))
+ ') VALUES(''+ ' + SUBSTRING(@stringData, ,
LEN(@stringData) - )
+ '''+'')'' 
FROM ' + @schemaName+'.'+ @tableName
END
CLOSE cursCol
DEALLOCATE cursCol
SET @schemaNameCount=@schemaNameCount-
IF(@schemaNameCount=)
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') 

以上所述是本文給大家分享的將表裡的數據批量生成INSERT語句的存儲過程 增強版,希望大家喜歡。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved