C# – Batch identity inserts

Updated! Now contains a second version which will return all id’s if you are uncertain of how table locks and row locks will be handled.

NOTE!

You really should think twice before using this kind of code. What you should do instead is to at least try to use the SqlBulCopy class instead. Of course you will get problems since it will not give you the id’s back, but there are solutions to that problem. E.g. you can create a solution where you in the database preserve a range of identities before you perform the insert. By doing this you can assign your parents and their children the correct id-values and use the KeepIdentity option of the SqlBulkCopy class.

I’m currently building support for identity inserts in SisoDb where I need to let the database generate the ids and then assign them to the entities. To get better performance I want to batch up the inserts and sending them in a large chunk to the Sql-server and I want to make use of parameters to be protected from sql-injection attacks. You can of course use SqlBulkCopy but it will not let you get the generated identities so that you can assign them to your entities.

This is a draft of the solution so you might have to tweak it a bit.

Tables

For this example I will use a classic example of Cars and Owners where each car can have one owner. The tables look like this:

Person {#Id, PersonNo, Name}
Car {#Id, RelOwnerId, RegNo, Make, Model}

Person -- <1:N> -- Car

The Id-fields are defined as identities.

Create records for insert

I have created a IdentityInsertRecord that holds the parent record values as well as several rows of child record values.

var records = new List<IdentityInsertRecord>();

var parentValues = new List<string> { "800503", "Hans Wertheim" };
var childValues = new List<string[]>
                        {
                            new[] { "ABC111", "Volvo", "S60" },
                            new[] { "ABC112", "Volvo", "V70" },
                            new[] { "ABC113", "Volvo", "XC90" },
                        };
records.Add(new IdentityInsertRecord(parentValues, childValues));

parentValues = new List<string> { "800504", "Daniel Wertheim" };
childValues = new List<string[]>
                        {
                            new[] { "ABC211", "Audi", "A3" },
                            new[] { "ABC212", "Audi", "A4" },
                            new[] { "ABC213", "Audi", "A6" },
                        };
records.Add(new IdentityInsertRecord(parentValues, childValues));

parentValues = new List<string> { "800505", "Anton Wertheim" };
childValues = new List<string[]>
                        {
                            new[] { "ABC311", "BMW", "316" },
                            new[] { "ABC412", "BMW", "320" },
                            new[] { "ABC513", "BMW", "323" },
                        };
records.Add(new IdentityInsertRecord(parentValues, childValues));

The code above defines three parent records (Persons) that each owns three cars.

Generate the Sql

To generate the Sql I have created a SqlBulkIdentityInsertsGenerator class that you will have to provide with some metadata, describing the names of the parent table, child table and their columns as well as the column in the child table holding the referencing value.

var generator = new SqlBulkIdentityInsertsGenerator
{
    ParentTableName = "Person",
    ParentColumnNames = { "PersonNo", "Name" },
    ChildTableName = "Car",
    ChildColumnNames = { "RegNo", "Make", "Model" },
    NameOfRelationalColumn = "RelOwnerId"
};

After that, all you have to do is let the generator generate the sql needed to insert the rows.

var sql = generator.GenerateSql(records);

Insert the rows and extract the Id’s

In this post I will just show you this manually, which means, I have taken the generated sql and executed it via Management studio.

VERSION 1

declare @currentParentId int;
declare @firstParentId int;
declare @lastParentId int;
declare @firstChildId int;
declare @lastChildId int;
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800503',@p1='Hans Wertheim',@pOut=@currentParentId out;
set @firstParentId=@currentParentId;
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC111',@p2 = 'Volvo',@p3 = 'S60',@pOut=@firstChildId out;
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC112',@p2 = 'Volvo',@p3 = 'V70';
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC113',@p2 = 'Volvo',@p3 = 'XC90';
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800504',@p1='Daniel Wertheim',@pOut=@currentParentId out;
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC211',@p2 = 'Audi',@p3 = 'A3';
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC212',@p2 = 'Audi',@p3 = 'A4';
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC213',@p2 = 'Audi',@p3 = 'A6';
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800505',@p1='Anton Wertheim',@pOut=@currentParentId out;
set @lastParentId=@currentParentId;
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC311',@p2 = 'BMW',@p3 = '316';
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100)', @p0 = @currentParentId,@p1 = 'ABC412',@p2 = 'BMW',@p3 = '320';
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC513',@p2 = 'BMW',@p3 = '323',@pOut=@lastChildId out;
select @firstParentId FirstParentId, @lastParentId LastParentId, @firstChildId FirstChildId, @lastChildId LastChildId;

The output becomes:

FirstParentId	LastParentId	FirstChildId	LastChildId
7	9	19	27

VERSION 2

declare @currentParentId int;
declare @currentChildId int;
declare @ids table(entityId int, entityType char(1));
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800503',@p1='Hans Wertheim',@pOut=@currentParentId out;
insert into @ids (entityId, entityType) values (@currentParentId, 'p');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC111',@p2 = 'Volvo',@p3 = 'S60',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC112',@p2 = 'Volvo',@p3 = 'V70',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC113',@p2 = 'Volvo',@p3 = 'XC90',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800504',@p1='Daniel Wertheim',@pOut=@currentParentId out;
insert into @ids (entityId, entityType) values (@currentParentId, 'p');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC211',@p2 = 'Audi',@p3 = 'A3',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC212',@p2 = 'Audi',@p3 = 'A4',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC213',@p2 = 'Audi',@p3 = 'A6',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Person] ([PersonNo],[Name]) values (@p0,@p1);set @pOut=scope_identity();',N'@p0 varchar(100),@p1 varchar(100), @pOut int out', @p0='800505',@p1='Anton Wertheim',@pOut=@currentParentId out;
insert into @ids (entityId, entityType) values (@currentParentId, 'p');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC311',@p2 = 'BMW',@p3 = '316',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC412',@p2 = 'BMW',@p3 = '320',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
exec sp_executesql N'insert into [dbo].[Car] ([RelOwnerId],[RegNo],[Make],[Model]) values (@p0,@p1,@p2,@p3);set @pOut=scope_identity();',N'@p0 int,@p1 varchar(100),@p2 varchar(100),@p3 varchar(100), @pOut int out', @p0 = @currentParentId,@p1 = 'ABC513',@p2 = 'BMW',@p3 = '323',@pOut=@currentChildId out;
insert into @ids (entityId, entityType) values (@currentChildId, 'c');
select entityId, entityType from @ids order by entityType, entityId;

All you have left to do is to loop your entities in the correct order and assign the values.

Have fun.

The code

VERSION 1

[Serializable]
public class IdentityInsertRecord
{
    public IList<string> ParentValues { get; private set; }

    public IList<string[]> ChildValues { get; private set; }

    public IdentityInsertRecord(IEnumerable<string> parentValues, IEnumerable<string[]> childValues)
    {
        ParentValues = new List<string>(parentValues);
        ChildValues = new List<string[]>(childValues);
    }
}

[Serializable]
public class SqlFormats
{
    public string ParentSqlFormatWithOut { get; private set; }

    public string ChildSqlFormat { get; private set; }

    public string ChildSqlFormatWithOut { get; private set; }

    public SqlFormats(string parentSqlFormatWithOut, string childSqlFormat, string childSqlFormatWithOut)
    {
        ParentSqlFormatWithOut = parentSqlFormatWithOut;

        ChildSqlFormat = childSqlFormat;
        ChildSqlFormatWithOut = childSqlFormatWithOut;
    }
}

public class SqlBulkIdentityInsertsGenerator
{
    public string ParentTableName { get; set; }

    public List<string> ParentColumnNames { get; private set; }

    public string ChildTableName { get; set; }

    public List<string> ChildColumnNames { get; private set; }

    public string NameOfRelationalColumn { get; set; }

    public SqlBulkIdentityInsertsGenerator()
    {
        ParentTableName = string.Empty;
        ParentColumnNames = new List<string>();

        ChildTableName = string.Empty;
        ChildColumnNames = new List<string>();
        NameOfRelationalColumn = string.Empty;
    }

    private SqlFormats GenerateSqlFormats()
    {
        //TODO: Check ParentTableName, ChildTableName, ...., ....

        var parentColumnNamesString = string.Join(",", ParentColumnNames.Select(n => "[" + n + "]"));
        var childColumnNamesString = string.Join(",", ChildColumnNames.Select(n => "[" + n + "]"));

        var parentParams = new List<string>();
        var numOfInsertableParentColumns = ParentColumnNames.Count();
        for (var c = 0; c < numOfInsertableParentColumns; c++)
            parentParams.Add("@p" + c);

        var childParams = new List<string>();
        var numOfInsertableChildColumns = ChildColumnNames.Count();
        for (var c = 0; c < numOfInsertableChildColumns; c++)
            childParams.Add("@p" + (c + 1));

        var parentInsert = string.Format("insert into [dbo].[{0}] ({1}) values ({2});",
                                            ParentTableName,
                                            parentColumnNamesString,
                                            string.Join(",", parentParams));

        var childInsert = string.Format("insert into [dbo].[{0}] ([{1}],{2}) values ({3});",
                                        ChildTableName,
                                        NameOfRelationalColumn,
                                        childColumnNamesString,
                                        "@p0," + string.Join(",", childParams));

        var parentParamsString = string.Join(",", parentParams.Select(p => p + " varchar(100)"));
        var childParamsString = "@p0 int," + string.Join(",", childParams.Select(p => p + " varchar(100)"));

        var parentSqlFormatWithOut = string.Format("exec sp_executesql N'{0}set @pOut=scope_identity();',N'{1}, @pOut int out', {2};",
                                            parentInsert,
                                            parentParamsString,
                                            "{0}");

        var childSqlFormat = string.Format("exec sp_executesql N'{0}',N'{1}', {2};",
                                            childInsert,
                                            childParamsString,
                                            "{0}");

        var childSqlFormatWithOut = string.Format("exec sp_executesql N'{0}set @pOut=scope_identity();',N'{1}, @pOut int out', {2};",
                                            childInsert,
                                            childParamsString,
                                            "{0}");

        return new SqlFormats(parentSqlFormatWithOut, childSqlFormat, childSqlFormatWithOut);
    }

    public string GenerateSql(IList<IdentityInsertRecord> records)
    {
        var sqlFormats = GenerateSqlFormats();
        var sql = new StringBuilder();

        sql.AppendLine("declare @currentParentId int;");
        sql.AppendLine("declare @firstParentId int;");
        sql.AppendLine("declare @lastParentId int;");
        sql.AppendLine("declare @firstChildId int;");
        sql.AppendLine("declare @lastChildId int;");

        for (var c = 0; c < records.Count; c++)
        {
            var record = records[c];
            var isFirstRecord = c == 0;
            var isLastRecord = c == records.Count - 1;

            var parentParameters = new List<string>();
            foreach (var parentValue in record.ParentValues)
                parentParameters.Add(string.Format("@p{0}='{1}'", parentParameters.Count, parentValue));

            sql.AppendLine(string.Format(sqlFormats.ParentSqlFormatWithOut,
                                                string.Join(",", parentParameters) + ",@pOut=@currentParentId out"));

            if (isFirstRecord)
                sql.AppendLine("set @firstParentId=@currentParentId;");
            else if (isLastRecord)
                sql.AppendLine("set @lastParentId=@currentParentId;");
                
            for (var cChild = 0; cChild < record.ChildValues.Count; cChild++)
            {
                var childRow = record.ChildValues[cChild];
                var isFirstChildRow = cChild == 0;
                var isLastChildRow = cChild == record.ChildValues.Count - 1;

                var childParameters = new List<string>();
                childParameters.Add(string.Format("@p{0} = {1}", childParameters.Count, "@currentParentId"));

                foreach (var childValue in childRow)
                    childParameters.Add(string.Format("@p{0} = '{1}'", childParameters.Count, childValue));

                if (isFirstRecord && isFirstChildRow)
                    sql.AppendLine(string.Format(sqlFormats.ChildSqlFormatWithOut,
                                                    string.Join(",", childParameters) + ",@pOut=@firstChildId out"));
                else if(isLastRecord && isLastChildRow)
                    sql.AppendLine(string.Format(sqlFormats.ChildSqlFormatWithOut,
                                                    string.Join(",", childParameters) + ",@pOut=@lastChildId out"));
                else
                    sql.AppendLine(string.Format(sqlFormats.ChildSqlFormat, string.Join(",", childParameters)));
            }

            if (isLastRecord)
                sql.AppendLine(
                    "select @firstParentId FirstParentId, @lastParentId LastParentId, @firstChildId FirstChildId, @lastChildId LastChildId;");
        }

        return sql.ToString();
    }
}

VERSION 2

[Serializable]
public class IdentityInsertRecord
{
    public IList<string> ParentValues { get; private set; }

    public IList<string[]> ChildValues { get; private set; }

    public IdentityInsertRecord(IEnumerable<string> parentValues, IEnumerable<string[]> childValues)
    {
        ParentValues = new List<string>(parentValues);
        ChildValues = new List<string[]>(childValues);
    }
}

[Serializable]
public class SqlFormats
{
    public string ParentSqlFormatWithOut { get; private set; }

    public string ChildSqlFormat { get; private set; }

    public string ChildSqlFormatWithOut { get; private set; }

    public SqlFormats(string parentSqlFormatWithOut, string childSqlFormat, string childSqlFormatWithOut)
    {
        ParentSqlFormatWithOut = parentSqlFormatWithOut;

        ChildSqlFormat = childSqlFormat;
        ChildSqlFormatWithOut = childSqlFormatWithOut;
    }
}

public class SqlBulkIdentityInsertsGenerator
{
    public string ParentTableName { get; set; }

    public List<string> ParentColumnNames { get; private set; }

    public string ChildTableName { get; set; }

    public List<string> ChildColumnNames { get; private set; }

    public string NameOfRelationalColumn { get; set; }

    public SqlBulkIdentityInsertsGenerator()
    {
        ParentTableName = string.Empty;
        ParentColumnNames = new List<string>();

        ChildTableName = string.Empty;
        ChildColumnNames = new List<string>();
        NameOfRelationalColumn = string.Empty;
    }

    private SqlFormats GenerateSqlFormats()
    {
        //TODO: Check ParentTableName, ChildTableName, ...., ....

        var parentColumnNamesString = string.Join(",", ParentColumnNames.Select(n => "[" + n + "]"));
        var childColumnNamesString = string.Join(",", ChildColumnNames.Select(n => "[" + n + "]"));

        var parentParams = new List<string>();
        var numOfInsertableParentColumns = ParentColumnNames.Count();
        for (var c = 0; c < numOfInsertableParentColumns; c++)
            parentParams.Add("@p" + c);

        var childParams = new List<string>();
        var numOfInsertableChildColumns = ChildColumnNames.Count();
        for (var c = 0; c < numOfInsertableChildColumns; c++)
            childParams.Add("@p" + (c + 1));

        var parentInsert = string.Format("insert into [dbo].[{0}] ({1}) values ({2});",
                                            ParentTableName,
                                            parentColumnNamesString,
                                            string.Join(",", parentParams));

        var childInsert = string.Format("insert into [dbo].[{0}] ([{1}],{2}) values ({3});",
                                        ChildTableName,
                                        NameOfRelationalColumn,
                                        childColumnNamesString,
                                        "@p0," + string.Join(",", childParams));

        var parentParamsString = string.Join(",", parentParams.Select(p => p + " varchar(100)"));
        var childParamsString = "@p0 int," + string.Join(",", childParams.Select(p => p + " varchar(100)"));

        var parentSqlFormatWithOut = string.Format("exec sp_executesql N'{0}set @pOut=scope_identity();',N'{1}, @pOut int out', {2};",
                                            parentInsert,
                                            parentParamsString,
                                            "{0}");

        var childSqlFormat = string.Format("exec sp_executesql N'{0}',N'{1}', {2};",
                                            childInsert,
                                            childParamsString,
                                            "{0}");

        var childSqlFormatWithOut = string.Format("exec sp_executesql N'{0}set @pOut=scope_identity();',N'{1}, @pOut int out', {2};",
                                            childInsert,
                                            childParamsString,
                                            "{0}");

        return new SqlFormats(parentSqlFormatWithOut, childSqlFormat, childSqlFormatWithOut);
    }

    public string GenerateSql(IList<IdentityInsertRecord> records)
    {
        var sqlFormats = GenerateSqlFormats();
        var sql = new StringBuilder();

        sql.AppendLine("declare @currentParentId int;");
        sql.AppendLine("declare @currentChildId int;");
        sql.AppendLine("declare @ids table(entityId int, entityType char(1));");

        for (var c = 0; c < records.Count; c++)
        {
            var record = records[c];
            var isLastRecord = c == records.Count - 1;

            var parentParameters = new List<string>();
            foreach (var parentValue in record.ParentValues)
                parentParameters.Add(string.Format("@p{0}='{1}'", parentParameters.Count, parentValue));

            sql.AppendLine(string.Format(sqlFormats.ParentSqlFormatWithOut,
                                                string.Join(",", parentParameters) + ",@pOut=@currentParentId out"));

            sql.AppendLine("insert into @ids (entityId, entityType) values (@currentParentId, 'p');");
                
            foreach (var childRow in record.ChildValues)
            {
                var childParameters = new List<string>();
                childParameters.Add(string.Format("@p{0} = {1}", childParameters.Count, "@currentParentId"));

                foreach (var childValue in childRow)
                    childParameters.Add(string.Format("@p{0} = '{1}'", childParameters.Count, childValue));

                sql.AppendLine(string.Format(sqlFormats.ChildSqlFormatWithOut,
                                                string.Join(",", childParameters) + ",@pOut=@currentChildId out"));
                sql.AppendLine("insert into @ids (entityId, entityType) values (@currentChildId, 'c');");
            }

            if (isLastRecord)
                sql.AppendLine(
                    "select entityId, entityType from @ids order by entityType, entityId;");
        }

        return sql.ToString();
    }
}

//Daniel

About these ads

2 thoughts on “C# – Batch identity inserts

  1. Pingback: DotNetShoutout

  2. Pingback: DotNetHeaven.Net

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s