不太理解的插入语句，但速度快，知道的进来看。

panwen516 2009-03-17 11:46:57
if object_id('a')is not null
drop table a
create table a
(
id int primary key,
name varchar(20)
)
--第一种插入
insert into a(id,name)
select 1,'a' union
select 2,'b' union
select 3,'c'
--第二种插入
insert into a values(1,'a')
insert into a values(2,'b')
insert into a values(3,'c')

执行后消息显示：
第一种是影响3行。而第二种是3个影响1行
所以我想第一种速度很定比第二种快，但我对第一种插入不太理解，特别是select与union，望大虾仔细讲解。谢谢。
...全文
768 24 打赏 收藏 转发到动态 举报
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
yuyangyangde 2009-03-19
  • 打赏
  • 举报
 回复
up
yygyogfny 2009-03-19
  • 打赏
  • 举报
 回复
学习
jiashu912387 2009-03-19
  • 打赏
  • 举报
 回复
顶 很不错的 帖子 学习
haitao 2009-03-18
  • 打赏
  • 举报
 回复
第一种是先生成一个子查询，再把它的结果一次性插入
第二种插入多次

子查询只是sql编译的开销，插入引发的锁动作只是一次
插入多次，每次都是引发一次锁动作
jinjazz 2009-03-18
  • 打赏
  • 举报
 回复
truncate table t

declare @i int
set @i=1

while @i<=1000
begin
exec insert_t1
set @i=@i+1
end
--2秒

truncate table t

declare @i int
set @i=1

while @i<=1000
begin
exec insert_t2
set @i=@i+1
end
--20多秒

truncate table t

declare @i int
set @i=1

while @i<=1000
begin
exec insert_t3
set @i=@i+1
end
--4秒
htl258_Tony 2009-03-18
  • 打赏
  • 举报
 回复
以事实说话，严重支持近身剪老大
jinjazz 2009-03-18
  • 打赏
  • 举报
 回复
create proc insert_t3 as
begin
insert into t
select '6933FB9A-B95C-4732-9857-B6C067DE7A54' union
select '9BADDE9E-B6B5-4501-BF4D-C214108637BD' union
select 'A6ACF17D-4668-485F-8BC8-A7A1E4C7AF0D' union
select 'EB36A73B-F25E-4D2A-957E-D394069FB7B4' union
select 'BDF26FF7-BD8E-417D-B4EC-728AFD288053' union
select '97CDC2FA-42BB-41A4-9C2B-13D74B61E203' union
select 'EC7365E4-E4AB-43C4-A23F-A470E4EA626C' union
select '40224107-3084-4E3A-B973-F5A6DB6770A8' union
select 'DAA1A558-9BCF-4089-ADA5-83BA0EE1B2A9' union
select '4D9D9A9A-5ED8-4ECB-ADA2-952D4FB12666' union
select 'AD0A4DDF-791E-4F35-9861-00343FD6A552' union
select '207B54D6-A592-4134-A893-43795C8D4E63' union
select '14EA89CB-8EBB-4870-AC47-592009DD6CEF' union
select 'CBEA4858-12B4-4783-B4D3-1615BCD3F77A' union
select '6EB3C12A-B1EF-4443-B225-9603E4394BE3' union
select '52054D48-AEDF-4598-A92C-AF616476E2B8' union
select 'F10DF5BB-C4CF-4D07-9F49-955DA1CA00D8' union
select '78339BC3-AF65-4269-9801-717165096749' union
select '51796509-415C-4937-BCD1-7E7E45BC26F5' union
select '4AE32F28-3E9F-4062-94C3-FA698DC05C7B' union
select 'F896F1AB-CD84-43B8-A58F-8DEDE9571F5C' union
select 'C4A94418-CDB4-4B8D-968E-758B732214EA' union
select '89C60B64-673F-4585-BB25-24107AA1A608' union
select '1344CEE0-0269-4F47-B4C3-36B7010C79AB' union
select 'A8B501F5-53B1-47E7-A0DA-9AB1FE354C50' union
select '4EAB67FC-70C1-40D6-B861-398581BF18A1' union
select 'F6C88368-94EE-4753-B0FA-2DC84435B3D3' union
select '88BCD592-157B-413B-A5CF-7B021996D7AD' union
select 'A1824CC2-35AA-4FA0-ADED-25DA64D5BCA1' union
select '8702E29D-8077-45D2-B0D4-E6ECF376BC46' union
select 'DDCB8709-758B-40E8-9943-97ED970C68A0' union
select 'D93E5854-9CE7-4B7B-B9BD-65314B5B3CD0' union
select 'B77E8A4F-A2F5-4474-A398-97721E690FEE' union
select 'C23FEAB2-FB19-4594-AB1C-A5BF5A9B6CE6' union
select '38176B30-F377-43F0-B79D-E295CDF09E2F' union
select '04C142EC-99D4-4D47-B042-DF2C71EEB035' union
select '3AA2C710-6833-42A3-AED6-87C4E8AAFE7F' union
select '748C9076-B513-42D4-AF07-2D03679E5777' union
select '4CFF8885-7B0B-4C66-879E-D97F9F0E25D6' union
select '725AF032-A9DF-443B-891F-C698D70C67F4' union
select '1FE4ECBD-200C-4E52-9318-7D34770EE206' union
select 'A63DDF25-800C-40C6-AE0F-01BBB4181F2B' union
select '47FD6E78-D50B-4F47-89A6-D5A003C0EFDA' union
select 'F87287FB-6C9F-4F51-BB68-AA49C420D3D3' union
select '6450393E-50C4-4EE7-BA7D-C2E4FD48AABE' union
select '24BEBB23-39A4-48AE-B458-AE8F76AF05C0' union
select 'D18B9BDD-3B5A-4C7F-BBF7-7B2A235026DD' union
select '47F544EE-44BA-43A8-9D87-935817B7DEA0' union
select '4A672B5A-9CB3-4A72-9762-9B4955749B8D' union
select '82A71F99-6389-4783-9129-84258D9AC0AD' union
select 'AB69FCAE-F9C1-4050-B380-A1EC51E24ABA' union
select '096E6452-433C-49AC-8EB1-15CCA71F9B0E' union
select '578978D5-3138-478F-8D2A-8057C51D8717' union
select 'B2F7B640-392A-4723-868D-341DF547DA69' union
select '305C5B3E-EEBB-46F0-8B90-A33F5DE7051F' union
select '1ADC6E06-BA16-4895-BA60-B3E65F3619DF' union
select 'D7AEE39D-F3B5-45FC-BBD4-4E4D77303F5D' union
select 'B9858660-2B7D-4CE7-8B0D-7EFD74DAFE38' union
select '800E699E-C23B-47CB-963B-BE677EBC0A98' union
select '6ACACC0B-5CAC-462D-83D4-603CA3CB5F83' union
select 'C6E53E0D-48C3-4FD4-A3B0-E7C060556444' union
select 'E4AA2098-1CEB-408A-97C2-CF951B5DC59F' union
select '50318635-A2C2-47CD-80ED-744C6E9F18DF' union
select '96A171A4-3D4B-413D-8A57-F76950B247F6' union
select '43F025C2-33D8-4F62-8B93-4B52EECF4A2F' union
select '0A4C0D66-1B71-4E53-81DA-1570FB374288' union
select 'FD914216-1D88-44D5-896C-4DABC2657544' union
select '4B4B0DCF-DA29-4940-A568-D568EB3FDA10' union
select '08E7376A-3A8B-4CAD-A803-1D2E6843029A' union
select '8B43DD93-9D26-4CF4-AC45-625199E0F339' union
select '3F4A0D41-191A-4BC6-9913-76E73403AD5E' union
select '8FC60F7E-B10D-4404-ADCA-101259E2CC81' union
select 'CAC7CE20-F996-4265-A5FA-E2B41169D8E2' union
select '84A7A3FE-431D-49FB-9128-4DBF7EC7F93B' union
select '80266CC5-A340-4796-81D7-31A65F1CA09C' union
select '45201B8F-9ADF-4088-A42C-1DB382FEC5E2' union
select 'A20E96FF-3B33-4599-902A-6A72C5C3D4CE' union
select 'E5E13276-8439-4360-9ECD-47EDD95C58F9' union
select '1EE3D5B5-3F56-4969-AD6B-8B70D386DBA8' union
select 'AFE56987-4016-4D5E-8B44-96047A6CBB63' union
select '69FB7C77-9E45-4D4A-BD9D-48B4277B8F3E' union
select 'DFF74896-F16C-409A-8AC1-080F12D337C9' union
select '02EF1FFE-DC22-4E5A-80FC-DA625DFC291A' union
select 'D9DE5647-05AA-41F8-AE58-2E522BAAC16E' union
select '3BCC058B-6EFE-4240-9BD3-F7CEA4A7C5AB' union
select '887D47D9-3195-4896-B399-3EF472D24919' union
select 'A84118C8-58B9-4044-9710-2AD28287FF4F' union
select '11619117-B8E1-4510-8B80-9892F7F9BA8C' union
select 'F41F5524-01CD-4EB4-AC4C-195E7B19DEB4' union
select '264E5438-6D61-4BAB-B5F3-F35153DCD95E' union
select '9A0C523B-E781-4495-920B-3909B1B0F530' union
select 'FD77A80E-0820-449E-B39B-A8B9FAD2F171' union
select '44613089-0FB5-4286-AACC-745F8E76441B' union
select 'C3DE83EA-55D2-4356-9179-3D466C3A7AC8' union
select 'C59EF6BF-5A35-407F-93DE-89054BBB5399' union
select '068F1277-CD27-4D40-81FD-186E509A3EE2' union
select 'DAB5A752-EB92-42B0-AA05-3A84EC451854' union
select '1FA7ACE0-5BA3-4E03-904D-723938D005B3' union
select '680FC092-3C61-4876-B409-F30153D3FDD7' union
select 'FAE026FA-20FB-4E60-8234-1265A0751643'
end
go
ljluck7687 2009-03-18
  • 打赏
  • 举报
 回复
第一种是一条语句；第二种是三条语句，确实第一种要快。

union是联合的意思，用在sql语句中是求两个sql记录集的并集（记录集相加，重复的算一次）
jinjazz 2009-03-18
  • 打赏
  • 举报
 回复
create proc insert_t2
as
begin
insert into t select 'C3148284-5EAF-43F6-B65A-23C76581ACAB'
insert into t select '99AD2A1A-D42C-4116-A257-6B5BE7092AE2'
insert into t select '3E5467A4-60A0-413F-9FD9-886042236D76'
insert into t select '2B47F56D-952A-47FB-AE1B-A361EF8A95A9'
insert into t select '917FBF0A-1323-475C-9E5F-20D6CC564E1F'
insert into t select 'E2BDE282-DD67-4D97-AE49-E6A8B43D4C64'
insert into t select 'ACB5F65F-A4CA-4152-8C19-9A2954EAE567'
insert into t select '0E57FA6D-F18C-40DE-AF38-F819C86EB575'
insert into t select 'CE7CA8A4-875B-41DA-B88F-439122AAB7C1'
insert into t select '29ECCB08-1228-4122-AA54-E9D5D3081AE8'
insert into t select '4B84E018-89E1-4DFF-9167-3DD302DF43BD'
insert into t select '3E4E0D65-6E35-4226-A13B-7EB68249E4D9'
insert into t select '4399F954-468A-4D9D-AB6C-9A2BC4B5CE6A'
insert into t select '9D5DCF6E-DCCF-44A4-81B2-A26B3A547F8A'
insert into t select 'A660DFA4-23C6-4FED-BA3D-C06CFBE50BD7'
insert into t select '38583773-CB65-4A0C-9049-36B85992FAD1'
insert into t select '31A474EC-E06E-413C-BE73-0413CCB11536'
insert into t select '4BAD73C7-8BE9-4715-8AA6-90D2EC048BE4'
insert into t select '0CF2B716-059F-4B72-B711-E037239309AD'
insert into t select '00C99502-5C4D-484E-9554-884AC068E290'
insert into t select 'EAE2A8EE-7CD6-4091-A5F3-18E30B975EB8'
insert into t select 'F5530D73-F2DA-4AAC-B995-DA287B05E639'
insert into t select 'E8A91CF4-3EC4-4296-B051-65A0FDB23396'
insert into t select '0817C923-3CA3-4210-BBCF-614668C4BFAB'
insert into t select 'DDEAD6F0-709C-4A60-BE75-BFCA84590BBF'
insert into t select '4BE53209-E2AA-466F-A608-A36A921B14D4'
insert into t select '37FA9DE1-7D33-4814-9645-CC24ED553D70'
insert into t select '441A1D69-B0BE-4871-87EE-12ED0470729F'
insert into t select '934B7C5E-5002-40ED-B398-6263C5FDDC2D'
insert into t select '700FBB13-7921-4D9B-A6DD-1EE5E0DE06D6'
insert into t select '7D4F7704-4014-48EB-AF0B-CCD9E45F6CFD'
insert into t select '7A8CE493-6570-4B92-B00B-32686BC248CD'
insert into t select '077FD640-9655-4AC1-AC26-F72EFEC67FC2'
insert into t select 'B7BC4CD4-56FB-42D2-86B3-9BB9260EA208'
insert into t select '0DCE1185-D33A-48A1-832E-EF82F6327D21'
insert into t select '8BAF3867-DB3C-489D-A6D0-2EA1D7CFBB0D'
insert into t select 'DA6CF2A0-1322-46E9-8253-C92BB28D4CFB'
insert into t select '861DB332-29FF-4C2D-B373-BDB6181A202C'
insert into t select '0F431A31-A47A-4874-8CA3-465ACE6B16C2'
insert into t select '1A7A705B-4FCE-4B7C-B42C-732EDC7E1F38'
insert into t select 'A9643A0D-CB37-4803-ABD5-4271C67DEE0D'
insert into t select '075A741C-1A9A-4238-8E39-D2E6024993EC'
insert into t select 'E73DD053-5B16-402E-8984-FE88F2148CCD'
insert into t select '9F34382E-2149-4F91-9486-03F8B60C42CE'
insert into t select 'F97826B5-EDB6-457B-81E8-8CF89084DF15'
insert into t select '73EB8284-04D0-4B94-BF92-DE3A4F5337D6'
insert into t select 'CADE1065-F0C9-40B4-B34A-209E1AC8FBB6'
insert into t select '712EC64C-0E68-4AFF-8E3A-8F028F88E9B5'
insert into t select 'E466F11E-3A9E-42E4-B895-57FC19BE4F87'
insert into t select '415A960B-2368-4003-B429-E480DA28BC6C'
insert into t select 'B02B93CF-3EB4-4A3B-8594-A6D2BF4ADFEB'
insert into t select '1A8AB9D5-3625-4DD3-8CD3-10BAFF1D0558'
insert into t select '46564196-CF22-4F28-89A1-DAE806AB25D1'
insert into t select '484A76C4-4AA3-4763-9BF7-253BC2349571'
insert into t select 'DA88CC30-B094-4199-A1F3-282FE153B385'
insert into t select '2ACB4DB6-B47E-48D0-978C-3BCCFA54FA1F'
insert into t select '589E3C0B-BC6B-45E6-892F-51C0FF1620CC'
insert into t select '341C3604-1240-4E35-88EE-1B2481B34225'
insert into t select '578C083B-BD4B-4743-803A-202E8B2B283B'
insert into t select '8D6087C5-EBCB-4D14-AF29-FA04E1FACA15'
insert into t select '37FC087D-CFAA-4EE6-A5D3-7BF703E44EE3'
insert into t select 'BE75E7B9-CEBB-4EB7-94C8-8CDF95E4ABB4'
insert into t select '6EC3E4BC-E00C-4402-BD4A-BBB174F9F7D7'
insert into t select 'FDA49F4D-96FB-4FFC-BDEC-EA2FFED5CC4E'
insert into t select 'B54EF5FF-2F42-493F-8F45-D1E96A4EF6B1'
insert into t select '1B282A28-5983-42DF-9B68-2B203566DB5A'
insert into t select '2EF95A26-8555-431B-B154-D3E7EFE476A9'
insert into t select '21F9FEB3-3554-41EC-8493-7D4D61351A16'
insert into t select '5D32F208-011E-43CE-9D51-F25242D32B33'
insert into t select 'A241FCCE-EB62-4F40-88D4-242617AB5855'
insert into t select '5B60FB20-9642-483D-A5BD-0141180B63BE'
insert into t select 'B1DD4CDB-3D46-4E2C-A08B-5A1D5E95302F'
insert into t select '911638D7-A062-4FFC-A26B-65A6D390B822'
insert into t select '8AF2E19E-9227-4B10-ACBE-C17A08E4E18A'
insert into t select 'B76805BD-D405-440D-8EB4-DA5A2E26D891'
insert into t select 'D6DCEAF0-2023-4D8D-A260-B19A1A6BA31F'
insert into t select 'BB46C205-EE05-4192-B417-5C78EBCE87E2'
insert into t select '03C418DB-E0AF-4831-84CC-777158377A56'
insert into t select 'A9D9140C-5D0A-432A-8AE3-C15DB3F4D596'
insert into t select 'D068358E-E10D-42ED-B5B5-446A286C4D5D'
insert into t select 'CEA870AF-AC77-4468-86B0-6CD35E97726D'
insert into t select '65A4BB61-580F-43FD-A8D7-7A33C802EA44'
insert into t select '05580013-DDAA-4D58-B483-671C77BE6F0E'
insert into t select '994C570B-B531-467C-A606-73F47724A48C'
insert into t select '04DD7616-26DD-4900-A484-C6B8DD3D048E'
insert into t select '477D6485-22B3-447F-90E3-B53FFF01FF52'
insert into t select '6BD7DEBF-7A3E-46B6-8817-8D3465DA3A97'
insert into t select 'B76998CE-B736-461E-8D56-D11CE4BBACB5'
insert into t select '6C81E006-6662-46B1-95AF-99238BD494A4'
insert into t select '3094343C-9985-4604-9A13-A76FA6598772'
insert into t select 'B3EF469D-32EA-414E-A0C4-4844AE7725CD'
insert into t select '93A456D1-8458-45E4-B780-6025D41F1E93'
insert into t select '2640AA4D-AFB7-4C76-A8DC-DCA49A488E97'
insert into t select '69228EAD-5A9A-48E5-BA08-722A820AB016'
insert into t select '51D49A1C-0FEB-4CA0-98CA-1A4872866944'
insert into t select '902F0C93-6E22-4311-84FD-D84EAC56E45D'
insert into t select 'EE2D31D5-B869-4786-AFD9-528FE8005FB4'
insert into t select '14472E7D-9F31-4700-86ED-0085B64A0D80'
insert into t select '952A85F1-C51D-4B02-8870-DF4AD65F7852'
insert into t select '04003DDC-2BA4-44EA-B53E-A16045BE0B23'
end
go
jinjazz 2009-03-18
  • 打赏
  • 举报
 回复
create proc insert_t1 as
begin
insert into t
select '6933FB9A-B95C-4732-9857-B6C067DE7A54' union all
select '9BADDE9E-B6B5-4501-BF4D-C214108637BD' union all
select 'A6ACF17D-4668-485F-8BC8-A7A1E4C7AF0D' union all
select 'EB36A73B-F25E-4D2A-957E-D394069FB7B4' union all
select 'BDF26FF7-BD8E-417D-B4EC-728AFD288053' union all
select '97CDC2FA-42BB-41A4-9C2B-13D74B61E203' union all
select 'EC7365E4-E4AB-43C4-A23F-A470E4EA626C' union all
select '40224107-3084-4E3A-B973-F5A6DB6770A8' union all
select 'DAA1A558-9BCF-4089-ADA5-83BA0EE1B2A9' union all
select '4D9D9A9A-5ED8-4ECB-ADA2-952D4FB12666' union all
select 'AD0A4DDF-791E-4F35-9861-00343FD6A552' union all
select '207B54D6-A592-4134-A893-43795C8D4E63' union all
select '14EA89CB-8EBB-4870-AC47-592009DD6CEF' union all
select 'CBEA4858-12B4-4783-B4D3-1615BCD3F77A' union all
select '6EB3C12A-B1EF-4443-B225-9603E4394BE3' union all
select '52054D48-AEDF-4598-A92C-AF616476E2B8' union all
select 'F10DF5BB-C4CF-4D07-9F49-955DA1CA00D8' union all
select '78339BC3-AF65-4269-9801-717165096749' union all
select '51796509-415C-4937-BCD1-7E7E45BC26F5' union all
select '4AE32F28-3E9F-4062-94C3-FA698DC05C7B' union all
select 'F896F1AB-CD84-43B8-A58F-8DEDE9571F5C' union all
select 'C4A94418-CDB4-4B8D-968E-758B732214EA' union all
select '89C60B64-673F-4585-BB25-24107AA1A608' union all
select '1344CEE0-0269-4F47-B4C3-36B7010C79AB' union all
select 'A8B501F5-53B1-47E7-A0DA-9AB1FE354C50' union all
select '4EAB67FC-70C1-40D6-B861-398581BF18A1' union all
select 'F6C88368-94EE-4753-B0FA-2DC84435B3D3' union all
select '88BCD592-157B-413B-A5CF-7B021996D7AD' union all
select 'A1824CC2-35AA-4FA0-ADED-25DA64D5BCA1' union all
select '8702E29D-8077-45D2-B0D4-E6ECF376BC46' union all
select 'DDCB8709-758B-40E8-9943-97ED970C68A0' union all
select 'D93E5854-9CE7-4B7B-B9BD-65314B5B3CD0' union all
select 'B77E8A4F-A2F5-4474-A398-97721E690FEE' union all
select 'C23FEAB2-FB19-4594-AB1C-A5BF5A9B6CE6' union all
select '38176B30-F377-43F0-B79D-E295CDF09E2F' union all
select '04C142EC-99D4-4D47-B042-DF2C71EEB035' union all
select '3AA2C710-6833-42A3-AED6-87C4E8AAFE7F' union all
select '748C9076-B513-42D4-AF07-2D03679E5777' union all
select '4CFF8885-7B0B-4C66-879E-D97F9F0E25D6' union all
select '725AF032-A9DF-443B-891F-C698D70C67F4' union all
select '1FE4ECBD-200C-4E52-9318-7D34770EE206' union all
select 'A63DDF25-800C-40C6-AE0F-01BBB4181F2B' union all
select '47FD6E78-D50B-4F47-89A6-D5A003C0EFDA' union all
select 'F87287FB-6C9F-4F51-BB68-AA49C420D3D3' union all
select '6450393E-50C4-4EE7-BA7D-C2E4FD48AABE' union all
select '24BEBB23-39A4-48AE-B458-AE8F76AF05C0' union all
select 'D18B9BDD-3B5A-4C7F-BBF7-7B2A235026DD' union all
select '47F544EE-44BA-43A8-9D87-935817B7DEA0' union all
select '4A672B5A-9CB3-4A72-9762-9B4955749B8D' union all
select '82A71F99-6389-4783-9129-84258D9AC0AD' union all
select 'AB69FCAE-F9C1-4050-B380-A1EC51E24ABA' union all
select '096E6452-433C-49AC-8EB1-15CCA71F9B0E' union all
select '578978D5-3138-478F-8D2A-8057C51D8717' union all
select 'B2F7B640-392A-4723-868D-341DF547DA69' union all
select '305C5B3E-EEBB-46F0-8B90-A33F5DE7051F' union all
select '1ADC6E06-BA16-4895-BA60-B3E65F3619DF' union all
select 'D7AEE39D-F3B5-45FC-BBD4-4E4D77303F5D' union all
select 'B9858660-2B7D-4CE7-8B0D-7EFD74DAFE38' union all
select '800E699E-C23B-47CB-963B-BE677EBC0A98' union all
select '6ACACC0B-5CAC-462D-83D4-603CA3CB5F83' union all
select 'C6E53E0D-48C3-4FD4-A3B0-E7C060556444' union all
select 'E4AA2098-1CEB-408A-97C2-CF951B5DC59F' union all
select '50318635-A2C2-47CD-80ED-744C6E9F18DF' union all
select '96A171A4-3D4B-413D-8A57-F76950B247F6' union all
select '43F025C2-33D8-4F62-8B93-4B52EECF4A2F' union all
select '0A4C0D66-1B71-4E53-81DA-1570FB374288' union all
select 'FD914216-1D88-44D5-896C-4DABC2657544' union all
select '4B4B0DCF-DA29-4940-A568-D568EB3FDA10' union all
select '08E7376A-3A8B-4CAD-A803-1D2E6843029A' union all
select '8B43DD93-9D26-4CF4-AC45-625199E0F339' union all
select '3F4A0D41-191A-4BC6-9913-76E73403AD5E' union all
select '8FC60F7E-B10D-4404-ADCA-101259E2CC81' union all
select 'CAC7CE20-F996-4265-A5FA-E2B41169D8E2' union all
select '84A7A3FE-431D-49FB-9128-4DBF7EC7F93B' union all
select '80266CC5-A340-4796-81D7-31A65F1CA09C' union all
select '45201B8F-9ADF-4088-A42C-1DB382FEC5E2' union all
select 'A20E96FF-3B33-4599-902A-6A72C5C3D4CE' union all
select 'E5E13276-8439-4360-9ECD-47EDD95C58F9' union all
select '1EE3D5B5-3F56-4969-AD6B-8B70D386DBA8' union all
select 'AFE56987-4016-4D5E-8B44-96047A6CBB63' union all
select '69FB7C77-9E45-4D4A-BD9D-48B4277B8F3E' union all
select 'DFF74896-F16C-409A-8AC1-080F12D337C9' union all
select '02EF1FFE-DC22-4E5A-80FC-DA625DFC291A' union all
select 'D9DE5647-05AA-41F8-AE58-2E522BAAC16E' union all
select '3BCC058B-6EFE-4240-9BD3-F7CEA4A7C5AB' union all
select '887D47D9-3195-4896-B399-3EF472D24919' union all
select 'A84118C8-58B9-4044-9710-2AD28287FF4F' union all
select '11619117-B8E1-4510-8B80-9892F7F9BA8C' union all
select 'F41F5524-01CD-4EB4-AC4C-195E7B19DEB4' union all
select '264E5438-6D61-4BAB-B5F3-F35153DCD95E' union all
select '9A0C523B-E781-4495-920B-3909B1B0F530' union all
select 'FD77A80E-0820-449E-B39B-A8B9FAD2F171' union all
select '44613089-0FB5-4286-AACC-745F8E76441B' union all
select 'C3DE83EA-55D2-4356-9179-3D466C3A7AC8' union all
select 'C59EF6BF-5A35-407F-93DE-89054BBB5399' union all
select '068F1277-CD27-4D40-81FD-186E509A3EE2' union all
select 'DAB5A752-EB92-42B0-AA05-3A84EC451854' union all
select '1FA7ACE0-5BA3-4E03-904D-723938D005B3' union all
select '680FC092-3C61-4876-B409-F30153D3FDD7' union all
select 'FAE026FA-20FB-4E60-8234-1265A0751643'
end
go
jinjazz 2009-03-18
  • 打赏
  • 举报
 回复
create table t(a varchar(100))
go
jinjazz 2009-03-18
  • 打赏
  • 举报
 回复
事实证明俺是错的

测试条件，用union，union all和逐条插入，测试插入一个字段，每批100条，测试1000批

union all是union效率的2倍，是逐条插入的10倍
jinjazz 2009-03-18
  • 打赏
  • 举报
 回复
速度差不多吧，我倒觉得第二个快，第一个还要union

不管是union还是union all 都要占用额外的内存空间或者tempdb空间，第二个直接插入了
让你望见影子的墙 2009-03-18
  • 打赏
  • 举报
 回复
第一种是一次插入多行数据，下面的查询会形成一个结果集，然后把结果集插入到表中。
水族杰纶 2009-03-18
  • 打赏
  • 举报
 回复 
insert into a(id,name) --union all 效率高于union

select 1,'a'  union all  

select 2,'b'  union all

select 3,'c'
panwen516 2009-03-18
  • 打赏
  • 举报
 回复
那我上面的见解，第一种插入比第二种快（当然是在大量数据同时插入的情况下），没有说错吧？为什么？
qizhengsheng 2009-03-17
  • 打赏
  • 举报
 回复
又学习了
ws_hgo 2009-03-17
  • 打赏
  • 举报
 回复
第一种还有这种写法
insert into a(id,name) select 1,'a'
还有这种写法
select 字段名1,字段名1 into '你要插入的表明' from 从那个表获取值
htl258_Tony 2009-03-17
  • 打赏
  • 举报
 回复
SQL2000 支持两种插入，
1.insert tb values( )
2.insert select ... {union all}...

SQL2005除上面两种插入方式外多了一种插入方式
3.insert tb exec ...
w504809427 2009-03-17
  • 打赏
  • 举报
 回复
我顶啊
加载更多回复（2）
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题：1. 自己写了SQL却不知道对不对。2. 在上线后发现原有的数据内容SQL语句执⾏结果没问题， 但是数据变化了以后SQL返回的结果就错误了。3. 稍微复杂⼀点的SQL就不知道该如何写了。4. 看不懂同⾏写的SQL是为什么。5. 不知道索引的原理。6. 不知道怎么加索引能够提⾼查询性能纵观全网的教程都只停留在概念解析的层面，没有深入剖析原理，所以看了那么多的教程，可还是不会写。本教程与其他教程不同带你深入了解原理，让你真正了解什么是SQL语句，让你懂得SQL语句的编写⽅法。不再为SQL语句而烦恼。真正做到面对任何问题能够判断出SQL语句是不是能够实现，该怎样实现。 
数据库语句执行过程理解，存储引擎的学习，字符集的理解，加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练
数据库语句执行过程理解加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练
MySQL语句执行过程以及三大日志
前言 当初看MySQL45讲时做的笔记，发布出来记录下自己的学习心得吧。 查询缓存 连接建立完成后，你就可以执行select语句了。执行逻辑就会来到第二步：查询缓存。 MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。 如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成
[深入理解Android卷一全文-第七章]深入理解Audio系统
由于《深入理解Android 卷一》和《深入理解Android卷二》不再出版，而知识的传播不应该因为纸质媒介的问题而中断，所以我将在CSDN博客中全文转发这两本书的全部内容。第7章  深入理解Audio系统本章主要内容·  详细分析AudioTrack。·  详细分析AudioFlinger。·  详细分析AudioPolicyService。本章涉及的源代码文件名及位置下面是本章分析的源码文件名
深入理解计算机系统（CSAPP）含lab详解 完结
深入理解计算机操作系统-第一章 1.1 信息就是位 + 上下文 #include <stdio.h> int main() { printf("hello, world\n"); return 0; } hello.c的 ascii文本表示 像 hello.c 这样只由 ASCII 字符构成的文件称为文本文件，所有其他文件都称为二进制文件。 hello.c 的表示方法说明了一个基本思想∶系统中所有的信息——包括磁盘文件、内存中的程序、内存中存放的用户数据以及网络上传送的数据
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章