Sql Server 图形化界面数据导入导出操作
一、数据导出
1. 导出为excel格式
- 找到指定的数据库右键-任务-导出数据
- A.选择导出为excel格式
2. 导出到指定服务器数据库中
- B.选择导出到另一台服务器
- 之后步骤同上
3. 导出脚本(包含所有SQL语句)
4. 复制指定数据到另一数据库
- 导出使用SQL语句查询的结果
- 报错分析:导出的数据与导入的数据列不匹配(需要编辑映射或者修改目标表的约束)。
- 修改目标表的约束,允许null值即可完成插入导出的数据
二、数据导入
1. 导入到空库
- 指定的需导入的数据库 右键-任务-导入数据
- 一直下一步直到完成即可完成导入.
2. 把数据导入到指定的表
- 同上相同操作可能会产生如下错误,因为默认表中的自增键冲突.
- 所以我们需要忽略表自带的自增键.使用编辑映射
- 然后再导入即可成功.
三、作业的使用
1. 设置作业
- 启动服务
- 然后新建作业
- 建立步骤,输入需要执行的命令。
- 设置作业的时间
- 按需设置警报和通知方式
2. 设置通知
- 设置邮件代理: https://docs.microsoft.com/zh-cn/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail?view=sql-server-ver15
四、备份的使用
1. 数据库的备份
命令方式:
backup database 数据库名 to disk = 'd:\databak\db.bak'
2. 数据库的还原
命令方式:
restore database 数据库名 from disk = 'd:\databak\db.bak'
五、跨服务操作
- 使用SSMS工具
- 使用链接服务器
- 使用函数
首先,模拟以下场景表格
1. A服务器(本地)有一张 Score(成绩)表,数据如上:
2. B 服务器(远程)有一张 Subject(科目)表,结构如下:
CREATE TABLE dbo.Subject
( SubjectId tinyint NOT NULL, --科目Id
GradeId int NOT NULL, --年级Id
SubjectName nvarchar(25) NOT NULL, --名称
ClassHour smallint NOT NULL --课时
CONSTRAINT PK_Subject_SubjectId PRIMARY KEY CLUSTERED
(SubjectId ASC
) ON [PRIMARY];
1. 使用链接服务器
------首先新建链接服务器------
1. 代码新建方式:
--创建链接服务器
EXEC sp_addlinkedserver 'MyServer', '', 'SQLOLEDB', '服务器地址';
--登录服务器
EXEC sp_addlinkedsrvlogin 'MyServer', 'false', null, 'sa', 'Password';
--查询可用服务器
EXEC sp_helpserver;
--删除链接服务器(使用完记得删除)
EXEC sp_dropserver 'MyServer', 'droplogins';
2. 可视化新建
1) 某数据库服务器 -> 服务器对象 -> 链接服务器 -> 新建链接服务器。
2) 常规:链接服务器(服务器名称随便起) -> 其他数据源 -> 数据源(服务器地址) -> 其他选项可以为空。
3) 安全性:使用此安全上下文建立链接 -> 远程登录(输入 sa) -> 使用密码(输入密码)
3. 新建完成后就会出现在链接服务器列表中,如图:
1.插入数据
INSERT INTO [MyServer].[DbName].[dbo].Subject VALUES
(1, 1, '数学', 286),(2, 1, '语文', 278),(3, 1, '英语', 291),
(5, 2, '语文', 288),(6, 2, '英语', 271);
2.查询数据
SELECT * FROM [192.168.18.108].[DbName].[dbo].Subject;
也省略部分[]可以这么写:
select * from [192.168.18.108].mytest.dbo.aaa
-- 注意此处dbo必须写!!
结果:
3.修改数据
UPDATE [MyServer].[DbName].[dbo].Subject SET SubjectName='物理' WHERE SubjectId=6;
4.删除数据
DELETE FROM [MyServer].[DbName].[dbo].Subject WHERE SubjectId=6;
2. 使用OPENQUERY()
使用 OPENQUERY() 函数前,同样需要新建一个链接服务器,并向 OPENQUERY() 函数指定将使用的链接服务器。
1) 插入数据
INSERT INTO OPENQUERY(MyServer, 'SELECT * FROM [DbName].[dbo].Subject') VALUES
(1, 1, '数学', 286),(2, 1, '语文', 278),(3, 1, '英语', 291),
(5, 2, '语文', 288),(6, 2, '英语', 271);
2) 查询数据
SELECT * FROM OPENQUERY(MyServer, 'SELECT * FROM [DbName].[dbo].Subject');
3) 修改数据
UPDATE OPENQUERY(MyServer, 'SELECT * FROM [DbName].[dbo].Subject') SET SubjectName='物理' WHERE SubjectId=6;
4) 删除数据
DELETE FROM OPENQUERY(MyServer, 'SELECT * FROM [DbName].[dbo].Subject') WHERE SubjectId=6;
3. 使用OPENROWSET()
1) 插入数据
INSERT INTO OPENROWSET('SQLOLEDB', '服务器地址'; 'sa'; 'Password', DbName.dbo.Subject) VALUES
(1, 1, '数学', 286),(2, 1, '语文', 278),(3, 1, '英语', 291),
(5, 2, '语文', 288),(6, 2, '英语', 271);
2) 查询数据
SELECT * FROM OPENROWSET('SQLOLEDB', '服务器地址'; 'sa'; 'Password', DbName.dbo.Subject);
3) 修改数据
UPDATE OPENROWSET('SQLOLEDB', '服务器地址'; 'sa'; 'Password', DbName.dbo.Subject) SET SubjectName='物理' WHERE SubjectId=6;
4) 删除数据
DELETE FROM OPENROWSET('SQLOLEDB', '服务器地址'; 'sa'; 'Password', DbName.dbo.Subject) WHERE SubjectId=6;
4. 使用OPENDATASOURCE()
1) 插入数据
INSERT INTO OPENDATASOURCE('SQLOLEDB', 'Data Source=服务器地址;User ID=sa;Password=Password').DbName.dbo.Subject VALUES
(1, 1, '数学', 286),(2, 1, '语文', 278),(3, 1, '英语', 291),
(5, 2, '语文', 288),(6, 2, '英语', 271);
2) 查询数据
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=服务器地址;User ID=sa;Password=Password').DbName.dbo.Subject;
3) 修改数据
UPDATE OPENDATASOURCE('SQLOLEDB', 'Data Source=服务器地址;User ID=sa;Password=Password').DbName.dbo.Subject SET SubjectName='物理' WHERE SubjectId=6;
4) 删除数据
DELETE FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=服务器地址;User ID=sa;Password=Password').DbName.dbo.Subject WHERE SubjectId=6;
5. 总结
使用 OPENROWSET/OPENDATASOURCE() 函数时,如果报以下错误,则需要进行“分布式查询”设置:
错误信息:
"SQL Server 阻止了对组件“Ad Hoc Distributed Queries”的 STATEMENT“OpenRowset/OpenDatasource”的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用“Ad Hoc Distributed Queries”。有关启用“Ad Hoc Distributed Queries”的详细信息,请搜索 SQL Server 联机丛书中的“Ad Hoc Distributed Queries”。"
1. 开启/关闭“分布式查询”
--开启“分布式查询”
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
--关闭“分布式查询”
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
2. 关联查询
SELECT T1.*, T2.SubjectName FROM Score AS T1
LEFT JOIN [MyServer].[DbName].[dbo].Subject AS T2 ON(T1.SubjectId=T2.SubjectId);
结果:
1) 本文介绍了常用的几种数据库跨服务器操作,链接服务器与 OPENQUERY() 函数的方式比较【推荐】使用,因为都是为某个链接服务器定义一个别名,使用起来比较方便。而 OPENROWSET/OPENDATASOURCE() 函数需要在每次使用时,都需要提供服务器地址、用户名、密码等,可以根据自己的需要选择对应的实现方式。
2) 垮服务器操作数据库,前面举例了关联查询,另外还可以关联更新、删除等,这跟操作当前服务器并没有太大区别。
3) 在跨服务器操作之前,首先需要确定远程服务器支持远程连接,如果不支持可参考http://xpbag.com/archives/1629 中最后的“开启允许远程连接”进行相关设置。