How to bulk insert into a table in sql server 2005

Question

Hi all,

I have a storedproc which takes an ntext field where some records are passed. Suppose there is a table t as below.

| ID | Name | Designation|
--------------------------
| 1  | ABC  | Team leader|
| 2  | DEF  | Developer  |
| 3  | XYZ  | Manager    |

I am sending two more record as '4|Tom|Developer; 5|John|Team Leader;' The above string contains column values separated by '|' and rows are separated by ';'. So if I pass the string as the ntext type parameter of the storedproc and need to insert the rows into the table then how to do this?

What is the best way to implement bulk insert in a table in sql server 2005?

Answer 1

Have you had a look at the Bulk Insert tsql examples from file (can you save to file first?

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      )

Otherwise you will have to split the rows, loop these and split the fields

use split as

CREATE FUNCTION [dbo].[SplitString]
(
     @String VARCHAR(MAX) ,
     @Delimiter VARCHAR(10)
)
RETURNS @RetTable TABLE(
     String varchar(MAX)
)
AS 
BEGIN
    DECLARE @i INT ,
      @j INT
    SELECT  @i = 1
    WHILE @i <= LEN(@String)
    BEGIN
     SELECT @j = CHARINDEX(@Delimiter, @String, @i)
     IF @j = 0
     BEGIN
      SELECT @j = LEN(@String) + 1
     END
     INSERT @RetTable SELECT SUBSTRING(@String, @i, @j - @i)
     SELECT @i = @j + LEN(@Delimiter)
    END
    RETURN
END

This will always be a maintinace nightmare though.