如何批量从*.TXT，把数据插入表中么？（UP的有分，谢谢，，急急，在线等待）

[youngby]

数据库中有表如下：
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！

[pengdali]

BULK INSERT Surveys
FROM 'c:\Surveys.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

[pengdali]

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

[txlicenhe]

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'
)

[zjcxc]

insert into surveys
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=NO;DATABASE=C:\'
,a#txt)

[zjcxc]

--上面的有点错,正确的应该是:
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