Fastest way to perform nested bulk inserts with scope_identity() usage?
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.
Get SCOPE_IDENTITY value when inserting bulk records for SQL TableType
I assume this is Sql Server? Then you can make use of the OUTPUT clause, like so:
declare @NewId table (MyNewId INT)
insert into Table_2(Table_A_Id,Table_1_Id)
output INSERTED.MyNewId INTO @TempTable(MyNewID)
SELECT SomeValue, SomeValueAsParameter FROM @TType_Table_A;
SELECT * FROM @NewId
T-SQL Increment Id after Insert
You can output
the inserted
rows into a temporary table, then join your @swotparts
to the temporary table based on the natural key (whatever unique column set ties them together beyond the SwotId
). This would solve the problem with resorting to loops or cursors, while also overcoming the obstacle of doing a single swot
at a time.
set nocount, xact_abort on;
create table #swot (SwotId int, SwotTypeId int);
insert into MySchema.swot (SwotTypeId)
output inserted.SwotId, inserted.SwotTypeId into #swot
select SwotTypeId
from @swots;
insert into MySchema.SwotPart(SwotId, FieldTypeId, Label)
select s.SwotId, p.FieldTypeId, p.Label
from @swotparts p
inner join #swot s
on p.SwotTypeId = p.SwotTypeId;
How can I insert multiple rows into a table and get all new identity valued in order?
Yes. the insert will always work, once you include the order by, the insert will be executed in that order.
Here I change the staging order, btw you dont need OUTPUT
SQL DEMO
insert #Staging (TrackingId, Value) values (201,1000),(204,2000),(203,2000),(202,1000);
^ ^ ^ ^
INSERT INTO #Target (Value <, otherfields>)
SELECT TrackingID <, otherfields>
FROM #Staging
ORDER BY TrackingID
;
SELECT *
FROM #Target;
Please read the comments below in that article the answer from the author:
- Could you elaborate on statement #4.
Yes, the identity values will be generated in the sequence established by the ORDER BY. If a clustered index exists on the identity column, then the values will be in the logical order of the index keys. This still doesn’t guarantee physical order of insertion. Index maintenance is a different step and that could also be done in parallel for example. So you could end up generating the identity values based on ORDER BY clause and then feeding those rows to the clustered index insert operator which will perform the maintenance task. You can see this in the query plan. You should really NOT think about physical operations or order but instead think of a table as a unordered set of rows. The index can be used to sort rows in logical manner (using ORDER BY clause) efficiently.
Related Topics
What Is the Simplest SQL Query to Find the Second Largest Value
Generate Dates Between Date Ranges
What Are Best Practices For Multi-Language Database Design
How to Order by With Union in Sql
SQL Performance on Left Outer Join VS Not Exists
How to Make a Recursive SQL Query
How to Install Freetds in Linux
MySQL :: Insert into Table, Data from Another Table
Mixing Implicit and Explicit Joins
Can a Check Constraint Relate to Another Table
MySQL Insert into Table Values.. VS Insert into Table Set
How to Return Multiple Values in One Column (T-Sql)
Database Structure For Tree Data Structure
Passing an Array of Parameters to a Stored Procedure
Sql: Creating a Relational Table With 2 Different Auto_Increment
SQL Select Speed Int VS Varchar