Question

Quickest way to insert multiple rows into a database table

Workflow

Updated February 13, 2018

asked on February 13, 2018

I have a database column with three columns: First Name, Last Name and Occupation.

I also have a workflow with three multi-value tokens corresponding to these columns. For example:

%(FirstName) - Al, Jack, Scarlet

%(LastName) - Pacino, Nicholson, Johannson

%(Occupation) - Mob Boss, Joker, Spy

What is the quickest way of inserting these tokens into the database table where the value indexes correspond to the columns?

I tried iterating over them using For Each Value and using an Insert Data activity inside the loop, but that quickly gets out of hand when dealing with large datasets. Is there a way to do it in a single operation?

0 0

Replies

replied on February 13, 2018

Ege,

How about using a Script activity to iterate through the multi-value tokens building a large INSERT INTO string and then applying the update in a single SQL call from the script?

0 0

replied on February 13, 2018

I'm actually following a similar approach outlined in this thread, but getting an error:

0 0

replied on February 13, 2018

Yep, something seems to be going on with the parser when adding the parameters. I'd just go with running the SQL commands in the script...

0 0

replied on February 13, 2018

OK, decided to give it a shot using a custom query.

I have this:

DECLARE @Values VARCHAR(MAX)

SET @VALUES = 

'(''Bob'',''Marley'',''Songwriter''),(''Jack'',''Nicholson'',''Joker'')'

DECLARE @SQL AS VARCHAR(MAX)

SET @SQL = 'INSERT INTO [ExternalTables].[dbo].[Test Table]
					,[First Name]
					,[Last Name]
					,[Occupation])
			 VALUES' + @Values 

EXEC(@SQL)

This works in SQL Management Studio and the rows are inserted.

However, this doesn't:

When I test this Custom Query activity and feed this into the Combined Fields token:

(''Bob'',''Marley'',''Songwriter''),(''Jack'',''Nicholson'',''Joker'')

I get this:

No errors are reported anywhere so I can't debug it. When the workflow runs, it completes with no errors also, but the data is not inserted into the table.

0 0

View 1 previous reply

replied on February 13, 2018

No, that is a single, long string that is the combined version of the multi-value fields.

Anyway, I figured it out. Replaced @Values with ? and it worked.

0 0

replied on February 13, 2018

Interesting! I tried it as many ways as I could think of and couldn't get it to work.

While searching for an answer though I saw some hits that indicated that SQL will only accept up to 1000 rows inserted at one time like this. (Not that you are going to do that but FYI).

0 0

replied on February 13, 2018 • Show version history

Actually I am doing exactly that (10,836 rows to be exact) but I solved that too (using loops and counters).

This only takes 3 minutes to run so I'm OK with it.

3 0

You are not allowed to follow up in this post.

Question

Question

Quickest way to insert multiple rows into a database table

Replies

Sign in to reply to this post.