求sql导入语句

Question

我有500多个csv文件，想导入SQL数据库内，数据库字段与文件字段是一样的， 如何写批量导入语句。

Answer 1

用动态sql
--以下是别人写的，--第一步,先获取某一个文件夹下的所有txt 文件create table tmp_filelist (cdrfile varchar(64))
go
exec master.dbo.xp_cmdshell 'dir D:\data.db\4006\400.<YYYYMM>\*.txt /s/b  > D:\data.db\4006\400.<YYYYMM>\list'-- -s:表示该文件夹下所有文本文件,包括子文件夹
-- -b:当前文件夹所有文本文件
-- >:表示把当前文件列表存入指定的文件中 
go
bulk insert tmp_filelist from N'D:\data.db\4006\400.<YYYYMM>\list'--用药bulk insert 命令把文件列表的文件名存入 数据表中
go
--第二步,利用游标把每一文件导入数据库中
--他这里是用bulk insert ，也可以用
--select * from opendatasource('microsoft.jet.oledb.4.0','text;hdr=no;database=路径')...[文件名#txt]truncate table tmp_cdr4006csv
declare @cdrfile varchar(64)
declare @sql nvarchar(256)
declare @cmd varchar(512) 
declare cur_fl cursor for select * from tmp_filelist
open cur_fl
fetch next from cur_fl into @cdrfile
while @@fetch_status!=-1 begin
    set @sql ='BULK INSERT tmp_cdr4006csv FROM N'''+@cdrfile+''' WITH ( FIELDTERMINATOR = '','')' exec sp_executesql @sql
    fetch next from cur_fl into @cdrfile
end
close cur_fl
deallocate cur_fl

Answer 2

declare @count int,@path varchar(200),@tb varchar(100),@cmd nvarchar(4000)
set @count =500
set @path='D:\abc\'--a100.csv
set @count=1
whiel @count<=600
begin
set @cmd='bcp '+@path + 'a'+cast(@count as varchar)+'.csv in '+@tb+' -c -t, -T'
exec master.dbo.xp_cmdshell @cmd
set @count=@count=1
end

Answer 3

if exists(select 1 from sysobjects where name='file2table' and objectproperty(id,'isprocedure')=1)
 drop procedure file2table
 go
 create procedure file2table 
   @servername varchar(200) --服务器名称
  ,@username varchar(200)   --用户名,如果用nt验证方式,则为空''
  ,@password varchar(200)   --密码
  ,@tbname varchar(500)     --数据库
  ,@filename varchar(1000)  --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
  ,@isout bit               --1为导出,0为导入
 as
 declare @sql varchar(8000)------如果@tbname参数为ksoa类型,则默认导出整个数据库中的数据,如果@tbname参数为ksoa..spkfk则默认导出某个表的数据
 begin --导出整个数据库,定义游标,取出所有的用户表
   declare @m_tbname varchar(250)
   if right(@filename,1)<>'\' set @filename=@filename+'\'--如果不是文件夹根目录,设置为根目录
   set @m_tbname='declare #tb cursor for select name from '+@tbname+'.dbo.sysobjects where xtype=''u''
                                         and (name not like ''wms_%'' and name not like ''HR_%'' and name not like ''tmp_%'')'
   exec(@m_tbname)
   open #tb
   fetch next from #tb into @m_tbname
   while @@fetch_status=0
     begin
       set @sql='bcp '+@tbname+'.'+'dbo.'+@m_tbname+case when @isout=1 then ' out ' else ' in ' end
           +  @filename+@m_tbname+'.csv -w'   --  注意
           +' /S '+@servername +case when isnull(@username,'')='' then '' else ' /U '+@username end
           +' /P '+isnull(@password,'')
 exec master.dbo.xp_cmdshell @sql
 fetch next from #tb into @m_tbname
 end
 close #tb
 deallocate #tb 
 end
 go

Answer 4

http://blog.csdn.net/jinjazz/archive/2008/07/25/2710169.aspx

Answer 5

                            CREATE TABLE [dbo].[aaa](
                            [t] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
                            [y] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
                            );
                            BULK INSERT aaa
                            FROM 'e:\jsb\e3.csv'
                            WITH (
                                FIELDTERMINATOR = '\t',
                                ROWTERMINATOR = '\n'
                            );

Answer 6

                            CREATE TABLE [dbo].[aaa](
                                [t] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
                                [y] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
                            );
                            BULK INSERT aaa
                            FROM 'e:\jsb\e3.csv'
                            WITH (
                                FIELDTERMINATOR = ',',
                                ROWTERMINATOR = '\n'
                            );

Answer 7

DECLARE @dir sysname,@cmd nvarchar(4000);
SET @dir = 'C:\';CREATE TABLE #tmp(filename nvarchar(1024));
SET @cmd = N'dir "' + @dir + '*.csv" /B'
INSERT #tmp EXEC master.dbo.xp_cmdshell @cmd;DELETE #tmp WHERE filename IS NULL;IF EXISTS(SELECT * FROM #tmp WHERE filename LIKE '%找不到文件%')
    BEGIN
        RAISERROR('找不到文件',16,1)
    END
ELSE
    BEGIN
        DECLARE @SQL nvarchar(MAX);
        SET @SQL = '';
        SELECT @SQL = @SQL + 'EXEC xp_cmdshell N''bcp 数据库.dbo.表 in "' + @dir + filename 
                           + '" -w -T -t, -r\n'';' + CHAR(13) + CHAR(10)
        FROM #tmp;        EXEC(@SQL); 
    ENDDROP TABLE #tmp;

Answer 8

谢谢大家对我的blog的关注。@小梁,读取文件列表不需要xp_cmdshell掉用dir命令，xp_dirtree就可以返回结果集了  

Answer 9

請問JJ，如何用這個命令找出指定目錄下所有的.csv文件，我沒試出來。

Answer 10

找到了，以前收藏的，好久没看，贴出来一下：
将某个目录上的Excel表,导入到数据库中 收藏 
--将某个目录上的Excel表,导入到数据库中--将所有的Excel文件放到一个目录中,假设为c:\test\,然后用下面的方法来做create table #t(fname varchar(260),depth int,isf bit)
insert into #t exec master..xp_dirtree 'c:\test',1,1
declare tb cursor for select fn='c:\test'+fname from #t
    where isf=1 and fname like '%.xls'  --取.xls文件(EXCEL)
declare @fn varchar(8000)
open tb
fetch next from tb into @fn
while @@fetch_status=0
begin
    --下面是查询语句,需要根据你的情况改为插入语句
    --插入已有的表用:insert into 表 selct * from ...
    --创建表用:select * into 表 from ...
    set @fn='select * from 
    OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;DATABASE='+@fn+''',全部客户$)'
    exec(@fn)
    fetch next from tb into @fn
end
close tb
deallocate tb
drop table #t访问excel
       1)、inert into A  SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="E:\联系方式.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...           [Sheet1$]
           但此时查询结果得顺序是按照列名得顺序排列的，而不是按照电子表格中原有的顺序。这是 SQL Server 2000 行集函数 OpenRowSet 和 OpenDataSource 本身的问题，与访问接口引擎无关，也与 Excel 版本无关。SQL Server 2005 的 OpenRowSet 和 OpenDataSource 不存在这个问题。
       2)、inert into A select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=E:\联系方式.xls', 'select * from [Sheet1$]')   
           此种方法可以解决opendatasurce得问题，即查询结果列得顺序与电子表格中原有顺序一致
       3)、使用链接服务器：
           exec sp_addlinkedserver @server='xlsserver',@srvproduct='jet4.0',@provider='microsoft.jet.oledb.4.0',@datasrc='E:\联系方式.xls',@provstr='excel            8.0'
          inert into A  select * from xlsserver...[Sheet1$]
           此时查询结果中列得顺序是按照列名得顺序排列的。
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/04/24/4105912.aspx

Answer 11

    CREATE TABLE [dbo].[aaa](
                                [t] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
                                [y] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
                            );
                            BULK INSERT aaa
                            FROM 'e:\jsb\e3.csv'
                            WITH (
                                FIELDTERMINATOR = ',',
                                ROWTERMINATOR = '\n'
                            );

Answer 12

还是建议SSIS or dts如果是直接sql statement
还需要将csv都放在数据库所在server吧？

Answer 13

这个提供参考 
java实现 excel 中数据导入 oracle 
ORACLE是有一个叫ADI的解决方案 所需的额外包：commons-io-1.4、poi-bin-3.0.2 思路：用户选择要导入的EXCEL文件，上传至WEB服务器。然后将文件存放目录传给POI类。通过对row循环取到cell的值，最后insert到ORACLE中。 public boolean saleDeptToDB(String spreadSheet)throws HekException{ boolean flag = false; 
IDBConn db = DBConn.getInstance(); 
IDBOperate dbOp = DBOperate.getInstance(); Connection conn = db.getConn(); 
PreparedStatement pstmt = null; 
try{ 
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(spreadSheet)); 
HSSFSheet sheet = workbook.getSheetAt(0); String sql = "insert into tableName(DEPT_ID,DEPT_CODE,DEPT_DESC,ITEM_NO,ITEM_CATE,BUDGET_QTY,BUDGET_AMOUNT,TIME_ID)"; 
sql +="values(?,?,?,?,?,?,?,?)"; pstmt = conn.prepareStatement(sql); for(int rowNumOfSheet=1;rowNumOfSheet 
HSSFRow rowOfSheet = sheet.getRow(rowNumOfSheet); 
HekSaleDeptManual hek = new HekSaleDeptManual(); HSSFCell cell0 = rowOfSheet.getCell((short)0); 
if(cell0 != null)hek.setDeptId((int)cell0.getNumericCellValue()); HSSFCell cell1 = rowOfSheet.getCell((short)1); 
if(cell1 != null)hek.setDeptCode(cell1.getRichStringCellValue().toString()); HSSFCell cell2 = rowOfSheet.getCell((short)2); 
if(cell2 != null)hek.setDeptDesc(cell2.getRichStringCellValue().toString()); HSSFCell cell3 = rowOfSheet.getCell((short)3); 
if(cell3 != null)hek.setItemNo(cell3.getRichStringCellValue().toString()); HSSFCell cell4 = rowOfSheet.getCell((short)4); 
if(cell4 != null)hek.setItemCate(cell4.getRichStringCellValue().toString()); HSSFCell cell5 = rowOfSheet.getCell((short)5); 
if(cell5 != null)hek.setBudgetQty(cell5.getNumericCellValue()); HSSFCell cell6 = rowOfSheet.getCell((short)6); 
if(cell6 != null)hek.setBudgetQty(cell6.getNumericCellValue()); HSSFCell cell7 = rowOfSheet.getCell((short)7); 
if(cell7 != null)hek.setTimeID(cell7.getRichStringCellValue().toString()); dbOp.insertBathHekDept(pstmt, hek); 
} 
pstmt.executeBatch(); 
flag = true; 
conn.commit(); 
}catch(SQLException ex){ 
db.rollbackTransaction(conn); 
System.out.println("recordToDB Error: "+ex); 
}catch(IOException ioex){ 
System.out.println("saleDeptToDB read file Error: "+ioex); 
}finally{ 
db.closePstmt(pstmt); 
db.closeConn(conn); 
} 
return flag; 
} 

Answer 14

都有导入的工具的，mysql有，不知道你用的有没有

Answer 15

如果结构和表结构一致，可以调用bcp工具，也可以调用bulk insert语句来批量导入

Answer 16

sql链接服务器也不错。学习学习。

Answer 17

数据的导入/导出，人个倾向于使用BAT+BCP命令行。