Microsoft SQL Server 数据量大 导入导出 问题汇总

问题一:

SQL Server的导出导入方式有:

【SQL Server 数据库导入导出部分】

今天拿到一份有近百万条数据的Excel要导到数据库里面,我先在本地(2014)用自带Excel,然后生成脚本文件去服务器(2008)上执行;文件SQL打开不了。

  1. 在SQL Server中提供了导入导出的界面操作。
  2. 在界面操作中又分【复制一个或多个表或视图的数据】和【编写查询以指定要传输的数据】两种模式,第一种是直接对表、视图进行全部字段、记录进行导出,而第二种就是可以通过SQL语句来控制导出导入的字段和行。
  3. 使用 简单但有用的SQL脚本 中的【表复制】这里面的方法。
  4. 再一种就是在命令行中使用bcp命令来导入导出数据,需要特别说明的是,这是对大数据量导入导出就好的办法。

    –整个表导出(out)
    bcp 数据库名.dbo.表名 out
    c:\currency.txt -S”数据库实例” -U”用户” -P”密码” -c 

    –使用SQL语句导出(queryout)
    bcp “select * from 数据库名.dbo.表名” queryout c:\currency.txt -S
    数据库实例 -U”用户” -P”密码” -c

    –设置字段分隔符和行分隔符(-c -t”,”
    -r”\n”),不想输入字段类型等请配合-c一起使用
    bcp “select * from 数据库名.dbo.表名” queryout c:\currency.txt -S
    数据库实例 -U”用户” -P”密码” -c -t”,” -r”\n”

    –指定每批导入数据的行数、指定服务器发出或接收的每个网络数据包的字节数(-k
    -b5000 -a65535)
    bcp “select * from 数据库名.dbo.表名” queryout c:\currency.txt -S
    数据库实例 -U”用户” -P”密码” -c -t”,” -r”\n” -k -b5000-a65535

    –在查询分析器上执行(EXEC master..xp_cmdshell)
    EXEC master..xp_cmdshell ‘bcp “select * from 数据库名.dbo.表名”
    queryout c:\currency.txt -S 数据库实例 -U”用户” -P”密码” -c’

    –把SQL语句生成一个.sql文件,然后调用
    –注:路径的文件夹名称中间不能有空格
    exec master..xp_cmdshell ‘osql -S 数据库实例 -U 用户 -P 密码 -i   
    C:\cmdshellTest.sql’  

    –将数据导入到currency表中
    EXEC master..xp_cmdshell ‘bcp 数据库名.dbo.表名 in c:\currency.txt
    -c -T’
    –导入数据也同样可以使用-F和-L选项来选择导入数据的记录行。
    EXEC master..xp_cmdshell ‘bcp 数据库名.dbo.表名 in c:\currency.txt
    -c -F 10 -L 13 -T’

1、在查询分析器下查询Excel文档
SELECT * FROM
OpenDataSource(‘Microsoft.Jet.OLEDB.4.0’,’Data Source=”c:\测试.xls”;
User ID=Admin;Password=;Extended properties=Excel 8.0′)…Sheet1$

解决方法:

图片 1

2、从数据库中导出数据并存到文件中
EXEC master..xp_cmdshell ‘bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.xls
-c -q -S”.” -U”sa” -P””‘
EXEC master..xp_cmdshell ‘bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.txt
-c -q -S”.” -U”sa” -P””‘

用自带的sqlcmd工具

在使用命令xp_cmdshell的时候需要设置权限:

3、从文件中导入数据到数据库对应表中
EXEC master..xp_cmdshell ‘bcp CAS2004..HGZ_LIAOJIAN in c:\temp1.xls
-c -q -S”.” -U”sa” -P””‘
EXEC master..xp_cmdshell ‘bcp CAS2004..HGZ_LIAOJIAN in c:\temp1.txt
-c -q -S”.” -U”sa” -P””‘

第一步:Win+R 键入:cmd 命令,开启命令行工具;

图片 2

【SQL SERVER 数据库实用SQL语句】

第二步:键入:cd C:\Program Files\Microsoft SQL
Server\100\Tools\Binn
 (具体目录路径跟你安装的SQL位置有关)

/*MSsql2005 如何启用xp_cmdshell
默认情况下,sql
server2005安装完后,xp_cmdshell是禁用的(可能是安全考虑),如果要使用它,可按以下步骤
*/
— 允许配置高级选项
EXEC sp_configure ‘show advanced options’, 1
GO
— 重新配置
RECONFIGURE
GO
— 启用xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 1
GO
–重新配置
RECONFIGURE
GO

1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate
Chinese_PRC_Stroke_ci_as

第三步:键入:sqlcmd -S . -U sa -P 123 -d test -i data.sql

–执行想要的xp_cmdshell语句
Exec xp_cmdshell ‘query user’
GO

2.分页SQL语句
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as
rownum,tab.* from 表名 As tab) As t where rownum between 起始位置 And
结束位置

参数说明:*-S 服务器地址 -U 用户名 -P 密码
 -d 数据库名称 -i 脚本文件路径 *

–用完后,要记得将xp_cmdshell禁用(出于安全考虑)
— 允许配置高级选项
EXEC sp_configure ‘show advanced options’, 1
GO
— 重新配置
RECONFIGURE
GO
— 禁用xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 0
GO
–重新配置
RECONFIGURE
GO

3.获取当前数据库中的所有用户表
select * from sysobjects where xtype=’U’ and category=0

转自:

图片 3

4.获取某一个表的所有字段
select name from syscolumns where id=object_id(‘表名’)

更多参考:

图片 4

5.查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and
b.text like ‘%表名%’

 

 

6.查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype=’P’

问题二:

 

7.查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from
master..syslogins where name=’sa’)
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid
<> 0x01

今天拿到一个数据库备份文件,服务器上还原一下,一百来万条数据,要导出成Excel,一套
任务 => 导出数据 => SQL->Excel 2007
很好导出到65536条报错,一查是Excel限制
。到本地试试还是不行,那么写存储过程用分页导出多张子表,每个子表65536条数据。好烦不想写。用bcp吧。

 

8.查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = ‘表名’

解决方法:

 

9.使用事务
在使用一些对数据库表的临时的SQL语句操作时,可以采用SQL
SERVER事务处理,防止对数据操作后发现误操作问题
开始事务
Begin tran
  Insert Into TableName Values(…)
SQL语句操作不正常,则回滚事务。
回滚事务
Rollback tran
SQL语句操作正常,则提交事务,数据提交至数据库。
提交事务
Commit tran

第一步:运行下列语句设置安全配置

遇见的错误:

  1. 按全文匹配方式查询
    字段名 LIKE N’%[^a-zA-Z0-9]China[^a-zA-Z0-9]%’
    OR 字段名 LIKE N’%[^a-zA-Z0-9]China’
    OR 字段名 LIKE N’China[^a-zA-Z0-9]%’
    OR 字段名 LIKE N’China

EXEC sp_configure ‘show advanced options’, 1

1、发生以下错误:
[Error][Microsoft][Native]Error = [Microsoft][SQL Native
Client]无法打开 BCP 主数据文件

11.计算执行SQL语句查询时间
declare @d datetime
set @d=getdate()
select * from SYS_ColumnProperties select
[语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())