Bulk insert

Question

 

I m a newbie in Stored Proc. Here I m working with some stuff for importing the csv files then write those inside to MSSQL Server. However I get confused what steps I shoud take.

Here's the Stored Proc I've written.

Instead of running  execute proc_TestIT '11113333', 'V', 'Tony Jones  , those records would be kept in a csv file instead with at least 100 records per each csv file. I am wondering if I should use bulk insert and what I should do with the SP I 've written. As only 10 out of  all the 20 columns in the interface file would be required for the updates/inserts. I m wondering where I should start.

---execute proc_TestIT '11113333', 'V', 'Tony Jones'
CREATE Procedure proc_TestIT

@Locker_No     Varchar(10),
@Locker_Type Varchar(10),
@Member_Full_Name     Varchar(30)

AS
 declare @Location         Varchar(06)  
 set @Location=' ' 
Begin
 BEGIN TRANSACTION
 SET @Location = (select Location from tblLockers where Locker_No=@Locker_No) 
 IF (@Locker_Type='P')
  begin
   Update tblLockerIssues SET Remarks='Damaged' where Locker_No=@Locker_No
  end
 IF(@Locker_Type='V')
  begin
   insert into tblLockerIssues_VIP values(@Locker_No,@Location,'')
  end  
 COMMIT TRANSACTION
 RETURN
End
GO

 

 

Answer 1

There are several basic building blocks that you can use:

• Consider using SSIS (DTS if you are running SQL Server 2000) instead of a stored procedure
• The BULK INSERT command
• OPENROWSET
• BCP

Give each of these a look in books online and consider which option best fits your needs.

Dave

Answer 2

Hi Dave,

Thanks a lot. For the stuff I m working with, we have to enable users to import the csv files with the use of ASP.

As such imports would trigger inserts/updates in several tables e.g. by check each record's member_type in the inteface files,  I m wondering if I should make use of ASP to extract data from those columns I need from the csv files then import into the DB. 

Cheers,

Manfred