Solved

procedure for bulk insert

Posted on 2004-10-15
11
417 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Abstract Express Replacement Software 12 28
Find results from sql within a time span 11 33
Passing Parameter to Stored Procedure 4 24
transaction in asp.net, sql server 6 34
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

832 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