將表里的數(shù)據(jù)批量生成INSERT語句的存儲過程 增強版
有時候，我們需要將某個表里的數(shù)據(jù)全部或者根據(jù)查詢條件導出來，遷移到另一個相同結(jié)構(gòu)的庫中
目前SQL Server里面是沒有相關(guān)的工具根據(jù)查詢條件來生成INSERT語句的，只有借助第三方工具（third party tools）
這種腳本網(wǎng)上也有很多，但是網(wǎng)上的腳本還是欠缺一些規(guī)范和功能，例如：我只想導出特定查詢條件的數(shù)據(jù)，網(wǎng)上的腳本都是導出全表數(shù)據(jù)
如果表很大，對性能會有很大影響
?
這里有一個存儲過程（ 適用于SQLServer2005 或以上版本 ）
--
Author: <樺仔>
--
Blog: <http://www.cnblogs.com/lyhabc/>
--
Create date: <2014/10/18>
--
Description: <根據(jù)查詢條件導出表數(shù)據(jù)的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)),
''
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腳本
比如我現(xiàn)在有三個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
)
在執(zhí)行腳本的時候他只會生成dbo這個schema下面的表insert腳本
INSERT
INTO
[
dbo
]
.
[
customer
]
(
[
city
]
,
[
region
]
)
VALUES
(
'
1
'
,
'
2
'
)
?
這個腳本有一個缺陷
無論你的表的字段是什麼數(shù)據(jù)類型，導出來的時候只能是字符
表結(jié)構(gòu)
CREATE
TABLE
[
dbo
]
.
[
customer
]
(city
int
,region
int
)
?
導出來的insert腳本
INSERT
INTO
[
dbo
]
.
[
customer
]
(
[
city
]
,
[
region
]
)
VALUES
(
'
1
'
,
'
2
'
)
?
?
我這里演示一下怎麼用
有兩種方式
1、導全表數(shù)據(jù)
InsertGenerator
'
customer
'
,
null
或
InsertGenerator
'
customer
'
,
'
'
?
2、根據(jù)查詢條件導數(shù)據(jù)
InsertGenerator
'
customer
'
,
'
city=3
'
或者
InsertGenerator
'
customer
'
,
'
city=3 and region=8
'
點擊一下，選擇全部
然后復制
新建一個查詢窗口，然后粘貼
?
其實SQLServer的技巧有很多
最后，大家可以看一下代碼，非常簡單，如果要支持SQLServer2000，只要改一下代碼就可以了
?
補充：創(chuàng)建一張測試表
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類型會有一些問題
?
生成的結(jié)果會自動幫你轉(zhuǎn)換
INSERT
INTO
[
dbo
]
.
[
testinsert
]
(
[
id
]
,
[
name
]
,
[
cash
]
,
[
dtime
]
)
VALUES
(
'
1
'
,
'
nihao
'
,
convert
(
money
,
'
8.80
'
),
convert
(
datetime
,
'
02 8 2015 5:17PM
'
))
?
?
如有不對的地方，歡迎大家拍磚o(∩_∩)o?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
