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