1:sql Server平时接触不多,最近只是在上面造大量数据和导出使用了下
批量造几万条数据
假如一个表
- CREATE TABLE [dbo].[userInfo] (
- [userID] [int] IDENTITY (1, 1) NOT NULL ,
- [roleType] [int] NULL ,
- [groupID] [int] NULL ,
- [userCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
- [userName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
- [text1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
- [text2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
- [text3] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
- ) ON [PRIMARY]
- GO
DECLARE @userCode VARCHAR(30)
DECLARE @userName VARCHAR(30)
DECLARE @userCode_base VARCHAR(30)
DECLARE @count INTEGER
DECLARE @index INTEGER
DECLARE @rand1 INTEGER
DECLARE @rand2 INTEGER
SET @userCode_base='qs_'
SET @userName='userName'
SET @count=100000
SET @index=10000
WHILE @index<@count
BEGIN
SET @userCode=@userCode_base+CONVERT(VARCHAR,@index)
SET @rand1=convert(int,rand()*5)
SET @rand2=convert(int,rand()*5)
INSERT INTO userInfo (userCode,roleType,groupID,userName,text1,text2,text3)
VALUES (@userCode,@rand1,@rand2,@userName,'aokei kaol jof','','aokei kaol jof')
SET @index=@index+1
END
GO
这些也是从其他同行那抄来的,顺便给自己一个便签记录下
2:最近还一个比较头疼的是sqlServer数据移植到mysql,数据少嘛还好办,关键丫的几十万上百万的数据,让我情何以堪,反正使用odbc数据源我是没搞定,网上也有好多牺牲的说法,我也没辙,就使用老土的先导出CSV,再导入到mysql,唉。。。关键在于,几十万上百万万一一下子导出错了那不就悲剧了,就想到每5万或者10万作为一个文件导出在那,再一个个导进去,这样毕竟降低点风险。这里又来了,怎么导出一个个文件呢,我去找找。。。。回来再补充。。。嘎嘎,不过我一次性导入了38万条了,导入的时候我就在那祈祷啊,千万不要出错,千万不要出错。
找了下还算找到个方法
首先最好建一张临时表吧,这样尽量不要操作原表数据,把原表数据复制到临时表,此时为了方便排序以及导出,加了一个字段id,就是这张临时表结构比原表多了个字段ID,里面放着序号,这样利于下面导出操作。
我例子呢,就是上面的userInfo的表,由于表中userid就是一个自增的字段,我暂且就用userid当作临时表的id
我就直接操作userInfo表
捣鼓半天,找到个靠谱的SQL:
declare @i int
declare @sqlstr nvarchar(1000)
set @i=0
while @i*10000<100*1000
begin
set @sqlstr='bcp "select top 10000 * from testdb.dbo.userInfo where userid>='+cast(@i*10000 as varchar(10))+' and userid<'+cast(@i*10000+10000 as varchar(10))+'" queryout "d:\test\'+cast(@i as varchar(10))+'.txt" -c -SServername -Uusername -Ppassword'
exec master.dbo.xp_cmdshell @sqlstr
set @i=@i+1
end
就是每一万条导出一个txt文件,执行中呢,报错‘SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问’,又找了下
EXEC sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure N'show advanced options', N'0'
RECONFIGURE WITH OVERRIDE
修改下配置,
再执行,有戏,出来了。总算收到效果。
1 0 2 qs_10000 userName aokei kaol jofaokei kaol jof
2 0 1qs_10001userNameaokei kaol jofaokei kaol jof
3 3 0qs_10002userNameaokei kaol jofaokei kaol jof
4 3 3qs_10003userNameaokei kaol jofaokei kaol jof
5 0 0qs_10004userNameaokei kaol jofaokei kaol jof
截取的一段,就是没有逗号分隔的,导出CSV也是一样,后期处理吧,这样出来已经不错了。
这样是以制表符分隔每一个字段的,我又把表清空,再选择这个txt导入,导入时候到第二步映射的时候,映射到哪张表,后面编辑打开,勾选启用标识列插入”不然导入不进去。
这是将数据还原到sqlServer,下面试着就把这些txt导入到mysql \t 是制表符
mysql> load data infile 'd:/0.txt' into table user fields terminated by '\t' optionally enclosed by '' lines terminated by '\r\n';
Query OK, 999 rows affected (0.06 sec)
Records: 999 Deleted: 0 Skipped: 0 Warnings: 0
OK,完成,还是可以导入的。
好了,这样一来,借助这种比较笨拙的方法,还是能够完成数据的导入导出,以及不同数据库之间的操作,sqlserver对Oracle是有数据源接口的,这个就不扯了。