Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

procedure for bulk insert

Posted on 2004-10-15
11
Medium Priority
?
422 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

722 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