Insert from a file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert from a file

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-07-22 : 10:17:03

dear All,
When I run this sql script it shows some error
sql = "INSERT INTO SMSs (TagName, SmsBody, MobileNumber, Status) SELECT e.TagName,e.SmsBody,v.columnname,e.Status FROM Events e CROSS JOIN (SELECT * FROM OPENROWSET (BULK ''' C:\20080722162219_1.txt ''', SINGLE_BLOB) v WHERE e.Status=0 "

Error is : Incorrect syntax near the keyword 'BULK'

Here I am just getting TagName, SmsBody, Status from the table Events and getting MobileNumber from the file C:\20080722162219_1.txt and then want to insert all these into SMSs table
SMSs table structure is:
Id, TagNmae(varchar), SmsBody(varchar), MobileNumber(varhcar)

Regards
Shaji

Can anybody help me!

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 10:34:36

are you using sql 2000 or 2005?BULK option is only in 2005 i guess

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 10:36:07

[code]INSERT SMSs
(
TagName,
SmsBody,
MobileNumber,
Status
)
SELECT e.TagName,
e.SmsBody,
v.Col,
e.Status
FROM Events AS e
CROSS JOIN OPENROWSET (BULK 'C:\20080722162219_1.txt', SINGLE_BLOB) AS v(Col)
WHERE e.Status = 0[/code]Please remember that the path to the file is not your local path.
The path in OPENROWSET is relative to your SQL Server because that's where the code is run.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-22 : 10:36:15

Import the file in the staging table and use that staging table in the query

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 10:42:22

quote:
Originally posted by shajimanjeri

Can anybody help me!

You are missing an ending paranthesis for the v derived table.
See my example. There is no need for extra paranthesises.

E 12°55'05.25"
N 56°04'39.16"

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-07-22 : 14:55:14

Thanks Mr.Peso,

When run I run the script above its shows agian the same.
Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'BULK'.

"Please remember that the path to the file is not your local path." What does it mean. I have this text file on my C drive. The path is C:\20080722162219_1.txt
I don't know whats the issue here? I want to fetch the records from this file
I hope you will give a solution to this please
I am using SQL 2000 server

Shaji

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-23 : 02:59:30

quote:
Originally posted by shajimanjeri

Thanks Mr.Peso,

When run I run the script above its shows agian the same.
Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'BULK'.

"Please remember that the path to the file is not your local path." What does it mean. I have this text file on my C drive. The path is C:\20080722162219_1.txt
I don't know whats the issue here? I want to fetch the records from this file
I hope you will give a solution to this please
I am using SQL 2000 server

Shaji

Did you read my solution?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources