Inserting data into several tables via stored procedure

Question

I got a performance issue with an import task (user need to import new data into the system) and could use little help.

The data within the database looks like this:

Table Categories:

Columns:

• Id int primary key identity(1,1)
• Code nvarchar(20) not null
• RootId int not null
• ParentId int not null

Table CategoryNames:

Columns:

• CategoryId int not null foreign key references Category (Id)
• LanguageId int not null foreign key references Language (Id)
• Name nvarchar(100)

Currently it's working like this: for each row

• Create connection to database
• Call stored procedure with data[i]
• Close connection

I already got rid of the creating and closing connection for each row of data. But it's still not good enough.

Currently the task needs round about 36 minutes to insert ~22000 categories. The stored procedure looks like this:

procedure Category_Insert 
     (@code nvarchar(20),
      @root int,
      @parent int,
      @languageId int,
      @name nvarchar(100),
      @id int output)
as
     set nocount on

     insert into Categories (Code, RootId, ParentId) 
     values (@code, @root, @parent)

     select id = scope_identity()

     insert into CategoryNames (CategoryId, LanguageId, Name) 
     values (@id, @languageId, @name)

Got any advice how I can speed up the performance of that task?

I would love to use bulk insert or something like that, but how would I realize the logic of the stored procedure with bulk insert?

Or is the any other way to speed this up?