Thursday, April 26, 2007

Bulk insert with dynamic primary key -SQL Server 2000/2005

You often need a bulk insert with dynamic primary key where you can define your keys without getting it from the .txt files or .csv files.

here are the stored procedure to bulk insert country with dynamic primary key.

first create temp.txt file in c drive with following data.

Australia
Canada
America
India

then create table with following fields

countryid int 4
countryname varchar(100)

and assign name as tblCountryMaster

now use following stored procedure


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_BulkInsertCountry
(
@FilePath varchar(1000)
)
AS
BEGIN--PROCEDURE
--variable declaration
declare @SQL varchar(500)
declare @id int
declare @CountryName varchar(30)


--Create temporary table for Country
CREATE TABLE #tmpCountry
(
CountryName varchar(30),
)

---executing bulk insert on temporary table
SET @SQL='BULK INSERT #tmpCountry from ''' + @FilePath + ''' WITH (FIELDTERMINATOR ='','',ROWTERMINATOR=''\n'')'
EXEC(@sql)

DECLARE cursor_Country CURSOR READ_ONLY FOR
select [CountryName] from #tmpCountry

OPEN cursor_Country
FETCH NEXT FROM cursor_Country INTO @CountryName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @id=isnull(max(Countryid),0) from tblCountryMaster
SET @id=@id+1
INSERT INTO tblCountryMaster values(@Id,@CountryName)
FETCH NEXT FROM cursor_Country INTO @CountryName
END
CLOSE cursor_Country
DEALLOCATE cursor_Country
END--PROCEDURE


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

happy programming
Share:

1 comment:

  1. Good lord is there not an easier way to do this? This seems horrendously complicated.

    ReplyDelete

Your feedback is very important to me. Please provide your feedback via putting comments.

Search This Blog
Loading...
About Me
My Photo

Full Stack Developer,Blogger,Microsoft Mvp, Mentor, Life long learner, 10+ Years of Experience for JavaScript, web and Microsoft technologies. ASP.NET MVC,Web Forms,C#,AngularJs,Golang
Mvp profile
Subscribe to my blog

Enter your email address:

Follow us on facebook
Blog Archive
Total Pageviews