Inserting a large number of rows using a list/array/table etc

Question

I did a research showing that there's no such thing as list or array to rely on in SQL. The only non-scalar data structure is a table. I'm more of a C# than SQL developer so to me, the following seems very bad. However, I sense there should be a way to make the code more tidy.

declare @Url01 as varchar(max) = 'aaa.domain.com/some-some'
...
declare @Url24 as varchar(max) = 'zzz.domain.com/some-some'

insert into UrlTable (Url,...) values (concat('https://',@Url01),...)
...
insert into UrlTable (Url,...) values (concat('https://',@Url24),...)

Coming from the background of mine, I'd expect an array of the values, instead of declaring them in individual variables, then loopify in them into the target table. I can imagine that concept is resolved using a temporary table instead of a variable with multiple values but that's where my SQL-foo ends. Googling the issue led me nowhere good.

Is it doable at all (reasonably speaking)? What approach should I take (so I know what to google for)?

Answer 1

You can reduce the bulk of the code by doing:

DECLARE @Url01 varchar(max) = 'aaa.domain.com/some-some',
        ...
        @Url24 as varchar(max) = 'zzz.domain.com/some-some';

INSERT dbo.UrlTable (Url,...) VALUES
        (concat('https://',@Url01),...),
        ...
        (concat('https://',@Url24),...);

But if you have thousands, you'll need to break the inserts up into 1,000 sets of VALUES() at a time (this is an arbitrary limit set by SQL Server).

Another way:

DECLARE @urls varchar(max) = '
url1.com
url2.com
url3.com
url4.com';

--INSERT dbo.table(URL, othercol1, othercol2)
SELECT value, 
       othercol1 = 5, 
       othercol2 = 'some constant string'
  FROM STRING_SPLIT(TRIM(char(13) FROM @urls), char(10))
  WHERE value > '';

• Example db<>fiddle

Ideally, if this data is coming from some kind of structured type outside of SQL Server (like a DataTable in C#), you can simply use a table-valued parameter.

CREATE TYPE dbo.URLs AS TABLE(URL varchar(max));
GO

CREATE PROCEDURE dbo.GiveMeAllTheURLs
  @urls dbo.URLs READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.UrlTable(Url, othercol1, othercol2)
  SELECT URL, 5, 'some constant string' FROM @urls;
END
GO

Then from C#, assuming you already have a command object set to call this stored procedure:

SqlParameter tvp1 = cmd.Parameters.AddWithValue("@urls", DataTableName);
tvp1.SqlDbType = SqlDbType.Structured;
tvp1.TypeName = "dbo.URLs";
cmd.ExecuteNonQuery();

I don't know if you have to use AddWithValue, but it's the only example I had handy, so please don't roast me for it.

Answer 2

Taking into account that you can easily form a JSON string in C#, you also can use this approach to pass data. Depending on what is best suited for your task.

Declare @JsonData As VarChar(max) = '[{"Url":"aaa.domain.com/some-some", "OtherProp":"Val0"},
                                      {"Url":"zzz.domain.com/some-some", "OtherProp":"Val1"}]'

Insert Into UrlTable ([Url], OtherProp)
Select T.[Url], T.OtherProp
From OPENJSON(@JsonData, '$') Cross Apply OPENJSON(value, '$')
WITH (
[Url] VarChar(255) '$.Url',
OtherProp VarChar(255) '$.OtherProp') As T

db<>fiddle

If only the URL values differ in the input data, you can use a simpler structure like this:

Declare @JsonData As VarChar(max) = '["aaa.domain.com/some-some", 
                                      "zzz.domain.com/some-some"]'
Declare @OtherProp As VarChar(255) = 'Val'

Insert Into UrlTable ([Url], OtherProp)
Select value, @OtherProp
From OPENJSON(@JsonData, '$')

Newtonsoft.Json NuGet package can be used to form this JSON array in C# app as follows:

var urls = new List<string>();
urls.Add("aaa.domain.com/some-some");
urls.Add("zzz.domain.com/some-some");

var jsonData = JsonConvert.SerializeObject(urls);

db<>fiddle