Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

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
0
ajaypappan
Asked:
ajaypappan
  • 6
  • 5
1 Solution
 
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
 
JaffaKREECommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now