Bulk Insert - 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

Bulk Insert

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-06-28 : 01:44:41

I am trying to solve one problem in sql. Here my problem is on regards bulk insert.
Let me explain it. I have a table called Events. In this table I am just storing TagName, SmsBody, FilePath, Status.
See one row example here:
TagName = Test
SmsBody = Testing Message
FilePath = C:\temp.txt (this file may contains more than 100000 mobiles or more)
Status = 0

Now here if the status is 0 then I need to insert these values (after reading the given text file) in to another table called SMSs with fields ‘TagName’, ‘SmsBody’, ‘MobileNumber’, ‘Status’.
So by using bulk insert query need read the given file (C:\temp.txt) and need to take the row records (TagName, SmsBody, Status) from Events table and need to insert these values in to SMSs table
So after this process the first row of SMSs table will look like this:
TagName = Test
SmsBody = Testing Message
MobileNumber = 91XXXXXXXXXX (This is the first mobile number which read from the C:\temp.txt file)
Status = 1

Please give me a solution for this one.
Any kind of help is highly appreciated

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 03:09:00

You need to use dynamic sql to call OPENROWSET to get contents of text file to a table. look here for more info on OPENROWSET. just replace file path with the variable which you declare containing file path.the varaible can be populated inside a loop with each time value got from Event table FilePath field.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-06-28 : 04:36:07

Can you give me an example with the above requirement please. To get an Idea only

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 05:31:06

quote:
Originally posted by shajimanjeri

Can you give me an example with the above requirement please. To get an Idea only

Shaji

something like:-

DECLARE @Path varchar(100)

SELECT @Path=MIN(FilePath)
FROM Events
WHERE Status=0

WHILE @Path IS NOT NULL
BEGIN
SET @Sql='INSERT INTO SMS (TagName, SmsBody, MobileNumber, Status)
SELECT e.TagName,e.SmsBody,v.columnname,e.Status
FROM Events e
CROSS JOIN (SELECT * FROM OPENROWSET ( BULK '''+ @Path + ''',SINGLE_BLOB) v
WHERE e.Status=0
AND e.FilePath=' +  @Path
EXEC (@Sql)

SELECT @Path=MIN(FilePath)
FROM Events
WHERE Status=0
AND FilePath >@Path
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources