Insert a List to a Temp table without stored procedure

Question

I have a list of integers List<int> lstIds. Now I want to add all of these ID into a temporary table.

I know a few different approaches like using SqlCommand parameters which will require to make multiple database calls which is not preferable. Also, I don't want to use stored procedure.

So, is it possible to insert the whole List into the temp table? If yes, then how?

Note: Solution with LINQ will also work.

EDIT: someone marked this as duplicate of this question, while in given question they took DataTable as temp table while here I want to store data in SQL Server temp table.

When you want to pass some kind of array data to SQL Server 2008 or later using ADO.NET, you should define and use a table-valued parameter. — mm8, Jul 04 '18 at 12:56
@mm8 thank you but all the solutions with Table valued params i saw has used Stored procedure which i don't want to use — Harshil Doshi, Jul 04 '18 at 12:58
What are you intending to with a temp table if you don't use a stored procecdure? — mm8, Jul 04 '18 at 13:00
Possible duplicate of [Fastest way to insert 30 thousand rows in a temp table on SQL Server with C#](https://stackoverflow.com/questions/17028657/fastest-way-to-insert-30-thousand-rows-in-a-temp-table-on-sql-server-with-c-shar) — mjwills, Jul 04 '18 at 13:01
The duplicate shows how to take a datatable and insert it quickly into a temp table. Could you, as a suggestion, take your `List`, convert it into a datatable and then use the technique that is suggested there? `SqlBulkCopy` is _usually_ the fastest way to insert large volumes of data. — mjwills, Jul 04 '18 at 13:09
@HarshilDoshi: A table-valued parameter isn't tied to be used only in a stored procedure. See my answer. — mm8, Jul 04 '18 at 13:23
what are you actually trying to achieve? What kind of data is it you're trying to work with (e.g. sequential id's ), how many are you trying to insert? Is this a one time thing, or a continuous thing? — JonnySchnittger, Jul 04 '18 at 14:29
@JonnySchnittger I've a lot of ids which may/may not be sequential. I am taking 50k IDs at a time in list, then adding those IDs into temp table & joining them with an existing table to get relevant IDs. — Harshil Doshi, Jul 04 '18 at 15:04
where are the Id's coming from... a different system? or are they already in the database? @HarshilDoshi ... it does sound like the table type option is bets suited for you — JonnySchnittger, Jul 04 '18 at 15:17
@JonnySchnittger IDs coming from a text file & it's been stored into a list & I can't change that. — Harshil Doshi, Jul 04 '18 at 15:23
`while in given question they took DataTable as temp table` That is not what the duplicate says. The `DataTable` is the **source** that is being written to the **destination** temp table. I would recommend reading the link again. — mjwills, Jul 04 '18 at 21:57

score 1 · Answer 1 · answered Jul 04 '18 at 13:08

When you want to pass some kind of array data to SQL Server 2008 or later using ADO.NET, you could define and use a table-valued parameter:

CREATE TYPE integer_list_tbltype AS TABLE (n int NULL)

You could then do something like this in your C# code:

List<int> integers = new List<int> { 1, 2, 3 };

DataTable dataTable = new DataTable();
dataTable.Columns.Add("n", typeof(int));
dataTable.SetTypeName("integer_list_tbltype");
foreach (int i in integers)
    dataTable.Rows.Add(i);

using (SqlCommand cmd = new SqlCommand())
{
    cmd.Connection = conn;

    cmd.CommandText = "INSERT INTO YourTable (n) SELECT n FROM @integers";
    cmd.Parameters.Add("@integers", SqlDbType.Structured);
    cmd.Parameters["@integers"].Direction = ParameterDirection.Input;
    cmd.Parameters["@integers"].TypeName = "integer_list_tbltype";
    cmd.Parameters["@integers"].Value = dataTable;
    cmd.ExecuteNonQuery();
}

Please refer to Erland Sommarskog's article for more information: http://www.sommarskog.se/arrays-in-sql-2008.html

JonnySchnittger · Answer 2 · 2018-07-04T13:38:55.180

You didn't mention which version of SQL Server you're using... but assuming it's a current version you could use the STRING_SPLIT function: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

Essentially, convert your list of values in to a delimited string. Send that in and split it. Ideally you should probably use table type, but this might suit your needs

SQL Code

DECLARE @Array NVARCHAR(MAX) = '0,1,2,3,4,5,6,7,8,9,10';

DECLARE @TemporaryTable TABLE (
    [Value] INT
);

INSERT INTO @TemporaryTable ( [Value] )
SELECT CONVERT(INT, [value]) FROM STRING_SPLIT(@Array, ',');

SELECT [Value] FROM @TemporaryTable

List<int> lstIds = new List<int>() { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10};

C# Code

List<int> lstIds = new List<int>() { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10};

using (var connection = new SqlConnection("Data Source=localhost;Initial Catalog=Testing;Integrated Security=True;")) { connection.Open();

using (var command = connection.CreateCommand())
{
    command.CommandText = @"CREATE TABLE #TemporaryTable ( [Value] INT );";
    command.CommandType = System.Data.CommandType.Text;
    command.ExecuteNonQuery();
}

using (var command = connection.CreateCommand())
{
    command.CommandText = @"INSERT INTO #TemporaryTable ( [Value] ) SELECT CONVERT(INT, [value]) FROM STRING_SPLIT(@Array, ',');";
    command.CommandType = System.Data.CommandType.Text;
    command.Parameters.Add(new SqlParameter("@Array", string.Join(",", lstIds)));
    command.ExecuteNonQuery();
}

using (var command = connection.CreateCommand())
{
    command.CommandText = @"SELECT COUNT(1) FROM #TemporaryTable";
    command.CommandType = System.Data.CommandType.Text;
    int count = Convert.ToInt32(command.ExecuteScalar());
}

}

This is an interesting solution but I ma using SQL Server 2014 so `String_Split()` is not there i guess. Anyway, nice to see a different approach. — Harshil Doshi, Jul 05 '18 at 06:30
Splitting strings is what you should *not* do. You really should read Erland's article. — mm8, Jul 05 '18 at 14:49
In general I would agree, but the original request did not want table types, linq or stored procedures. That's why I asked above what the purpose was. If this was a once off, single import the above would be fine and meet the needs specified. — JonnySchnittger, Jul 06 '18 at 15:06
I am already familiar with Erland's article, it's been around for a long time now. — JonnySchnittger, Jul 06 '18 at 15:19

score -2 · Answer 3 · answered Jul 04 '18 at 13:01

-2

You have to use linq.

By creating datacontext

Using(Datacontext dc = new Datacontext())
{
    foreach(int id in lstIds.Items)
    {
        temp.Add(id);
    }
    dc.SubmitChanges();
}

Where temp will your DbContext Class

answered Jul 04 '18 at 13:01

Shailesh Chaudhary

139
1
11

Insert a List to a Temp table without stored procedure

3 Answers3