国产片侵犯亲女视频播放_亚洲精品二区_在线免费国产视频_欧美精品一区二区三区在线_少妇久久久_在线观看av不卡

服務器之家：專注于服務器技術及軟件下載分享
分類導航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|數據庫技術|

服務器之家 - 數據庫 - Sql Server - 將表里的數據批量生成INSERT語句的存儲過程 增強版

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

2020-04-30 14:21樺仔 Sql Server

這篇文章主要介紹了將表里的數據批量生成INSERT語句的存儲過程 增強版的相關資料,需要的朋友可以參考下

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

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

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

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

這里有一個存儲過程（適用于SQLServer2005 或以上版本）

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
-- 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這個表

?
1
2
3
4
5
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腳本

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

 

這個腳本有一個缺陷

無論你的表的字段是什麼數據類型，導出來的時候只能是字符

表結構

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

導出來的insert腳本

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

我這里演示一下怎麼用

有兩種方式

1、導全表數據

?
1
InsertGenerator 'customer', null

 

或

?
1
InsertGenerator 'customer', ' '

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

2、根據查詢條件導數據

?
1
InsertGenerator 'customer', 'city=3'

 

或者

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

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

點擊一下，選擇全部

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

然后復制

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

新建一個查詢窗口，然后粘貼

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

其實SQLServer的技巧有很多

最后，大家可以看一下代碼，非常簡單，如果要支持SQLServer2000，只要改一下代碼就可以了

補充：創建一張測試表

?
1
2
3
4
5
6
7
8
9
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]

測試

?
1
2
3
InsertGenerator 'testinsert' ,''
InsertGenerator 'testinsert' ,'name=''nihao'''
InsertGenerator 'testinsert' ,'name=''nihao'' and cash=8.8'

datetime類型會有一些問題

生成的結果會自動幫你轉換

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

--------------------------------------------------------------------------------

群里的人共享的另一個腳本

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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

調用示例

?
1
2
3
4
5
6
7
8
9
10
11
12
--非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区 | 精品一二三区 | 欧美日韩在线播放 | 国产一区久久久 | 在线欧美视频 | 在线a视频| 久久精品无码一区二区三区 | av网站免费在线观看 | 亚洲黄色免费 | 亚色在线| 国产在线专区 | 日韩精品一二三区 | 成人精品久久久 | 久久精品国产清自在天天线 | 亚洲综合在线播放 | 亚洲精品在线视频 | 在线播放一区二区三区 | 国产伊人一区 | 亚洲热妇 | 91aiai| 成人免费看 | 欧美激情在线精品一区二区三区 | 黄色片在线观看视频 | 久久亚洲一区二区三区四区 | 超碰人人爱人人 | 免费看黄色大片 | 色乱码一区二区三区网站 | 国产精品久久久久久久久久久久久 | 国产视频三区 | 日韩精品在线播放 | av在线电影网 | 久久久美女视频 | 欧美二区在线 | 亚洲一级毛片 | 中文字幕精品一区久久久久 | 国产成人午夜精品5599 | 日韩精品在线播放 | 一区日韩 | 农村人乱弄一区二区电影 |