SqlBulkCopy alternative on SQL Server

Question

I'm developing this Stored Procedure in SQL Server 2012:

CREATE PROCEDURE [dbo].[UploadCodes]
 @param1 nvarchar(20), 
 @param2 nvarchar(20), 
 @param3 nvarchar(20), 
 @param4 nvarchar(20), 
 @param5 nvarchar(20), 
 @newCodes as dbo.CodeList READONLY 
AS
 declare @code nvarchar(20),
 @codeLevel tinyint,
 @headerId int,
 @productId int;
 set nocount on;
 Begin transaction
 -- Insert china header file.
 Insert into CODES_HEADER
 values (@param1, @param2, @param3, @param4, @param5);
 -- If an error, end here.
 If (@@ERROR != 0)
 Begin
 rollback transaction
 return -1 -- Database error
 End
 -- Get header id from latest insert.
 set @headerId = (select SCOPE_IDENTITY());
 -- Get product's id.
 set @productId = (select Id from PRODUCTS where PRODUCT_CODE = @param3)
 -- If this product doesn't exist on database, insert it.
 if (@productId is null)
 begin
 Insert into PRODUCTS values (@param3);
 -- If an error, end here.
 If (@@ERROR != 0)
 Begin
 rollback transaction
 return -1 -- Database error
 End
 set @productId = (select SCOPE_IDENTITY());
 end
 set @codeLevel = (select CAST(@param1 as tinyint));
 Create table #tempCodes (
 Code nvarchar(20))
 insert into #tempCodes (Code) select CODE from @newCodes;
 set rowcount 1
 select @code = Code from #tempCodes
 -- Loop all child codes to check if they have a parent.
 while @@rowcount <> 0
 begin
 set rowcount 0
 select * from #tempCodes where Code = @code
 delete #tempCodes where Code = @code
 insert into EXTERNAL_CODES(CODE, CODE_LEVEL, CODES_HEADER_ID, PRODUCT_ID)
 values (@code, @codeLevel, @headerId, @productId);
 -- If an error, end here.
 If (@@ERROR != 0)
 Begin
 rollback transaction
 return -1 -- Database error
 End
 -- Get next code.
 set rowcount 1
 select @code = Code from #tempCodes
 end
 set rowcount 0
 Commit transaction
return 0

This is the definition of dbo.CodeList type:

CREATE TYPE [dbo].[CodeList]
AS TABLE
(
 CODE nvarchar(20)
);

My problem is with its loop, it gets too long when there are a lot of codes.

Is there another way to run it faster?

On C# I use SqlBulkCopy but I don't know if there is something similar on SQL. I have found Bulk Insert but it uses a file.

• Lasse V. Karlsen: Have you considered trying to rewrite your loop into a query?
• VansFannel: No, I don't have enough sql knowledge to do that.
• Lasse V. Karlsen: It looks like you're pulling out one code from #tempCodes at a time, and for each such row you add one row to EXTERNAL_CODES, is that correct?
• VansFannel: Yes, it is correct. I have to insert all #tempCodes values into EXTERNAL_CODES table. But I need codeLevel, headerId, productId for each EXTERNAL_CODES row.
• Lasse V. Karlsen: Note that your query also does one other thing, it issues a select * from #tempCodes where Code = @code for each iteration, is this necessary?

Answer 1

You should be able to rewrite the entire loop into a single insert statement.

This:

set rowcount 1
select @code = Code from #tempCodes
-- Loop all child codes to check if they have a parent.
while @@rowcount <> 0
begin
 set rowcount 0
 select * from #tempCodes where Code = @code
 delete #tempCodes where Code = @code
 insert into EXTERNAL_CODES(CODE, CODE_LEVEL, CODES_HEADER_ID, PRODUCT_ID)
 values (@code, @codeLevel, @headerId, @productId);
 -- If an error, end here.
 If (@@ERROR != 0)
 Begin
 rollback transaction
 return -1 -- Database error
 End
 -- Get next code.
 set rowcount 1
 select @code = Code from #tempCodes
end
set rowcount 0

Does this:

1. Pick out a single code from #tempCodes into @code
2. Delete that code from #tempCodes
3. Insert one row into EXTERNAL_CODES, using that @code + other variables
4. Grab the next and go back to step #2.

You can rewrite that entire loop into this query (basically everything I pasted above):

INSERT INTO EXTERNAL_CODES (CODE, CODE_LEVEL, CODES_HEADER_ID, PRODUCT_ID)
SELECT Code, @codeLevel, @headerId, @productID FROM #tempCodes

If you also need to clear out #tempCodes (which I doubt), you would also issue this statement:

DELETE #tempCodes

If you also need to rollback other changes if the above fails you would add that single if-statement with a rollback in here as well

• VansFannel: Maybe, I don't need #tempCodes and I can use newCodes instead because I don't need to delete any code from it. This should make it faster.
• Lasse V. Karlsen: In general, avoid writing imperative code when talking to your database engine, always try to come up with query-based statements instead, it's what the database engine is optimized for and in general the people writing the database engine is way better at optimizing these things than we mere mortals are.
• Lasse V. Karlsen: Imperative code is code that is written like any other programming language, with if-statements, loops, etc. Query-based code uses inserts, deletes, updates, etc.
• VansFannel: Ok. Thanks. I've just tested it and it is very very fast.
• VansFannel: Following your answer I have to do an Update instead of an Insert. I have asked this question, stackoverflow.com/questions/30773219/update-topn-from-select, because I need to update N rows, and this value is also in on a table parameter.