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-25 : 07:21:29

I have a table called Events with fields SmsBody(varchar 2000), Tagname (varcahr 30), FilePath (varchar 50), status (char(1)).

Here the records are inserting as like below one

SmsBody = HaiWorld
Tagname = Test
FilePath = C:\mobiles.txt
Status = 0
This will be a row on events table.

Here field FilePath stored the physical path which stored on the C drive. This file contains 100000 mobile numbers. Now here I need to write a stored procedure to read this file and then insert (bulk insert) all the information (smsbody, tagname, filePath, status) in to another table called SMSs with fields SmsBody (varchar), tagname (varchar), Receiptent(varchar), status(char)

In this table the all field values are same except Receiptent field.
The Receiptent field will store the mobile numbers from the FilePath Here it is C:\mobiles.txt(Its dynamic).

Now how can I create/ write a stored procedure to do this issue
I hope you can help me!

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 07:24:21

You need to use dynamic sql for this. Get the FilePath value one by one to a variable inside a loop and then use dynamic sql to use xp_commandshell and call bcp with file path as variable value.

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-06-25 : 07:31:14

Thanks visakh16,
But can you make small example by using above table info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 08:20:41

quote:
Originally posted by shajimanjeri

Thanks visakh16,
But can you make small example by using above table info.

look at article below. you need to just include a loop to populate the variable inside with each of file paths and then use it as shown.

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-06-25 : 14:59:42

Could you please help me to do the above job with out any loop condition. I mean the filePath is not dynamic. Its a constand file in C drive.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 15:43:32

Have a look at OPENROWSET function.

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

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-06-26 : 02:36:39

Dear Mr. Peso,
Thanks for your kind reply. But still I am in search to solve this problem. Here my request is on regards bulk insert.
Let me explain it again. 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 = 919846181410 (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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources