procedure for bulk insert

how can i write a procedure to do bulk insert into a table. What i am trying to achieve is to do some sort of automatic process like the procedure should pick up file from k:\folder and update the table..can anyone help me in this issue
LVL 3
ajaypappanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
JaffaKREEConnect With a Mentor Commented:
CREATE PROCEDURE MyProcedureName
as
BEGIN
 BULK INSERT DatabaseName.Owner.OrderOct2004 FROM 'APAPORD1.txt'
  with FIELDTERMINATOR = '?'
END

replace ? with this "inverted comma" - do you mean a backtick:  `

Schedule a job to run the procedure.
0
 
JaffaKREECommented:
You can write a stored procedure containing the BULK INSERT statement, then create a job which is scheduled to run at regular intervals.

What's the name of the file and table/columns being inserted into ?

0
 
ajaypappanAuthor Commented:
The fields in file are delimited by  inverted comma..

name of file: APAPORD1.txt
tablename: OrderOct2004
fields:account      
division
name      
opendate      
salsid      
salesman      
location      
calltype      
wtnnum      
btnno      
provider      
ringto      
circuit      
firstcall      
lastcall      
canceldate      
loadate      
entrydate      
est_usage      
inter_stat      
inter_date      
inter_mess      
inter_actn      
inter_corr      
intra_stat      
intra_date      
intra_mess      
intra_actn      
intra_corr      
cable      
lec_stat      
lec_mess

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ajaypappanAuthor Commented:
there are two field in the table that are date fields will  the string automatically convert to date field?
0
 
JaffaKREECommented:
Sql will attempt to match the format of the field.
0
 
ajaypappanAuthor Commented:
what is this warning I am gettin when i try to create a table?
Warning: The table 'OrderOct2004Ex' has been created but its maximum row size (12356) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

0
 
JaffaKREECommented:
Are you using a large amount of TEXT or IMAGE fields ?
0
 
ajaypappanAuthor Commented:
yes i do..
0
 
ajaypappanAuthor Commented:
but this message i got when i created a table..
0
 
JaffaKREECommented:
TEXT and IMAGE are very large datatypes, especially in comparison to the other ones.  do you HAVE to use them ?  text is for storing large amounts of data, image is for actual images.

Instead of TEXT, consider varchar.
0
 
ajaypappanAuthor Commented:
Thanks buddy for all your help....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.