原文:
将表里的数据批量生成INSERT语句的存储过程 增强版
将表里的数据批量生成INSERT语句的存储过程 增强版
有时候，我们需要将某个表里的数据全部或者根据查询条件导出来，迁移到另一个相同结构的库中
目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的，只有借助第三方工具（third party tools）
这种脚本网上也有很多，但是网上的脚本还是欠缺一些规范和功能，例如：我只想导出特定查询条件的数据，网上的脚本都是导出全表数据
如果表很大，对性能会有很大影响
这里有一个存储过程（ 适用于SQLServer2005 或以上版本 ）
--
Author: <桦仔>
--
Blog: <http://www.cnblogs.com/lyhabc/>
--
Create date: <2014/10/18>
--
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
>
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
'
))
如有不对的地方，欢迎大家拍砖o(∩_∩)o
