天极传媒：天极网全国分站
您现在的位置：
& >&MySQL数据库导出与导入及常见错误
MySQL数据库导出与导入及常见错误解决博客 09:37
　　命令行导出数据库：
　　1，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录
　　如我输入的命令行：cd C:\Program Files\MySQL\MySQL Server 4.1\bin
　　(或者直接将windows的环境变量path中添加该目录)
　　2，导出数据库：mysqldump -u 用户名 -p 数据库名 & 导出的文件名
　　如我输入的命令行:mysqldump -u root -p jluibmclub & d:\ jluibmclub .sql (输入后会让你输入进入MySQL的密码)
　　(如果导出单张表的话在数据库名后面输入表名即可)
　　在用命令做数据库备份过程在cmd命令框中输入C:/mysql/bin&mysqldump -uroot -p jluibmclub& d:\ jluibmclub .
　　报错信息：mysqldump: Got error: 1049: Unknown database '' when selecting the database ，识别不了 jluibmclub 这个数据库。
　　但是我在：
　　mysql&
　　显示：
　　+---------------+
　　| Database |
　　+---------------+
　　| jpa |
　　+---------------+
　　mysql&
　　显示：
　　Database changed
　　其原因很简单，只要记住一点。没进入mysql环境不算执行sql语句，所以不用在其后加入逗号(“;”)。
　　如果进入mysql环境mysql& ,则输入的语句属于sql语句了，在其最后一定要加上逗号(“;”)。
　　3、会看到文件news.sql自动生成到d盘下文件，如果不指定盘符默认在bin目录下。
　　命令行导入数据库：
　　1，进入MySQL：mysql -u 用户名 -p
　　如我输入的命令行:mysql -u root -p (输入同样后会让你输入MySQL的密码)
　　2，在中新建你要建的数据库，这时是空数据库，如新建一个名为news的目标数据库
　　3，输入：mysql&use 目标数据库名
　　如我输入的命令行:mysql&
　　4，导入文件：mysql&source 导入的文件名;
　　如我输入的命令行：mysql&source news.sql(如果在bin目录下用相对路径名，如果在其他目录下用绝对目录名);
　　MySQL备份和还原,都是利用mysqldump、mysql和source命令来完成的。
　　1.Win32下MySQL的备份与还原
　　博客链接：http://chanir./3743
（作者：chanir_lee 的BLOG责任编辑：Sunny）
天极新媒体&最酷科技资讯扫码赢大奖
* 网友发言均非本站立场，本站不在评论栏推荐任何网店、经销商，谨防上当受骗！
办公软件IT新闻整机SQL server 2005将远程数据库导入到本地的方法
远程数据库与本地数据库数据之间的导入导出，从来都是一个值得人关注的问题。
　　下面，就给你详细介绍SQL2005将远程数据库导入到本地的方法。
　　首先，将SQL2005打开。方法是：“开始“→“程序”→“Microsoft SQL Server 2005”→“SQL Server Management Studio”，这样，就可以把本地的SQL2005打开了。
　　打开了本地的SQL2005之后，现在，如何把远程数据库中的数据库，导入到本地的SQL2005里面呢？
　　如下图一样，找到“系统数据库”，在任何一个数据库上面比如model上面点击右键，选择“任务”→“导入数据”。
　　接着，会弹出对话框，点击“下一步”，来到如下图的对话框。
　　上图的对话框，叫你选择的是数据源，指的就是选择远程数据库的数据，注意，上面的对话框，是指远程数据库的。
　　在①位置，输入远程数据库的IP地址，然后，再选择“使用SQL Server身份验证”，在②和③处分别填写用户名和密码。
　　最后，数据库那里，就是④的位置，选择好你要选择的远程服务器中的哪个数据库。都设置好了，就点击“下一步”，来到下图的设置。
　　上图的对话框，指的是本地数据库的设置。
　　服务器名称那里，一般使用默认即可，如果你有多个名称，也可以选择，然后，设置好登陆本地数据库的用户名和密码。
　　最后，在数据库那里选择一个本地数据库名称，如果没有，就点击“新建”按钮创建一个新的数据库名称即可。
　　然后点击“下一步”，会看到下图。
　　一般，如果要复制远程数据库中的所有数据，就选择“复制一个或多个表或视图的数据”，否则选择下面那个。然后点击“下一步”，来到下图。
　　上图中，列表中出现了被你选择的远程数据库的数据库表，点击“全选”，然后点击“下一步”，弹出如下图。
　　上图中，一般只选择“立即执行”，可以不选择“保存SSIS包”。接着点击“完成”按钮。
　　这样，就进入到远程数据库数据传输到本地数据的状态，你会看到，总计有多少个，成功了多少个的信息。如果完全成功了，那么，远程数据库导入到本地就成功了。
如果您喜欢本文请分享给您的好友，谢谢！
评论列表（网友评论仅供网友表达个人看法，并不表明本站同意其观点或证实其描述）《第六章》数据库转移——导入导出、备份还原
所谓数据库的转移，说白了就是把开发使用的数据库服务器上做好的表、数据、存储过程等复制出来，再导入到另一个服务器中。
一般来说方法有三种：
一是利用数据库自己的带参命令exp和imp进行，这种方式古老又低效，但因为其传统又官方，是老DBA彰显水平的必备手段；
二是利用第三方工具借助于数据库本身提供的功能（如Oracle的exp.exe以及imp.exe；SQL Server的Integration Service和“备份/还原”功能）进行图形化界面操作，本质上与第一种方法是一样的，但因为其操作简单方便，莫名其妙的常常被老DBA鄙视；
三是利用第三方工具为用户独创的“脚本输出/脚本执行”功能，也就是将数据库对象的CREATE文输出成SQL文件，然后利用这个SQL文件就可以随时再次创建数据库。这在PL/SQL和Object Browser中都有提供。
本文将以Object Browser为图例对后两种方法进行讲解，因为其中文界面较为简单易懂。最后再简单的交流一下关于DataPump的问题。
那么首先，我们来介绍一下Oracle的导入导出功能。说白了就是数据库对象以二进制文件（.DMP）的形式输出，之后再解析并输入到另一个数据库环境中。
■Oracle导出
1.从菜单进入导出界面
2.在[选择]页中，可以指定导出的对象范围。
全数据库：
数据库上所有用户的所有对象全部导出
用户指定：
指定用户下的所有对象
只导出指定的表，可指定多张表
表空间指定：
指定表空间下的所有对象全部导出
在文件名那一栏里输入要保存的DMP文件的全路径。也可通过浏览选择。目标对象里显示要导出的对象，可选对象里是通过你指定的导出方法列出所有可选的对象。
3.在[选项]里可以设置更多高级选项，这与oracle自身的exp所需要的参数几乎是一样的。
[连接用户ID]可以指定导出时使用的用户。可以指定为当前连接的用户，也可以指定其他用户。指定其他用户的话需要输入那个用户的用户ID和密码。
[Buffer长度]和[记录长度]可设置buffer字节数和record的字节数。
一般可以使用默认的，但如果有些表里有非常多的字段，或者存储了非常大的数据（比如LOB型等），为了避免出错，需要把它调大一些。
[同时导出的数据库对象]可以指定是否同时导出权限，索引，数据，制约。数据库的转移如果只转移表结构而不包含其中数据的话，就将[数据]取消选中。
[增量导出的种类]如下：
&&&&&&&&无：导出所有对象
&&&&&&&&完全：导出所有对象
&&&&&&&&增量：只导出上一次执行导出以来发生变化的对象
&&&&&&&&累计：只导出上一次执行导出以来发生变化的对象，但是在DMP中包含了压缩过的上一次导出的DMP。
[对象的分析]可以设置在导入时生成的统计信息
&&&&&&&&无：不统计
&&&&&&&&估计：只统计一部分的样例。
&&&&&&&&完全：统计
[NLS_LANG]里可以设置导出DMP要使用的字符集编码，不添即为数据库默认。
[扩展空间压缩]选中时，在扩展空间里存储的数据被整合在导出的DMP中
[表的制约]选中时，将会在导出过程中检查数据的一致性
[后台运行]选中时，导出过程将不在界面中体现。
[输出记录]可以指定导出结果的日志保存路径
[使用工具]就是Oracle客户端自带的exp.exe
4，点击[执行]按钮。就执行导出，在[结果日志]里会显示执行的结果。
■Oracle导入
导出成功之后，就会在指定目录下生成一个二进制的.DMP文件，数据库转移就是利用此文件导入到新的数据库中。首先，要连接到新的数据库中。
1.从菜单进入导入界面
2.在[选择]页中设置[导入方法]
全数据库：将DMP以整个数据库的形式导入
用户指定：将DMP文件中指定用户导入到当前连接数据库的指定用户中（此时点击右侧的[一览]按钮，将列出DMP中所有的用户）
表指定：只导入DMP文件中的指定的表（此时点击右侧的[一览]按钮，将列出DMP中所有的表）
在目录中可以输入DMP文件的绝对路径，也可以通过文件浏览方式找到DMP文件。
在[选项]页中的设置项与导出时的基本一致，就不多介绍了。
3.点击[执行]按钮，开始导入。显示正常结束后就完成了数据库的导入。
■脚本输出
脚本输出也是数据库备份，数据库转移的一种方法。
这里说的脚本，就是创建表，存储过程等各种数据库对象的SQL语句的.SQL（DDL）文件，当然也包括向表中插入数据的INSERT文。
任何第三方工具都可以直接查看，修改，执行这些.SQL文件，从而方便的建立起数据库。
1.在[对象列表]页面里列出了当前连接用户下所有的数据库对象，包括表、视图、存储过程、索引等等。选中要导出的对象，右键选择[脚本输出]，调出输出界面
[将同时输出的脚本汇总在一起]选中时，所有的SQL输出到一个文件中。不选中时，每个对象生成单独的SQL文件。
[创建与类型相应的子文件夹]选中时，所有Table的SQL文件将生成到Table文件夹中。所有View的SQL文件将生成到View文件夹中。
各种不同的对象类型将被归类在不同的文件夹中。
不选中时，统一生成在一个文件夹内。
[名称表]是一个很人性化的设置。它让你在数据库中建立一张表，用来表示表明与SQL脚本文件的文件名之间的匹配关系。
也就是说，生成的SQL脚本文件名可以由你按照你自己的意愿和规则自定义。
[输出SCHEMA名]可以设置是否在SQL中带上SHEMA信息。
[在输出TABLE脚本时，包含从属于该表的索引脚本]
[输出TABLE脚本时，同时输出数据（INSERT）语句]这里可以设置insert语句是附加在表的脚本文件中，还是保存在新建的“DATA”目录下，
或者另存为“表名_DATA.SQL”文件中。
[输出EXTANT信息]里可以设置脚本文件中需要包含哪些EXTENT信息。
[输出View脚本时包含项目名]设置是否在“CREATE VIEW 视图名 AS ”之后加上字段名
[输出View脚本时使用FORCE选项]设置是否在VIEW的脚本中附加FORCE设置（只限Oracle）
[输出对象权限]各脚本文件末尾，是否输出赋予对象权限的GRANT语句。
[不导出空白行]可以在导出时自动将脚本中的空行删除。
因为很多人在使用SQL*Plus执行脚本时，经常因为脚本中的空行而错误无法执行的情况。
2.点击[开始]按钮开始导出脚本文件。
脚本已输出，看看我们的文件夹下，多么的整齐！
各种对象分门别类的各就各位，数据也以_DATA.SQL另存了出来。
拥有了这些脚本，就相当于拥有了整个数据库！
这些脚本可以理解为数据库的备份，而且还不是不可修改的硬备份，而是随时可以查看修改任你处置的软备份。
■脚本执行
接下来，就可以通过执行这些脚本来创建新的数据库。
1.从菜单进入[脚本执行]界面
从右侧目录中选择脚本所在文件夹或脚本文件，需要执行的脚本文件或文件夹可以整体移动到左侧，作为脚本执行对象。
2.点击[执行]按钮，在确认对话框中可以设置是否在执行后删除该脚本文件，是否在发生错误时中断处理。
这里特殊说一下，
在执行就很多脚本文件的时候经常因为错误而中断，绝大多数都是因为执行顺序的原因。
比如在创建VIEW的时候，他参照的TABLE还没有被创建，就会出错误。
这种情况下，我们选中[将正确完成的SCRIPT从列表中删除]的话，就可以反复执行，直到列表被清空时就说明所有脚本都被正确执行了。
■如何选择用那种方式？
上面我们已经介绍了以DMP和脚本两种方式进行的导入导出。实际操作的时候，有人会问“我该用哪一种方法呢？“
我们把两种方法的优缺点总结一下：
脚本输出/脚本执行
输出文件种类
二进制DMP文件
SQL文本文件
生成的文件较小可以导出二进制型数据
可以查看和修改输出文件的内容跨不同数据库间也适用
跨不同的数据库时有问题
存在二进制型数据时有问题
最主要的区别就是二进制还是SQL文本。二进制存储，占用空间小；SQL文本形式查看编辑更方便。
如果数据库中存在LOB型的数据时（SQLServer为BINARY型），就只能使用DMP导入导出方式了。
但是，因为Oracle自身的导入导出功能并不保证向下兼容，因此当导入导出的两个数据库版本不同时经常发生问题。
SQLServer也有一样的版本兼容问题，“SQL Server转移”的时候往往会发生错误。
因此当导出和导入的数据库版本不一致的时候，推荐使用脚本输出/脚本执行的方式进行。
■关于DataPump
从Oracle10g开始，除了传统的exp/imp的DMP文件之外，家族里来了个新家伙，叫做DataPump（数据泵）。自出生以来就备受争议，而经过实际使用发现，他与exp/imp并没有本质意义上的不同。
主要区别如下：
1，exp/imp是基于Oracle客户端的。而DataPump是基于Oracle服务端的。
在这一点上，它只解决了Oracle服务器端不需要装客户端的问题…却导致了DataPum导出的文件只能保存在服务器端。
为了解决这个尴尬的情况，Oracle让它支持了网络传输…
2，DataPump导出的数据虽然也是.dmp，但是他与exp/imp导出的.dmp文件并不完全兼容。
3，DataPump号称的支持并行线程，支持挂起重启，支持空间估算等…让高贵的，配置强大的Oracle服务器们情何以堪。
4，非要找出一个优点的话，那就是DataPump允许对导入导出对象进行版本控制，解决了上面说到的DMP版本向下不兼容的问题。
因为DataPump的基本使用方法与exp/imp几乎一样，只是在更多的参数上会有问题。
有问题的同学可以浏览以下官方文档。SQL Server几种数据迁移/导出导入的实践_数据库技术_Linux公社-Linux系统门户网站
你好，游客
SQL Server几种数据迁移/导出导入的实践
来源：Linux社区&
作者：fishparadise
SQL Server提供了多种数据导出导入的工具和方法，在此，分享我实践的经验（只涉及数据库与Excel、数据库与文本文件、数据库与数据库之间的导出导入）。
（一）数据库与Excel
使用数据库客户端（SSMS）的界面工具。右键选择要导出数据的数据库，选择&任务&&&&导出数据&，下图1，按照向导一步一步操作即可。而导入则相反，导入时，SQLServer会默认创建一张新表，字段名也默认跟导入的Excel标题一样，并且会默认字段数据类型等。当然在可以在向导进行修改。需要注意的是如果标题不是英文而是中文，默认创建字段名也是中文，这将给后面数据更新操作带来麻烦，所以最好还是以有意义的英文字段名。把数据导入后，再通过执行语句，把数据插入/更新到业务表。
&figure-1：任务&&导出数据
从SQLServer2005开始，可以直接在SSMS上查询出来的结果复制，然后粘贴到Excel上，对于少量数据来说，是非常快速方便的，需要注意的是长数字可能会变成科学记数法的形式，提前在Excel上指定列的格式为文本即可。
导入的话，ctrl + c 复制Excel上的数据，然后在选择相关表，编辑数据，把数据直接粘贴上去即可。但是不建议直接粘贴到业务表（如果表是空白没有数据，并且字段顺序对应，可以这样操作），而是建议先粘贴到一个新建的中间表中，然后再通过语句，把数据插入/更新到业务表。
这种方法的导出导入，适合于少量的数据，如5000行以内的记录，大于5000行以上就不建议了，速度较慢，如果数据过大，还一定成功。
（二）数据库与文本文件、数据库与数据库
数据库之间的数据迁移或导出导入其实是比较方便的，比如备份数据库后，在新的机器上做恢复。但是需要注意的是SQL2008之前的版本的备份无法在SQL2012或以上版本上直接恢复的，而是通过中间的SQL2008做一个过渡，把旧版本的数据库恢复到SQL2008，然后做备份，最后在SQL2012上恢复。
如果是新版本（下面以SQL2012为例）的备份文件恢复到旧版本（以SQL2008为例）上就比较麻烦了，一般是不支持新版本备份文件在旧版本中恢复的。只能通过编写脚本，把新版本的数据导入到旧版本中。
首先推荐使用的是数据不落地的&链接服务器&。使用SQL2012的SSMS，同时连接到SQL2012和SQL2008的实例，通过编写脚本把SQL2012的数据导入到SQL2008中。两个实例的可以通过链接服务器来连接。以下是设置步骤。
figure-2：新建链接服务器
figure-3：链接服务器和数据源
figure-4：认证
figure-5：创建成功后，可以直接浏览链接服务器的目录，也可以使用语句查询了。
也可以使用脚本来创建链接服务器。
--创建链接服务器
EXEC sp_addlinkedserver
@server='LINKED_SERVER_TEST2',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.88.6,11433'--数据源
--创建登录名和密码
EXEC sys.sp_addlinkedsrvlogin
@rmtsrvname = 'LINKED_SERVER_TEST2', -- 被访问的服务器别名
@useself = 'false',
@locallogin = NULL,
@rmtuser = 'sa', -- 数据源登录名
@rmtpassword = 'psd123456' -- 数据源登录密码
--设置数据可以访问
EXEC sys.sp_serveroption
@server = 'LINKED_SERVER_TEST2',
@optname = 'data access',
@optvalue = N'true'
code-1：创建链接服务器的脚本
创建成功后，可以直接查询数据。
figure-6：查询链接服务器的数据
通过视图sys.servers可以查询所有服务器及相关的属性。
figure-7：查询所有链接服务器
在SSMS上或运行以下脚本可以删除指定的链接服务器。
--删除链接服务器及所有登录
EXEC sys.sp_dropserver
@server = 'LINKED_SERVER_TEST2',
@droplogins = 'droplogins'
&code-2：删除链接服务器及所有登录
详细请参考：
如果两个实例不能连接，只能在SQL2012上导出数据，再到SQL2008上导入。SQLServer提供生成包含数据的脚本工具，下图2。在第三步的&高级&选项里有一项&Types of data to scripts&有三个选择：Data only，Schema and data，Schema only，分别是只生成数据、生成表（对象）和数据，表（对象）。还有生成脚本的版本&Script for Server Version&，下图3。其他选项，按实际需要选择。
&figure-8：任务&&生成脚本
figure-9：生成脚本的高级选项
也可以使用存储过程生成包含数据的脚本。这里介绍一个别人已经做写好存储过程：sp_generate_inserts。运行之后，会按表每条记录生成一条insert的语句
CREATE PROC [dbo].[sp_generate_inserts]
@table_name VARCHAR(776) ,
-- The table/view for which the INSERT statements will be generated using the existing data
@target_table VARCHAR(776) = NULL ,
-- Use this parameter to specify a different table name into which the data will be inserted
@include_column_list BIT = 1 ,
-- Use this parameter to include/ommit column list in the generated INSERT statement
@from VARCHAR(800) = NULL ,
-- Use this parameter to filter the rows based on a filter condition (using WHERE)
@include_timestamp BIT = 0 ,
-- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
@debug_mode BIT = 0 ,
-- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
@owner VARCHAR(64) = NULL ,
-- Use this parameter if you are not the owner of the table
@ommit_images BIT = 0 ,
-- Use this parameter to generate INSERT statements by omitting the 'image' columns
@ommit_identity BIT = 1 ,
-- Use this parameter to ommit the identity columns
@top INT = NULL ,
-- Use this parameter to generate INSERT statements only for the TOP n rows
@cols_to_include VARCHAR(8000) = NULL ,
-- List of columns to be included in the INSERT statement
@cols_to_exclude VARCHAR(8000) = NULL ,
-- List of columns to be excluded from the INSERT statement
@disable_constraints BIT = 0 ,
-- When 1, disables foreign key constraints and enables them after the INSERT statements
@ommit_computed_cols BIT = 1
-- When 1, computed columns will not be included in the INSERT statement
/***********************************************************************************************************
Procedure:
sp_generate_inserts
(Build 22)
(Copyright ?2002 Narayana Vyas Kondreddi. All rights reserved.)
To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some other location.
This procedure is also useful to create a database setup, where in you can
script your data along with your table definitions.
Written by:
Narayana Vyas Kondreddi
Acknowledgements:
Divya Kalra
-- For beta testing
Mark Charsley
-- For reporting a problem with scripting uniqueidentifier columns with NULL values
Artur Zeygman
-- For helping me simplify a bit of code for handling non-dbo owned tables
Joris Laperre
-- For reporting a regression bug in handling text/ntext columns
Tested on:
SQL Server 7.0 and SQL Server 2000
Date created:
January 17th
Date modified:
This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types
Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results
IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed
you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
like nchar and nvarchar
Example 1:
To generate INSERT statements for table 'titles':
EXEC sp_generate_inserts 'titles'
Example 2:
To ommit the column list in the INSERT statement: (Column list is included by default)
IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,
to avoid erroneous results
EXEC sp_generate_inserts 'titles', @include_column_list = 0
Example 3:
To generate INSERT statements for 'titlesCopy' table from 'titles' table:
EXEC sp_generate_inserts 'titles', 'titlesCopy'
Example 4:
To generate INSERT statements for 'titles' table for only those titles
which contain the word 'Computer' in them:
NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter
EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"
Example 5:
To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
(By default TIMESTAMP column's data is not scripted)
EXEC sp_generate_inserts 'titles', @include_timestamp = 1
Example 6:
To print the debug information:
EXEC sp_generate_inserts 'titles', @debug_mode = 1
Example 7:
If you are not the owner of the table, use @owner parameter to specify the owner name
To use this option, you must have SELECT permissions on that table
EXEC sp_generate_inserts Nickstable, @owner = 'Nick'
Example 8:
To generate INSERT statements for the rest of the columns excluding images
When using this otion, DO NOT set @include_column_list parameter to 0.
EXEC sp_generate_inserts imgtable, @ommit_images = 1
Example 9:
To generate INSERT statements excluding (ommiting) IDENTITY columns:
(By default IDENTITY columns are included in the INSERT statement)
EXEC sp_generate_inserts mytable, @ommit_identity = 1
Example 10:
To generate INSERT statements for the TOP 10 rows in the table:
EXEC sp_generate_inserts mytable, @top = 10
Example 11:
To generate INSERT statements with only those columns you want:
EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"
Example 12:
To generate INSERT statements by omitting certain columns:
EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"
Example 13:
To avoid checking the foreign key constraints while loading data with INSERT statements:
EXEC sp_generate_inserts titles, @disable_constraints = 1
Example 14:
To exclude computed columns from the INSERT statement:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
***********************************************************************************************************/
SET NOCOUNT ON
--Making sure user only uses either @cols_to_include or @cols_to_exclude
IF ( ( @cols_to_include IS NOT NULL )
AND ( @cols_to_exclude IS NOT NULL )
RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
IF ( ( @cols_to_include IS NOT NULL )
AND ( PATINDEX('''%''', @cols_to_include) = 0 )
RAISERROR('Invalid use of @cols_to_include property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'
RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
IF ( ( @cols_to_exclude IS NOT NULL )
AND ( PATINDEX('''%''', @cols_to_exclude) = 0 )
RAISERROR('Invalid use of @cols_to_exclude property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'
RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
--Checking to see if the database name is specified along wih the table name
--Your database context should be local to the table for which you want to generate INSERT statements
--specifying the database name is not allowed
IF ( PARSENAME(@table_name, 3) ) IS NOT NULL
RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
--Checking for the existence of 'user table' or 'view'
--This procedure is not written to work on system tables
--To script the data in system tables, just create a view on the system tables and script the view instead
IF @owner IS NULL
IF ( ( OBJECT_ID(@table_name, 'U') IS NULL )
AND ( OBJECT_ID(@table_name, 'V') IS NULL )
RAISERROR('User table or view not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table or view.'
RETURN -1 --Failure. Reason: There is no user table or view with this name
IF NOT EXISTS ( SELECT
INFORMATION_SCHEMA.TABLES
TABLE_NAME = @table_name
AND ( TABLE_TYPE = 'BASE TABLE'
OR TABLE_TYPE = 'VIEW'
AND TABLE_SCHEMA = @owner )
RAISERROR('User table or view not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table or view.'
RETURN -1 --Failure. Reason: There is no user table or view with this name
--Variable declarations
DECLARE @Column_ID INT ,
@Column_List NVARCHAR(MAX) ,
@Column_Name VARCHAR(128) ,
@Start_Insert NVARCHAR(MAX) ,
@Data_Type VARCHAR(128) ,
@Actual_Values NVARCHAR(MAX) ,
--This is the string that will be finally executed to generate INSERT statements
@IDN VARCHAR(128)
--Will contain the IDENTITY column's name in the table
--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = ''
SET @Column_List = ''
SET @Actual_Values = ''
IF @owner IS NULL
SET @Start_Insert = 'INSERT INTO ' + '['
+ RTRIM(COALESCE(@target_table, @table_name)) + ']'
SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner))
+ '].' + '[' + RTRIM(COALESCE(@target_table, @table_name))
--To get the first column's ID
@Column_ID = MIN(ORDINAL_POSITION)
INFORMATION_SCHEMA.COLUMNS (NOLOCK)
TABLE_NAME = @table_name
AND ( @owner IS NULL
OR TABLE_SCHEMA = @owner
--Loop through all the columns of the table, to get the column names and their data types
WHILE @Column_ID IS NOT NULL
@Column_Name = QUOTENAME(COLUMN_NAME) ,
@Data_Type = DATA_TYPE
INFORMATION_SCHEMA.COLUMNS (NOLOCK)
ORDINAL_POSITION = @Column_ID
AND TABLE_NAME = @table_name
AND ( @owner IS NULL
OR TABLE_SCHEMA = @owner
IF @cols_to_include IS NOT NULL --Selecting only user specified columns
IF CHARINDEX('''' + SUBSTRING(@Column_Name, 2,
LEN(@Column_Name) - 2)
+ '''', @cols_to_include) = 0
GOTO SKIP_LOOP
IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
IF CHARINDEX('''' + SUBSTRING(@Column_Name, 2,
LEN(@Column_Name) - 2)
+ '''', @cols_to_exclude) && 0
GOTO SKIP_LOOP
--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
IF ( SELECT COLUMNPROPERTY(OBJECT_ID(QUOTENAME(COALESCE(@owner,
USER_NAME()))
+ '.' + @table_name),
SUBSTRING(@Column_Name, 2,
LEN(@Column_Name) - 2),
'IsIdentity')
IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
SET @IDN = @Column_Name
GOTO SKIP_LOOP
--Making sure whether to output computed columns or not
IF @ommit_computed_cols = 1
IF ( SELECT COLUMNPROPERTY(OBJECT_ID(QUOTENAME(COALESCE(@owner,
USER_NAME()))
+ @table_name),
SUBSTRING(@Column_Name, 2,
LEN(@Column_Name)
'IsComputed')
GOTO SKIP_LOOP
--Tables with columns of IMAGE data type are not supported for obvious reasons
IF ( @Data_Type IN ( 'image' ) )
IF ( @ommit_images = 0 )
RAISERROR('Tables with image columns are not supported.',16,1)
PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'
PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'
RETURN -1 --Failure. Reason: There is a column with image data type
GOTO SKIP_LOOP
--Determining the data type of the column and depending on the data type, the VALUES part of
--the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
--making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
SET @Actual_Values = @Actual_Values
+ CASE WHEN @Data_Type IN ( 'char', 'varchar', 'nchar','nvarchar' )
THEN 'COALESCE(''N'''''' + REPLACE(RTRIM('
+ @Column_Name
+ '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ( 'datetime', 'smalldatetime',
'DATE','time' )
THEN 'COALESCE('''''''' + RTRIM(CONVERT(char,'
+ @Column_Name + ',120))+'''''''',''NULL'')'
WHEN @Data_Type IN ( 'uniqueidentifier' )
THEN 'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM('
+ @Column_Name
+ ')),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ( 'text', 'ntext' )
THEN 'COALESCE(''N'''''' + REPLACE(CONVERT(char(8000),'
+ @Column_Name
+ '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ( 'binary', 'varbinary' )
THEN 'COALESCE(RTRIM(CONVERT(char,'
+ 'CONVERT(int,' + @Column_Name
+ '))),''NULL'')'
WHEN @Data_Type IN ( 'timestamp', 'rowversion' )
THEN CASE WHEN @include_timestamp = 0
THEN '''DEFAULT'''
ELSE 'COALESCE(RTRIM(CONVERT(char,'
+ 'CONVERT(int,' + @Column_Name
+ '))),''NULL'')'
WHEN @Data_Type IN ( 'hierarchyid' )
THEN 'COALESCE(''CAST(''''''+LTRIM(RTRIM('
+ 'CONVERT(char, ' + @Column_Name + ')'
+ ')),''NULL'')' + '+''''''AS
hierarchyid)'''
WHEN @Data_Type IN ( 'float', 'real', 'money',
'smallmoney' )
THEN 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, '
+ @Column_Name + ',2)' + ')),''NULL'')'
ELSE 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, '
+ @Column_Name + ')' + ')),''NULL'')'
END + '+' + ''',''' + ' + '
--Generating the column list for the INSERT statement
SET @Column_List = @Column_List + @Column_Name + ','
SKIP_LOOP: --The label used in GOTO
@Column_ID = MIN(ORDINAL_POSITION)
INFORMATION_SCHEMA.COLUMNS (NOLOCK)
TABLE_NAME = @table_name
AND ORDINAL_POSITION & @Column_ID
AND ( @owner IS NULL
OR TABLE_SCHEMA = @owner
--Loop ends here!
--To get rid of the extra characters that got concatenated during the last run through the loop
SET @Column_List = LEFT(@Column_List, LEN(@Column_List) - 1)
SET @Actual_Values = LEFT(@Actual_Values, LEN(@Actual_Values) - 6)
IF LTRIM(@Column_List) = ''
RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
--Forming the final string that will be executed, to output the INSERT statements
IF ( @include_column_list && 0 )
SET @Actual_Values = 'SELECT ' + CASE WHEN @top IS NULL
OR @top & 0 THEN ''
ELSE ' TOP '
+ LTRIM(STR(@top))
END + ''''
+ RTRIM(@Start_Insert) + ' ''+' + '''('
+ RTRIM(@Column_List) + '''+' + ''')'''
+ ' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' '
+ COALESCE(@from,
' FROM ' + CASE WHEN @owner IS NULL THEN ''
ELSE '[' + LTRIM(RTRIM(@owner))
END + '[' + RTRIM(@table_name) + ']'
+ '(NOLOCK)')
IF ( @include_column_list = 0 )
SET @Actual_Values = 'SELECT '
+ CASE WHEN @top IS NULL
OR @top & 0 THEN ''
ELSE ' TOP ' + LTRIM(STR(@top)) + ' '
END + '''' + RTRIM(@Start_Insert)
+ ' '' +''VALUES(''+ ' + @Actual_Values + '+'')'''
+ ' ' + COALESCE(@from,
+ CASE WHEN @owner IS NULL THEN ''
ELSE '[' + LTRIM(RTRIM(@owner))
END + '[' + RTRIM(@table_name)
+ ']' + '(NOLOCK)')
--Determining whether to ouput any debug information
IF @debug_mode = 1
PRINT '/*****START OF DEBUG INFORMATION*****'
PRINT 'Beginning of the INSERT statement:'
PRINT @Start_Insert
PRINT 'The column list:'
--PRINT @Column_List
PRINT 'The SELECT statement executed to generate the INSERTs'
PRINT @Actual_Values
PRINT '*****END OF DEBUG INFORMATION*****/'
--PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'
--PRINT '--Build number: 22'
--PRINT '--Problems/Suggestions? Contact Vyas @ '
--PRINT '--'
--PRINT ''
--PRINT 'SET NOCOUNT ON'
--PRINT ''
--Determining whether to print IDENTITY_INSERT or not
IF ( @IDN && '' )
PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,
USER_NAME()))
+ '.' + QUOTENAME(@table_name) + ' ON'
PRINT 'GO'
IF @disable_constraints = 1
AND ( OBJECT_ID(QUOTENAME(COALESCE(@owner, USER_NAME())) + '.'
+ @table_name, 'U') IS NOT NULL )
IF @owner IS NULL
'ALTER TABLE '
+ QUOTENAME(COALESCE(@target_table,
@table_name))
+ ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
'ALTER TABLE ' + QUOTENAME(@owner) + '.'
+ QUOTENAME(COALESCE(@target_table,
@table_name))
+ ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
PRINT 'GO'
PRINT 'PRINT ''Inserting values into ' + '['
+ RTRIM(COALESCE(@target_table, @table_name)) + ']' + ''''
--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!
EXEC (@Actual_Values)
PRINT 'PRINT ''Done'''
IF @disable_constraints = 1
AND ( OBJECT_ID(QUOTENAME(COALESCE(@owner, USER_NAME())) + '.'
+ @table_name, 'U') IS NOT NULL )
IF @owner IS NULL
'ALTER TABLE '
+ QUOTENAME(COALESCE(@target_table,
@table_name))
+ ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
'ALTER TABLE ' + QUOTENAME(@owner) + '.'
+ QUOTENAME(COALESCE(@target_table,
@table_name))
+ ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
PRINT 'GO'
IF ( @IDN && '' )
PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,
USER_NAME()))
+ '.' + QUOTENAME(@table_name) + ' OFF'
PRINT 'GO'
--PRINT 'SET NOCOUNT OFF'
SET NOCOUNT OFF
RETURN 0 --Success. We are done!
code-3：sp_generate_inserts脚本源代码
在我的实际使用中，只有两三个参数比较常用，分别是@table_name、@from和@owner，如果表的架构使用默认的dbo，则可以省略。以下是一个使用的例子：
figure-10：使用sp_generate_inserts的一个例子
其他参数的用法，这里就不一一解释了。我经常使用这个存储过程做一些简单而少量（如数万行记录以内）的数据导出导入，比前面介绍的方法方便快捷许多。但这个存储过程支持处理一般常用的数据类型，像XML这种类型则不支持。还有，如果生成的数据太多太大，SSMS返回数据会很慢，甚至SSMS会挂了，这时还是使用SSMS自带的导出脚本到文件稳妥些。如果使用生成的数据脚本文件很大，几百MB甚至上GB，在导入时，就不能直接使用SSMS直接打开来执行了。这时可以使用SQLCMD实用工具来在执行脚本。如下面的一个例子，在D盘下有一个脚本1.sql，内容为：
USE AdventureWorks2008R2
SELECT * FROM Person.CountryR
code-4：SQLMCD的测试脚本
在运行下输入CMD，输入：
sqlcmd -S localhost -d AdventureWorks2008R2 -i D:\1.sql
code-5：SQLMCD的命令
回车执行后如下图，SQLCMD的详细用法，请参考：
&figure-11：SQLCMD的测试例子
使用BCP导出导入大容量数据。可以参阅我的另一篇博客《》。
以上几种方法是我在日常工作比较常使用的数据导出导入的工具，每一种方法都有各自的优势和不同的使用场景，使用不同的方法组合，可以节省不少时间，提高工作效率，希望对您的有所帮助。如果您有更好的建议或方法欢迎告诉我！
本文永久更新链接地址：
相关资讯 & & &
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款}