Solved

procedure for bulk insert

Posted on 2004-10-15
11
416 Views
Last Modified: 2012-08-14
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
Comment
Question by:ajaypappan
  • 6
  • 5
11 Comments
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12323587
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
 
LVL 3

Author Comment

by:ajaypappan
ID: 12323648
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
 
LVL 6

Accepted Solution

by:
JaffaKREE earned 125 total points
ID: 12323700
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
 
LVL 3

Author Comment

by:ajaypappan
ID: 12323721
there are two field in the table that are date fields will  the string automatically convert to date field?
0
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12323747
Sql will attempt to match the format of the field.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 3

Author Comment

by:ajaypappan
ID: 12323918
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
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12323930
Are you using a large amount of TEXT or IMAGE fields ?
0
 
LVL 3

Author Comment

by:ajaypappan
ID: 12323936
yes i do..
0
 
LVL 3

Author Comment

by:ajaypappan
ID: 12323946
but this message i got when i created a table..
0
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12323969
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
 
LVL 3

Author Comment

by:ajaypappan
ID: 12338058
Thanks buddy for all your help....
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now