sqlserver数据库导出表⾥所有数据成insert语句
sqlserver备份表语句
有时候，我们想把数据库的某张表⾥的所有数据导⼊到另外⼀个数据库或另外⼀台计算机上的数据库，对于sql server有这样的⼀种⽅法
下⾯我以sql server 2008 R2，数据库是Northwind数据库为例，
⽬标：把Northwind数据库的Orders表导出成insert语句。
第⼀步：选择Northwind数据库，右键-任务-⽣成脚本：
第⼆步：在弹出的“⽣成和发布脚本”的简介窗⼝，按“下⼀步”按钮：
第三步：在“选择对象”窗⼝，选中“选择特定数据库对象”，展开表，
勾选要⽣成insert语句的表，我这⾥选的是order表，
按“下⼀步”按钮：
第四步：在弹出的“设置脚本编写选项”窗⼝，按“⾼级”按钮，
在弹出的“⾼级脚本编写选项”中下拉下拉条⾄底部，
设置“要编写脚本的数据类型”为“仅限数据”(“仅限数据”是只导出数据为insert语句，如果是导出表结构的话选择“仅限架构”，选择“架构和数据”则架构和insert语句都⽣成)，按“确定”按钮：
第五步：在“设置脚本编写选项”窗⼝，
“指定如何保存或发布脚本”的“输出类型”选中“将脚本保存到特定位置”,
勾选“保存到⽂件”，则可以指定⼀个保存的路径，保存为.sql⽂件，
勾选“保存到新建查询窗⼝”，则会新打开⼀个查询窗⼝，把所有insert 语句放到新查询窗⼝：
第六步：在“设置脚本编写选项”窗⼝，按“下⼀步”按钮，弹出：
第七步：在“摘要”窗⼝，按“下⼀步”按钮：
第⼋步：在“保存或发布脚本”窗⼝，按“完成”按钮：
最终会⾃动新建⼀个查询窗⼝，order表⾥的所有数据都转换成insert语句。
=====================================================================================
将表⾥的数据批量⽣成INSERT语句的存储过程增强版
有时候，我们需要将某个表⾥的数据全部或者根据查询条件导出来，迁移到另⼀个相同结构的库中
⽬前SQL Server⾥⾯是没有相关的⼯具根据查询条件来⽣成INSERT语句的，只有借助第三⽅⼯具（third party tools）
这种脚本⽹上也有很多，但是⽹上的脚本还是⽋缺⼀些规范和功能，例如：我只想导出特定查询条件的数据，⽹上的脚本都是导出全表数据
如果表很⼤，对性能会有很⼤影响
这⾥有⼀个存储过程（适⽤于SQLServer2005 或以上版本）
-- =============================================
-- Author:      <；桦仔>
-- Blog:        <wwwblogs/lyhabc/>
-- Create date: <2014/10/18>
-- Description: <；根据查询条件导出表数据的insert脚本>
-- =============================================
CREATE PROCEDURE[InsertGenerator]
(
@tableName NVARCHAR(100),-- the table name
@whereClause NVARCHAR(MAX),--col1=1
@includeIdentity INT-- include identity column(1:yes,0:no)
)
AS
--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(20) --data types returned for respective columns
DECLARE@schemaName NVARCHAR(20) --schema name returned from sys.schemas
DECLARE@schemaNameCount int--shema count
DECLARE@QueryString NVARCHAR(MAX) -- provide for the whole query,
DECLARE@identity INT--identity column(1:yes,0:no)
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
clmns.[name]AS[column_name],
usrt.[name]AS[data_type],
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) AS[Identity]
FROM dbo.sysobjects AS tbl WITH (NOLOCK)
INNER JOIN dbo.syscolumns AS clmns WITH (NOLOCK) ON clmns.id=tbl.id
LEFT JOIN dbo.systypes AS usrt WITH (NOLOCK) ON usrt.xusertype = clmns.xusertype
LEFT JOIN dbo.sysusers AS sclmns WITH (NOLOCK) ON sclmns.uid = usrt.uid
LEFT JOIN dbo.systypes AS baset WITH (NOLOCK) ON baset.xusertype = pe and baset.xusertype = pe LEFT JOIN dbo.syscomments AS defaults WITH (NOLOCK) ON defaults.id = clmns.cdefault
LEFT JOIN dbo.syscomments AS cdef WITH (NOLOCK) ON cdef.id = clmns.id AND cdef.number= lid
WHERE (tbl.[type]='U') AND (tbl.[name]=@tableName AND SCHEMA_NAME(tbl.uid)=@schemaName) AND CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int)=@includeIdentity ORDER BY tbl.[name], lorder
OPEN cursCol
SET@string='INSERT INTO ['+@schemaName+'].['+@tableName+']('
SET@stringData=''
DECLARE@colName NVARCHAR(500)
FETCH NEXT FROM cursCol INTO@colName, @dataType,@identity
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
--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)),''null'')+''''''),''+'
SET@stringData=@stringData+'COALESCE(''''''''+CONVERT(varchar(max),'+@colName+',120)+'''''''',''NULL'')+'',''+'
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,@identity
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
PRINT'stringData:'+@stringData
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
--SET @QueryString=REPLACE(@QueryString,'convert(datetime,''NULL'')',NULL)
PRINT CONVERT(VARCHAR(MAX),@schemaNameCount)+'---'+@QueryString
END
--PRINT @QueryString
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 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!=0AND@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
View Code

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。