bcp批量导入数据问题，来者有分

Question

--1. 创建如下的存储过程, 实现根据某个fmt文件创建指定名称的表CREATE PROC dbo.p_fmt2table
@tbname sysname, 
@fmtfile nvarchar(1000)
AS
--SELECT @tbname = 'tb',
-- @fmtfile = 'c:\test.fmt'
CREATE TABLE #(col nvarchar(4000))
EXEC('BULK INSERT # FROM ''' + @fmtfile + '''')DECLARE @rows varchar(10)
SET ROWCOUNT 2
SELECT @rows = col FROM #
DELETE FROM #
DECLARE @i int
SET @i = @rows
SET ROWCOUNT @iDECLARE @s nvarchar(4000)
SET @s = ''
SELECT @s = @s + ',
' + QUOTENAME(ColName) 
+ ' ' + xtype
+ CASE 
WHEN xtype IN ('binary','char','nchar','nvarchar','varbinary','varchar','decimal','numeric')
THEN QUOTENAME(datalen, '()')
ELSE '' END
+ CASE 
WHEN ColCollate = '' OR ColCollate = '""' THEN ''
ELSE ' COLLATE ' + ColCollate END
FROM(
SELECT id, xtype, plen, datalen, endchar, colid, 
ColName = RTRIM(LEFT(col, CHARINDEX(' ', col))), 
ColCollate = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id, xtype, plen, datalen, endchar, 
colid = LEFT(col, CHARINDEX(' ', col)), 
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id, xtype, plen, datalen, 
endchar = LEFT(col, CHARINDEX(' ', col)), 
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id, xtype, plen,
datalen = LEFT(col, CHARINDEX(' ', col)), 
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id, xtype, 
plen = LEFT(col, CHARINDEX(' ', col)), 
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id, 
xtype = STUFF(LEFT(col, CHARINDEX(' ', col)), 1, 3, ''), 
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id = LEFT(col, CHARINDEX(' ', col)), 
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT col = REPLACE(col, ' ',' ') + ' ' FROM #
)A
)AA
)AAA
)AAAA
)AAAAA
)AAAAAA
)AAAAAAA
)AAAAAAAA
SET ROWCOUNT 0IF OBJECT_ID(@tbname) IS NOT NULL
EXEC('DROP TABLE ' + @tbname )
SET @s = 'CREATE TABLE ' + @tbname + '(' + STUFF(@s, 1, 1, '') + ')'
PRINT @s
EXEC(@s)
DROP TABLE #
GO

Answer 1

然后你写个批处理, 调用上面的存储过程处理指定目录下的fmt文件即可. 由于不知道你要创建的表名和fmt文件名之间的联系, 所以无法帮你写出这个处理过程.

Answer 2

数据导入的话, 只是再写个批处理执行bcp而已, 没有更特别的.

Answer 3

-- 如果循环处理某个目录下的文件, 参考下面的批处理方式
-- 下面的批处理循环某个目录下的所有dbf 文件, 并将其导入到指定的表中.
DECLARE @Path nvarchar(1000), @sql nvarchar(1000), @tbname sysnameSELECT @Path = 'c:\'   -- dbf 文件所在的目录
,@tbname = 'tb'    -- 接收 dbf 文件数据的目的表-- 导入处理
CREATE TABLE #(fname nvarchar(1000))
SET @sql = 'DIR ' + @Path + '*.txt'
INSERT # EXEC master.dbo.xp_cmdshell @sql
IF @@ROWCOUNT = 0 
RETURN
DECLARE tb CURSOR LOCAL
FOR
SELECT N'INSERT ' + @tbname + N'
SELECT * FROM OPENROWSET(''MSDASQL'',
''Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=' + @Path + ''',
''SELECT * FROM ' + QUOTENAME(fname) + N''')'
OPEN tb
FETCH tb INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@sql)
FETCH tb INTO @sql
END
CLOSE tb
DEALLOCATE tb
DROP TABLE #

Answer 4

太感谢你了，我的那些数据表名和.fmt  .dat文件名是一致的，我对这方面不是很懂，突然领导让我做这个，我都不知道该如何下手，我是做服务的，不是做开发的，还望您能写出详细的代码
所有文件的样式如下：
z_tabCZRZ.dat
z_tabCZRZ.fmt
z_tabBZ.dat
z_tabBZ.fmt
.....

Answer 5

我的这些文件是放在本地d:\test目录下的
具体的存储过程、创建表的批处理，批处理执行bcp应该怎么写

Answer 6

.fmt文件的格式如下：7.0
51
1       SQLINT        1       4       ""                        1     KMND
2       SQLCHAR       0       23      ""                        2     KMDM
3       SQLCHAR       0       100     ""                        3     KMMC
4       SQLINT        1       4       ""                        4     KMFL
5       SQLINT        0       4       ""                        5     SSZT
6       SQLINT        1       4       ""                        6     KMJS
7       SQLINT        1       4       ""                        7     DXKM
8       SQLINT        1       4       ""                        8     YEFX
9       SQLNUMERIC    1       19      ""                        9     QCYE
10      SQLNUMERIC    1       19      ""                        10    QCWBYE
11      SQLNUMERIC    1       19      ""                        11    QCSL
12      SQLNUMERIC    1       19      ""                        12    YE
13      SQLNUMERIC    1       19      ""                        13    WBYE
14      SQLNUMERIC    1       19      ""                        14    SL
15      SQLNUMERIC    1       19      ""                        15    SCYE
16      SQLNUMERIC    1       19      ""                        16    SCWBYE
17      SQLNUMERIC    1       19      ""                        17    SCSL
18      SQLNUMERIC    1       19      ""                        18    NCYE
19      SQLNUMERIC    1       19      ""                        19    NCWBYE
20      SQLNUMERIC    1       19      ""                        20    NCSL
21      SQLNUMERIC    1       19      ""                        21    SSJFRFSE
22      SQLNUMERIC    1       19      ""                        22    SSDFRFSE
23      SQLNUMERIC    1       19      ""                        23    SSRCXFSE
24      SQLNUMERIC    1       19      ""                        24    SSJFYFSE
25      SQLNUMERIC    1       19      ""                        25    SSDFYFSE
26      SQLNUMERIC    1       19      ""                        26    SSYCXFSE
27      SQLNUMERIC    1       19      ""                        27    SSJFJFSE
28      SQLNUMERIC    1       19      ""                        28    SSDFJFSE
29      SQLNUMERIC    1       19      ""                        29    SSJCXFSE
30      SQLNUMERIC    1       19      ""                        30    SSJFNFSE
31      SQLNUMERIC    1       19      ""                        31    SSDFNFSE
32      SQLNUMERIC    1       19      ""                        32    SSNCXFSE
33      SQLNUMERIC    1       19      ""                        33    SSJFLFSE
34      SQLNUMERIC    1       19      ""                        34    SSDFLFSE
35      SQLNUMERIC    1       19      ""                        35    SSLCXFSE
36      SQLNUMERIC    1       19      ""                        36    JFRFSE
37      SQLNUMERIC    1       19      ""                        37    DFRFSE
38      SQLNUMERIC    1       19      ""                        38    RCXFSE
39      SQLNUMERIC    1       19      ""                        39    JFYFSE
40      SQLNUMERIC    1       19      ""                        40    DFYFSE
41      SQLNUMERIC    1       19      ""                        41    YCXFSE
42      SQLNUMERIC    1       19      ""                        42    JFJFSE
43      SQLNUMERIC    1       19      ""                        43    DFJFSE
44      SQLNUMERIC    1       19      ""                        44    JCXFSE
45      SQLNUMERIC    1       19      ""                        45    JFNFSE
46      SQLNUMERIC    1       19      ""                        46    DFNFSE
47      SQLNUMERIC    1       19      ""                        47    NCXFSE
48      SQLNUMERIC    1       19      ""                        48    JFLFSE
49      SQLNUMERIC    1       19      ""                        49    DFLFSE
50      SQLNUMERIC    1       19      ""                        50    LCXFSE
51      SQLINT        1       4       ""                        51    SCBZ

Answer 7

我的数据.dat和.fmt是bcp方式导出的，他就不能直接导入到一个空库里面去吗，既然能够连表格式文件都导出来，为什么不能直接恢复进空库呢

Answer 8

现在就是没有表，是一个空库，需要建立所有的表对了zjcxc(邹建) 大哥
文件夹里还有这个文件
Script.dat用记事本打开内容如下好像是创建所有表的语句，
?嘟
       _      O Title J   
 WIDTH  ,
Script J   
 WIDTH  @     z _ t a b B Z ?C R E A T E   T A B L E   [ d b o ] . [ z _ t a b B Z ]   ( 
 
 
 [ S S Z T ]   [ i n t ]   N O T   N U L L   , 
 
 [ B Z D M ]   [ i n t ]   N O T   N U L L   , 
 
 [ B Z M C ]   [ c h a r ] ( 2 0 )   N U L L   , 
 
 [ B Z J C ]   [ c h a r ] ( 1 0 )   N U L L   , 
 
 [ B Z B Z ]   [ c h a r ] ( 4 )   N U L L   , 
 
 [ B Z H L ]   [ n u m e r i c ] ( 1 5 ,   4 )   N U L L   , 
 
 [ S C B Z ]   [ i n t ]   N U L L   
 
 )   O N   [ P R I M A R Y ] 
 
    z _ t a b C Z R Z J
C R E A T E   T A B L E   [ d b o ] . [ z _ t a b C Z R Z ]   ( 
 
 
 [ S S Z T ]   [ i n t ]   N U L L   , 
 
 [ C Z D M ]   [ i n t ]   N U L L   , 
 
 [ C Z Y H ]   [ c h a r ] ( 2 0 )   N U L L   , 
 
 [ C Z R Q ]   [ d a t e t i m e ]   N U L L   , 
 
 [ W C Q K ]   [ i n t ]   N U L L   
 
 )   O N   [ P R I M A R Y ] 
 
    z _ t a b D Q P Z |C R E A T E   T A B L E   [ d b o ] . [ z _ t a b D Q P Z ]   ( 
 
 
 [ P Z D M ]   [ i n t ]   N O T   N U L L   , 
 
 [ P Z L X ]   [ i n t ]   N O T   N U L L   , 
 
 [ P Z L X H ]   [ i n t ]   N U L L   , 
 
 [ Z D S D ]   [ i n t ]   N U L L   , 
 
 [ Q T M K P Z ]   [ i n t ]   N U L L   , 
 
 [ S S Z T ]   [ i n t ]   N O T   N U L L   , 
 
 [ P Z R Q N ]   [ i n t ]   N O T   N U L L   , 
 
 [ P Z R Q Y ]   [ i n t ]   N O T   N U L L   , 
 
 [ P Z R Q R ]   [ i n t ]   N U L L   , 
 
 [ C J Y H ]   [ c h a r ] ( 2 0 )   N U L L   , 
 
 [ S H Y H ]   [ c h a r ] ( 2 0 )   N U L L   , 
 
 [ S H B Z ]   [ i n t ]   N U L L   , 
 
 [ J Z B Z ]   [ i n t ]   N O T   N U L L   , 
 
 [ J Z Y H ]   [ c h a r ] ( 2 0 )   N U L L   , 
 
 [ F D Z S ]   [ i n t ]   N U L L   , 
 
 [ P Z Z Y ] 等等

Answer 9

这个Script.dat文件对应一个Script.spt不过是空的，这是怎么产生的

Answer 10

能不能解释一下上面的存储过程的意思，怎么用.fmt文件创建表

Answer 11

Script.dat里面就是建表语句嘛, 所以不用根据fmt建表, fmt建表是不准确的.
例如下面的这段, 你把 create table 前面的 z _ t a b D Q P Z |换成go就行了.
)   O N   [ P R I M A R Y ] 
 
    z _ t a b D Q P Z |C R E A T E   T A B L E   [ d b o ] . [ z _ t a b D Q P Z ]   ( 

Answer 12

如果不管那个script文件, 则下面的批处理实现根据 fmt 文件建表
当然, 在执行下面的语句之前, 你要执行之前我帖出的那个存储过程的代码创建存储过程
(不然根据fmt文件建表的语句会写得很复杂)
-- 根据fmt文件建表DECLARE @Path nvarchar(1000), @sql nvarchar(1000), @tbname sysnameSELECT @Path = 'd:\test'   -- fmt 文件所在的目录-- 导入处理
CREATE TABLE #(fname nvarchar(1000))
SET @sql = 'DIR ' + @Path + '*.fmt'
INSERT # EXEC master.dbo.xp_cmdshell @sql
IF @@ROWCOUNT = 0 
RETURN
DECLARE tb CURSOR LOCAL
FOR
SELECT N'EXEC p_fmt2table @tbname = ' + QUOTENAME(LEFT(fname, LEN(fname) - 4), '''') + N'
@fmtfile = ''' + @Path + fname + ''''
FROM #
OPEN tb
FETCH tb INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@sql)
FETCH tb INTO @sql
END
CLOSE tb
DEALLOCATE tb
DROP TABLE #

Answer 13

-- 下面的批处理语句实现导入数据DECLARE @Path nvarchar(1000), @sql nvarchar(1000), @tbname sysnameSELECT @Path = 'd:\test'   -- fmt 文件所在的目录-- 导入处理
CREATE TABLE #(fname nvarchar(1000))
SET @sql = 'DIR ' + @Path + '*.fmt'
INSERT # EXEC master.dbo.xp_cmdshell @sql
IF @@ROWCOUNT = 0 
RETURN
DECLARE tb CURSOR LOCAL
FOR
SELECT N'BCP ' + DB_NAME() + '.dbo.' + LEFT(fname, LEN(fname) - 4)
+ N' IN "' + @Path + LEFT(fname, LEN(fname) - 4) + '.dat" '
+ N' /f"' + @Path + fname + '" /T /S"' + @@SERVERNAME + '"'
FROM #
OPEN tb
FETCH tb INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC master.dbo.xp_cmdshell @sql
FETCH tb INTO @sql
END
CLOSE tb
DEALLOCATE tb
DROP TABLE #

Answer 14

我的那个script文件不知道用什么打开，那好像是sqlserver产生的，具体怎么来的我就不知道了，谁能告诉我怎么打开这个文件，我执行建表那段程序时候出现错误：
（所影响的行数为 7 行）服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: '@fmtfile' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: '@fmtfile' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: '@fmtfile' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: '@fmtfile' 附近有语法错误。

Answer 15

-- 我少写了一个,-- 根据fmt文件建表DECLARE @Path nvarchar(1000), @sql nvarchar(1000), @tbname sysnameSELECT @Path = 'd:\test'   -- fmt 文件所在的目录-- 导入处理
CREATE TABLE #(fname nvarchar(1000))
SET @sql = 'DIR ' + @Path + '*.fmt'
INSERT # EXEC master.dbo.xp_cmdshell @sql
IF @@ROWCOUNT = 0 
RETURN
DECLARE tb CURSOR LOCAL
FOR
SELECT N'EXEC p_fmt2table @tbname = ' + QUOTENAME(LEFT(fname, LEN(fname) - 4), '''') + N'
,@fmtfile = ''' + @Path + fname + ''''
FROM #
OPEN tb
FETCH tb INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@sql)
FETCH tb INTO @sql
END
CLOSE tb
DEALLOCATE tb
DROP TABLE #

Answer 16

如果你的sql功底不够好, 可能你较难保证比较成功的完成这次任务