如何批量从*.TXT,把数据插入表中么?(UP的有分,谢谢,,急急,在线等待)

youngby 2003-11-21 02:51:13
数据库中有表如下:
CREATE TABLE [dbo].[Surveys] (
[SurveyID] [int] IDENTITY (1, 1) NOT NULL ,
[SurveyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SurveyInstructions] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SurveyResponse] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

----------------------------------------------------------
--C盘中有一文件Surveys.txt
--内容如下

1,"New Web Site Design","Please complete each question. Thank you!","Thank you for taking the time to answer our questions. Your feedback is greatly appreciated!"
2,"Sales Staff","We want to know what you think about our sales staff.","Thank you for taking the time to answer our questions. Your feedback is greatly appreciated!"





怎样把数据批量插入表中?不是一句句的使用SQL!
...全文
25 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
zjcxc 元老 2003-11-21
  • 打赏
  • 举报
回复
--上面的有点错,正确的应该是:
CREATE TABLE [dbo].[Surveys] (
[SurveyID] [int] IDENTITY (1, 1) NOT NULL ,
[SurveyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SurveyInstructions] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SurveyResponse] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

set identity_insert Surveys on
insert into Surveys(SurveyID,SurveyName,SurveyInstructions,SurveyResponse)
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=NO;DATABASE=C:\'
,Surveys#txt)

select * from Surveys
go
drop table Surveys
zjcxc 元老 2003-11-21
  • 打赏
  • 举报
回复
insert into surveys
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=NO;DATABASE=C:\'
,a#txt)
txlicenhe 2003-11-21
  • 打赏
  • 举报
回复
http://expert.csdn.net/Expert/topic/2341/2341997.xml?temp=.2739221
SQL语句导入导出大全

/** 导入文本文件
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'
)

pengdali 2003-11-21
  • 打赏
  • 举报
回复
BULK INSERT Surveys
FROM 'c:\Surveys.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

update Surveys set
surveyname=substring(surveyname,2,len(surveyname)-2),
SurveyInstructions=substring(SurveyInstructions,2,len(SurveyInstructions)-2),
SurveyResponse=substring(SurveyResponse,2,len(SurveyResponse)-2)

select * from Surveys
pengdali 2003-11-21
  • 打赏
  • 举报
回复
BULK INSERT Surveys
FROM 'c:\Surveys.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧