SQL\u8868\u5185\/\u8868\u95f4\u6570\u636e\u590d\u5236<\/p>\n
\u901a\u5e38SQLServer\u5728\u65b0\u589e\u6570\u636e\u65f6\uff0c\u7528insert into \uff08f1,f2,f3…\uff09values \uff08v1,v2,v3…\uff09\uff0c\u4f46\u5982\u679c\u63d2\u5165\u8868\u7684\u5b57\u6bb5\u5f88\u591a\uff0c\u5199\u8d77\u6765\u6709\u70b9\u9ebb\u70e6\uff0c\u53ef\u4ee5\u901a\u8fc7\u52a8\u6001\u65b9\u5f0f\u53bb\u83b7\u53d6\u6570\u636e\u5217\u5e76\u6309\u6761\u4ef6\u65b0\u589e\u63d2\u5165\u3002<\/p>\n
SQL Server 2017\u4ee5\u4e0b\u7248\u672c\uff1a<\/strong><\/p>\n DECLARE @columns NVARCHAR(MAX);<\/span> — \u83b7\u53d6\u9664\u6807\u8bc6\u5217\u5916\u7684\u6240\u6709\u5217\u540d<\/span> — \u6784\u5efa\u52a8\u6001 SQL \u8bed\u53e5<\/span> — \u6267\u884c\u52a8\u6001 SQL<\/span> SQL Server 2017\u53ca\u4ee5\u4e0a\u7248\u672c<\/strong><\/p>\n SQL Server 2017\u7248\u672c\u4ee5\u4e0a\u652f\u6301 STRING_AGG \u51fd\u6570\uff0c\u63d0\u4f9b\u4e86\u66f4\u9ad8\u6548\u7684\u65b9\u6cd5\uff1a<\/p>\n DECLARE @columns NVARCHAR(MAX);<\/span> — \u83b7\u53d6\u9664\u6807\u8bc6\u5217\u5916\u7684\u6240\u6709\u5217\u540d<\/span> — \u6784\u5efa\u52a8\u6001 SQL \u8bed\u53e5<\/span> — \u6267\u884c\u52a8\u6001 SQL<\/span> <\/p>\n","protected":false},"excerpt":{"rendered":" SQL\u8868\u5185\/\u8868\u95f4\u6570\u636e\u590d\u5236 \u901a\u5e38SQLServer\u5728\u65b0\u589e\u6570\u636e\u65f6\uff0c\u7528insert into \uff08f1,f2,f3&# […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[44],"tags":[],"class_list":["post-5770","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/itoau.com\/index.php?rest_route=\/wp\/v2\/posts\/5770","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itoau.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itoau.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/itoau.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/itoau.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5770"}],"version-history":[{"count":1,"href":"https:\/\/itoau.com\/index.php?rest_route=\/wp\/v2\/posts\/5770\/revisions"}],"predecessor-version":[{"id":5771,"href":"https:\/\/itoau.com\/index.php?rest_route=\/wp\/v2\/posts\/5770\/revisions\/5771"}],"wp:attachment":[{"href":"https:\/\/itoau.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5770"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itoau.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5770"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itoau.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5770"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nDECLARE @sql NVARCHAR(MAX);<\/span><\/p>\n
\nSELECT @columns = STUFF((<\/span>
\nSELECT ‘,’ + QUOTENAME(c.name)<\/span>
\nFROM sys.columns c<\/span>
\nJOIN sys.tables t ON c.object_id = t.object_id<\/span>
\nLEFT JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id<\/span>
\nWHERE t.name = ‘tablename’ AND ic.object_id IS NULL<\/span>
\nFOR XML PATH(”), TYPE<\/span>
\n).value(‘.’, ‘NVARCHAR(MAX)’), 1, 1, ”);<\/span><\/p>\n
\nSET @sql = N’<\/span>
\nINSERT INTO tablename (‘ + @columns + ‘)<\/span>
\nSELECT ‘ + @columns + ‘<\/span>
\nFROM tablename<\/span>
\nWHERE fieldname = ”0001”;’;<\/span><\/p>\n
\nEXEC sp_executesql @sql;<\/span><\/p>\n
\nDECLARE @sql NVARCHAR(MAX);<\/span><\/p>\n
\nSELECT @columns = STRING_AGG(QUOTENAME(c.name), ‘, ‘)<\/span>
\nFROM sys.columns c<\/span>
\nJOIN sys.tables t ON c.object_id = t.object_id<\/span>
\nLEFT JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id<\/span>
\nWHERE t.name = ‘tablename’ AND ic.object_id IS NULL;<\/span><\/p>\n
\nSET @sql = N’<\/span>
\nINSERT INTO tablename (‘ + @columns + ‘)<\/span>
\nSELECT ‘ + @columns + ‘<\/span>
\nFROM tablename<\/span>
\nWHERE fieldname = ”0001”;’;<\/span><\/p>\n
\nEXEC sp_executesql @sql;<\/span><\/p>\n