Fastest way to perform nested bulk inserts with scope_identity() usage?

by

I’ve written something like this before, but in the end didn’t get the chance to test it for performance.
To insert data into two related table I’ve used a single stored procedure and some T-SQL tricks like table valued parameters, merge and output clause.
Here is a sample script:

First, create the sample tables: tblBase and tblRelated are linked with a one to many foreign key.

CREATE TABLE tblBase 
(
    base_id int identity(1,1) primary key, 
    base_data int
); 

CREATE TABLE tblRelated
(
    related_base_id int foreign key references tblBase (base_id), 
    related_Id int identity(1,1) primary key, 
    related_data int
);

Now that we have sample tables, we can start creating the stored procedure for insert.

Create user defined table types:
For this stored procedure, we need to use three user defined table types:
One for the base table,
one for the related table,
and one to map the temporary id to the database-assigned id.

CREATE TYPE udt_base As Table
(
    base_id int,
    base_temp_id int,  -- NOTE: when populating the table valued parameters, this column holds the id used to connect the base and related tables.

    base_data int  
);

CREATE TYPE udt_related As Table
(
    related_base_id int,
    related_data int
);

CREATE TYPE udt_idMap as table
(
    temp_id int,
    id int
)
GO

Create the stored procedure:
Now that we have the user defined types, we can create the stored procedure to use them.

CREATE PROCEDURE stp_InsertMultipleRecordsToMultipleTables
(
    @base as dbo.udt_base readonly,
    @related as dbo.udt_related readonly
)
AS

DECLARE @idMap as dbo.udt_idMap
MERGE INTO tblBase USING @base AS temp ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
    INSERT (base_data)
    VALUES (temp.base_data)
    OUTPUT temp.base_temp_id, inserted.base_id -- Here we use the base_temp_id to map to the correct id
    INTO @idMap (temp_id, id);

INSERT INTO tblRelated(related_base_id, related_data)
SELECT id, related_data
FROM @related r
INNER JOIN @idMap m ON(r.related_base_id = m.temp_id) -- here we use the map to insert the related records with the correct base ids


GO

Testing:
Tested on 10 records in the base table and a 1000 records in the related table, executed in less then 1 second.
Here is the script for the test:

-- declare and populate table parameters to send to the procedure
DECLARE @base as dbo.udt_base,
        @related as dbo.udt_related

INSERT INTO @base (base_temp_id, base_data) VALUES 
(10, 10), (20, 20), (30, 30), (40, 40), (50, 50), (60, 60), (70, 70), (80, 80), (90, 90), (100, 100)

INSERT INTO @related(related_base_id, related_data) VALUES 
(10, 100), (10, 101), (10, 102), (10, 103), (10, 104), (10, 105), (10, 106), (10, 107), (10, 108), (10, 109), 
(10, 110), (10, 111), (10, 112), (10, 113), (10, 114), (10, 115), (10, 116), (10, 117), (10, 118), (10, 119), 
(10, 120), (10, 121), (10, 122), (10, 123), (10, 124), (10, 125), (10, 126), (10, 127), (10, 128), (10, 129), 
(10, 130), (10, 131), (10, 132), (10, 133), (10, 134), (10, 135), (10, 136), (10, 137), (10, 138), (10, 139), 
(10, 140), (10, 141), (10, 142), (10, 143), (10, 144), (10, 145), (10, 146), (10, 147), (10, 148), (10, 149), 
(10, 150), (10, 151), (10, 152), (10, 153), (10, 154), (10, 155), (10, 156), (10, 157), (10, 158), (10, 159), 
(10, 160), (10, 161), (10, 162), (10, 163), (10, 164), (10, 165), (10, 166), (10, 167), (10, 168), (10, 169), 
(10, 170), (10, 171), (10, 172), (10, 173), (10, 174), (10, 175), (10, 176), (10, 177), (10, 178), (10, 179), 
(10, 180), (10, 181), (10, 182), (10, 183), (10, 184), (10, 185), (10, 186), (10, 187), (10, 188), (10, 189), 
(10, 190), (10, 191), (10, 192), (10, 193), (10, 194), (10, 195), (10, 196), (10, 197), (10, 198), (10, 199), 
(20, 200), (20, 201), (20, 202), (20, 203), (20, 204), (20, 205), (20, 206), (20, 207), (20, 208), (20, 209), 
(20, 210), (20, 211), (20, 212), (20, 213), (20, 214), (20, 215), (20, 216), (20, 217), (20, 218), (20, 219), 
(20, 220), (20, 221), (20, 222), (20, 223), (20, 224), (20, 225), (20, 226), (20, 227), (20, 228), (20, 229), 
(20, 230), (20, 231), (20, 232), (20, 233), (20, 234), (20, 235), (20, 236), (20, 237), (20, 238), (20, 239), 
(20, 240), (20, 241), (20, 242), (20, 243), (20, 244), (20, 245), (20, 246), (20, 247), (20, 248), (20, 249), 
(20, 250), (20, 251), (20, 252), (20, 253), (20, 254), (20, 255), (20, 256), (20, 257), (20, 258), (20, 259), 
(20, 260), (20, 261), (20, 262), (20, 263), (20, 264), (20, 265), (20, 266), (20, 267), (20, 268), (20, 269), 
(20, 270), (20, 271), (20, 272), (20, 273), (20, 274), (20, 275), (20, 276), (20, 277), (20, 278), (20, 279), 
(20, 280), (20, 281), (20, 282), (20, 283), (20, 284), (20, 285), (20, 286), (20, 287), (20, 288), (20, 289), 
(20, 290), (20, 291), (20, 292), (20, 293), (20, 294), (20, 295), (20, 296), (20, 297), (20, 298), (20, 299), 
(30, 300), (30, 301), (30, 302), (30, 303), (30, 304), (30, 305), (30, 306), (30, 307), (30, 308), (30, 309), 
(30, 310), (30, 311), (30, 312), (30, 313), (30, 314), (30, 315), (30, 316), (30, 317), (30, 318), (30, 319), 
(30, 320), (30, 321), (30, 322), (30, 323), (30, 324), (30, 325), (30, 326), (30, 327), (30, 328), (30, 329), 
(30, 330), (30, 331), (30, 332), (30, 333), (30, 334), (30, 335), (30, 336), (30, 337), (30, 338), (30, 339), 
(30, 340), (30, 341), (30, 342), (30, 343), (30, 344), (30, 345), (30, 346), (30, 347), (30, 348), (30, 349), 
(30, 350), (30, 351), (30, 352), (30, 353), (30, 354), (30, 355), (30, 356), (30, 357), (30, 358), (30, 359), 
(30, 360), (30, 361), (30, 362), (30, 363), (30, 364), (30, 365), (30, 366), (30, 367), (30, 368), (30, 369), 
(30, 370), (30, 371), (30, 372), (30, 373), (30, 374), (30, 375), (30, 376), (30, 377), (30, 378), (30, 379), 
(30, 380), (30, 381), (30, 382), (30, 383), (30, 384), (30, 385), (30, 386), (30, 387), (30, 388), (30, 389), 
(30, 390), (30, 391), (30, 392), (30, 393), (30, 394), (30, 395), (30, 396), (30, 397), (30, 398), (30, 399), 
(40, 400), (40, 401), (40, 402), (40, 403), (40, 404), (40, 405), (40, 406), (40, 407), (40, 408), (40, 409), 
(40, 410), (40, 411), (40, 412), (40, 413), (40, 414), (40, 415), (40, 416), (40, 417), (40, 418), (40, 419), 
(40, 420), (40, 421), (40, 422), (40, 423), (40, 424), (40, 425), (40, 426), (40, 427), (40, 428), (40, 429), 
(40, 430), (40, 431), (40, 432), (40, 433), (40, 434), (40, 435), (40, 436), (40, 437), (40, 438), (40, 439), 
(40, 440), (40, 441), (40, 442), (40, 443), (40, 444), (40, 445), (40, 446), (40, 447), (40, 448), (40, 449), 
(40, 450), (40, 451), (40, 452), (40, 453), (40, 454), (40, 455), (40, 456), (40, 457), (40, 458), (40, 459), 
(40, 460), (40, 461), (40, 462), (40, 463), (40, 464), (40, 465), (40, 466), (40, 467), (40, 468), (40, 469), 
(40, 470), (40, 471), (40, 472), (40, 473), (40, 474), (40, 475), (40, 476), (40, 477), (40, 478), (40, 479), 
(40, 480), (40, 481), (40, 482), (40, 483), (40, 484), (40, 485), (40, 486), (40, 487), (40, 488), (40, 489), 
(40, 490), (40, 491), (40, 492), (40, 493), (40, 494), (40, 495), (40, 496), (40, 497), (40, 498), (40, 499), 
(50, 500), (50, 501), (50, 502), (50, 503), (50, 504), (50, 505), (50, 506), (50, 507), (50, 508), (50, 509), 
(50, 510), (50, 511), (50, 512), (50, 513), (50, 514), (50, 515), (50, 516), (50, 517), (50, 518), (50, 519), 
(50, 520), (50, 521), (50, 522), (50, 523), (50, 524), (50, 525), (50, 526), (50, 527), (50, 528), (50, 529), 
(50, 530), (50, 531), (50, 532), (50, 533), (50, 534), (50, 535), (50, 536), (50, 537), (50, 538), (50, 539), 
(50, 540), (50, 541), (50, 542), (50, 543), (50, 544), (50, 545), (50, 546), (50, 547), (50, 548), (50, 549), 
(50, 550), (50, 551), (50, 552), (50, 553), (50, 554), (50, 555), (50, 556), (50, 557), (50, 558), (50, 559), 
(50, 560), (50, 561), (50, 562), (50, 563), (50, 564), (50, 565), (50, 566), (50, 567), (50, 568), (50, 569), 
(50, 570), (50, 571), (50, 572), (50, 573), (50, 574), (50, 575), (50, 576), (50, 577), (50, 578), (50, 579), 
(50, 580), (50, 581), (50, 582), (50, 583), (50, 584), (50, 585), (50, 586), (50, 587), (50, 588), (50, 589), 
(50, 590), (50, 591), (50, 592), (50, 593), (50, 594), (50, 595), (50, 596), (50, 597), (50, 598), (50, 599), 
(60, 600), (60, 601), (60, 602), (60, 603), (60, 604), (60, 605), (60, 606), (60, 607), (60, 608), (60, 609), 
(60, 610), (60, 611), (60, 612), (60, 613), (60, 614), (60, 615), (60, 616), (60, 617), (60, 618), (60, 619), 
(60, 620), (60, 621), (60, 622), (60, 623), (60, 624), (60, 625), (60, 626), (60, 627), (60, 628), (60, 629), 
(60, 630), (60, 631), (60, 632), (60, 633), (60, 634), (60, 635), (60, 636), (60, 637), (60, 638), (60, 639), 
(60, 640), (60, 641), (60, 642), (60, 643), (60, 644), (60, 645), (60, 646), (60, 647), (60, 648), (60, 649), 
(60, 650), (60, 651), (60, 652), (60, 653), (60, 654), (60, 655), (60, 656), (60, 657), (60, 658), (60, 659), 
(60, 660), (60, 661), (60, 662), (60, 663), (60, 664), (60, 665), (60, 666), (60, 667), (60, 668), (60, 669), 
(60, 670), (60, 671), (60, 672), (60, 673), (60, 674), (60, 675), (60, 676), (60, 677), (60, 678), (60, 679), 
(60, 680), (60, 681), (60, 682), (60, 683), (60, 684), (60, 685), (60, 686), (60, 687), (60, 688), (60, 689), 
(60, 690), (60, 691), (60, 692), (60, 693), (60, 694), (60, 695), (60, 696), (60, 697), (60, 698), (60, 699), 
(70, 700), (70, 701), (70, 702), (70, 703), (70, 704), (70, 705), (70, 706), (70, 707), (70, 708), (70, 709), 
(70, 710), (70, 711), (70, 712), (70, 713), (70, 714), (70, 715), (70, 716), (70, 717), (70, 718), (70, 719), 
(70, 720), (70, 721), (70, 722), (70, 723), (70, 724), (70, 725), (70, 726), (70, 727), (70, 728), (70, 729), 
(70, 730), (70, 731), (70, 732), (70, 733), (70, 734), (70, 735), (70, 736), (70, 737), (70, 738), (70, 739), 
(70, 740), (70, 741), (70, 742), (70, 743), (70, 744), (70, 745), (70, 746), (70, 747), (70, 748), (70, 749), 
(70, 750), (70, 751), (70, 752), (70, 753), (70, 754), (70, 755), (70, 756), (70, 757), (70, 758), (70, 759), 
(70, 760), (70, 761), (70, 762), (70, 763), (70, 764), (70, 765), (70, 766), (70, 767), (70, 768), (70, 769), 
(70, 770), (70, 771), (70, 772), (70, 773), (70, 774), (70, 775), (70, 776), (70, 777), (70, 778), (70, 779), 
(70, 780), (70, 781), (70, 782), (70, 783), (70, 784), (70, 785), (70, 786), (70, 787), (70, 788), (70, 789), 
(70, 790), (70, 791), (70, 792), (70, 793), (70, 794), (70, 795), (70, 796), (70, 797), (70, 798), (70, 799), 
(80, 800), (80, 801), (80, 802), (80, 803), (80, 804), (80, 805), (80, 806), (80, 807), (80, 808), (80, 809), 
(80, 810), (80, 811), (80, 812), (80, 813), (80, 814), (80, 815), (80, 816), (80, 817), (80, 818), (80, 819), 
(80, 820), (80, 821), (80, 822), (80, 823), (80, 824), (80, 825), (80, 826), (80, 827), (80, 828), (80, 829), 
(80, 830), (80, 831), (80, 832), (80, 833), (80, 834), (80, 835), (80, 836), (80, 837), (80, 838), (80, 839), 
(80, 840), (80, 841), (80, 842), (80, 843), (80, 844), (80, 845), (80, 846), (80, 847), (80, 848), (80, 849), 
(80, 850), (80, 851), (80, 852), (80, 853), (80, 854), (80, 855), (80, 856), (80, 857), (80, 858), (80, 859), 
(80, 860), (80, 861), (80, 862), (80, 863), (80, 864), (80, 865), (80, 866), (80, 867), (80, 868), (80, 869), 
(80, 870), (80, 871), (80, 872), (80, 873), (80, 874), (80, 875), (80, 876), (80, 877), (80, 878), (80, 879), 
(80, 880), (80, 881), (80, 882), (80, 883), (80, 884), (80, 885), (80, 886), (80, 887), (80, 888), (80, 889), 
(80, 890), (80, 891), (80, 892), (80, 893), (80, 894), (80, 895), (80, 896), (80, 897), (80, 898), (80, 899), 
(90, 900), (90, 901), (90, 902), (90, 903), (90, 904), (90, 905), (90, 906), (90, 907), (90, 908), (90, 909), 
(90, 910), (90, 911), (90, 912), (90, 913), (90, 914), (90, 915), (90, 916), (90, 917), (90, 918), (90, 919), 
(90, 920), (90, 921), (90, 922), (90, 923), (90, 924), (90, 925), (90, 926), (90, 927), (90, 928), (90, 929), 
(90, 930), (90, 931), (90, 932), (90, 933), (90, 934), (90, 935), (90, 936), (90, 937), (90, 938), (90, 939), 
(90, 940), (90, 941), (90, 942), (90, 943), (90, 944), (90, 945), (90, 946), (90, 947), (90, 948), (90, 949), 
(90, 950), (90, 951), (90, 952), (90, 953), (90, 954), (90, 955), (90, 956), (90, 957), (90, 958), (90, 959), 
(90, 960), (90, 961), (90, 962), (90, 963), (90, 964), (90, 965), (90, 966), (90, 967), (90, 968), (90, 969), 
(90, 970), (90, 971), (90, 972), (90, 973), (90, 974), (90, 975), (90, 976), (90, 977), (90, 978), (90, 979), 
(90, 980), (90, 981), (90, 982), (90, 983), (90, 984), (90, 985), (90, 986), (90, 987), (90, 988), (90, 989), 
(90, 990), (90, 991), (90, 992), (90, 993), (90, 994), (90, 995), (90, 996), (90, 997), (90, 998), (90, 999), 
(100, 1000), (100, 1001), (100, 1002), (100, 1003), (100, 1004), (100, 1005), (100, 1006), (100, 1007), (100, 1008), (100, 1009), 
(100, 1010), (100, 1011), (100, 1012), (100, 1013), (100, 1014), (100, 1015), (100, 1016), (100, 1017), (100, 1018), (100, 1019), 
(100, 1020), (100, 1021), (100, 1022), (100, 1023), (100, 1024), (100, 1025), (100, 1026), (100, 1027), (100, 1028), (100, 1029), 
(100, 1030), (100, 1031), (100, 1032), (100, 1033), (100, 1034), (100, 1035), (100, 1036), (100, 1037), (100, 1038), (100, 1039), 
(100, 1040), (100, 1041), (100, 1042), (100, 1043), (100, 1044), (100, 1045), (100, 1046), (100, 1047), (100, 1048), (100, 1049), 
(100, 1050), (100, 1051), (100, 1052), (100, 1053), (100, 1054), (100, 1055), (100, 1056), (100, 1057), (100, 1058), (100, 1059), 
(100, 1060), (100, 1061), (100, 1062), (100, 1063), (100, 1064), (100, 1065), (100, 1066), (100, 1067), (100, 1068), (100, 1069), 
(100, 1070), (100, 1071), (100, 1072), (100, 1073), (100, 1074), (100, 1075), (100, 1076), (100, 1077), (100, 1078), (100, 1079), 
(100, 1080), (100, 1081), (100, 1082), (100, 1083), (100, 1084), (100, 1085), (100, 1086), (100, 1087), (100, 1088), (100, 1089), 
(100, 1090), (100, 1091), (100, 1092), (100, 1093), (100, 1094), (100, 1095), (100, 1096), (100, 1097), (100, 1098), (100, 1099)

-- execute the stored procedure
EXEC stp_InsertMultipleRecordsToMultipleTables @base, @related

-- test that the values are correct
SELECT *
FROM tblBase 
INNER JOIN tblRelated ON(base_id = related_base_id)

Clean up:

DROP TABLE tblRelated;
DROP TABLE tblBase;
DROP PROCEDURE stp_InsertMultipleRecordsToMultipleTables;
DROP TYPE udt_base;
DROP TYPE udt_related;
DROP TYPE udt_idMap;
GO

Side note:
This answer is the inspiration to a blog post entitled Insert multiple parents with multiple children in a single stored procedure on my blog.
It has a slightly different version of the code, but the idea is just the same.

More Related Contents:

  1. Closing connection when using Dapper
  2. Parameterise table name in .NET/SQL?
  3. What is wrong with this DB Diagram? [closed]
  4. I’m getting ‘Column name is invalid in the select list’ error when i execute the query
  5. SQL Server Subquery a
  6. Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  7. Efficiently convert rows to columns in sql server
  8. Not equal != operator on NULL
  9. SET NOCOUNT ON usage
  10. How can I select the first day of a month in SQL?
  11. T-SQL – Insert Data into Parent and Child Tables
  12. Copy tables from one database to another in SQL Server
  13. error, string or binary data would be truncated when trying to insert
  14. Difference between CTE and SubQuery?
  15. Constraint for only one record marked as default
  16. Find the smallest unused number in SQL Server
  17. How to Replace Multiple Characters in SQL?
  18. Why is a UDF so much slower than a subquery?
  19. Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?
  20. SQL comma-separated row with Group By clause
  21. Get everything after and before certain character in SQL Server
  22. T-SQL trim &nbsp (and other non-alphanumeric characters)
  23. I want to use CASE statement to update some records in sql server 2005
  24. Sql Server trigger insert values from new row into another table
  25. How to return a incremental group number per group in SQL
  26. Retrieve column names and types of a stored procedure? [duplicate]
  27. Getting the Id of a row I updated in Sql Server
  28. Zero SQL deadlock by design – any coding patterns?
  29. UNION ALL vs OR condition in sql server query
  30. How to Execute SQL Query without Displaying results

Leave a Comment