34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[temp](
[cardid] [char](5) COLLATE Chinese_PRC_CI_AS NOT NULL,
[consume] [decimal](10, 2) NOT NULL,
[spare] [decimal](10, 2) NOT NULL,
[consumeorder] [int] NOT NULL,
[curtime] [datetime] NOT NULL,
[stationno] [int] NOT NULL,
[computer_id] [int] ,
[manage_id] [int] ,
[manage_name] [varchar](50) COLLATE Chinese_PRC_CI_AS,
CONSTRAINT [pk_temp] PRIMARY KEY NONCLUSTERED
(
[cardid] ASC,
[consumeorder] ASC,
[curtime] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
通过建立连接服务器查询txt文本文件
(1)在E盘下创建文件demo.txt
(2)创建连接文本文件的服务器
/*使用SP_ADDLINKEDSERVER系统存储过程*/
EXEC SP_ADDLINKEDSERVER txtsrv,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'E:\',
NULL,
'Text'
GO
/*查询获得txt链接服务器上所有可用表的列表*/
EXEC SP_TABLES_EX txtsrv
GO
(3)查询
SELECt * FROM txtsrv...[demo#txt]
GO
EXEC master..xp_cmdshell 'bcp DB_news..dbo.A in d:\sql0915.txt -c -s. -U"sa" -P"meichis1!"'
create table #
(
[cardid] [char](5),
[consume] [decimal](10, 2),
[spare] [decimal](10, 2),
[consumeorder] [int],
[curtime] [datetime],
[stationno] [int]
)
BULK Insert #
FROM 'd:\a.txt'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
select * from #
CREATE TABLE [dbo].[temp](
[cardid] [char](5) COLLATE Chinese_PRC_CI_AS NOT NULL,
[consume] [decimal](10, 2) NOT NULL,
[spare] [decimal](10, 2) NOT NULL,
[consumeorder] [int] NOT NULL,
[curtime] [datetime] NOT NULL,
[stationno] [int] NOT NULL,
[computer_id] [int] ,
[manage_id] [int] ,
[manage_name] [varchar](50) COLLATE Chinese_PRC_CI_AS,
CONSTRAINT [pk_temp] PRIMARY KEY NONCLUSTERED
(
[cardid] ASC,
[consumeorder] ASC,
[curtime] ASC
) ON [PRIMARY]
) ON [PRIMARY]
insert into [temp](cardid,consume,spare,consumeorder,curtime,stationno)
select * from #
select * from [temp]
drop table #
EXEC master..xp_cmdshell 'bcp master.dbo.tb out --master.dbo.tb为表名,out参数为输出
d:\GSPCOMPONENTFACTORY.txt -f -c -U"sa" -P"123456"' --d:\...输出文件路径及文件名
/*
1
2009-04-01 00:00:00.000 2009-09-10 00:00:00.000 2
2008-07-01 00:00:00.000 2008-11-10 00:00:00.000 /
*/
文件内容
EXEC master..xp_cmdshell 'bcp master.dbo.tb out d:\GSPCOMPONENTFACTORY.txt -f -c -U"sa" -P"123456"'
/*
1
2009-04-01 00:00:00.000 2009-09-10 00:00:00.000 2
2008-07-01 00:00:00.000 2008-11-10 00:00:00.000 /
*/
文件内容
/** 导入文本文件
exec master..xp_cmdshell 'bcp dbname..tablename in c:\dt.txt -c -sservername -usa -ppassword'
/** 导出文本文件
exec master..xp_cmdshell 'bcp dbname..tablename out c:\dt.txt -c -sservername -usa -ppassword'
或
exec master..xp_cmdshell 'bcp "select * from dbname..tablename" queryout c:\dt.txt -c -sservername -usa -ppassword'
导出到txt文本,用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-u sa -p password'
bulk insert 库名..表名
from 'c:\test.txt'
with (
fieldterminator = ';',
rowterminator = '\n'
)