{"id":2441,"date":"2023-11-10T10:49:42","date_gmt":"2023-11-10T01:49:42","guid":{"rendered":"https:\/\/www.dbsheetclient.jp\/blog\/?p=2441"},"modified":"2023-11-10T11:01:44","modified_gmt":"2023-11-10T02:01:44","slug":"output%e5%8f%a5%ef%bc%88sql-server%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.dbsheetclient.jp\/blog\/?p=2441","title":{"rendered":"OUTPUT\u53e5\uff08SQL Server\uff09"},"content":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002<\/p>\n

\"\u571f\u6d66\u82b1\u706b\"<\/a><\/p>\n

\u4e0a\u306e\u5199\u771f\u306f\u300111\/4\u306b\u65e5\u672c\u4e09\u5927\u82b1\u706b\u5927\u4f1a\u306e\u4e00\u3064\u3068\u3044\u308f\u308c\u308b\u571f\u6d66\u306e\u82b1\u706b\uff08\u6b63\u5f0f\u540d\u306f\u571f\u6d66\u5168\u56fd\u82b1\u706b\u7af6\u6280\u5927\u4f1a\uff09\u306b\u5bb6\u65cf\u3067\u898b\u306b\u884c\u3063\u305f\u6642\u306e\u5199\u771f\u306b\u306a\u308a\u307e\u3059\u3002\u5bb6\u65cf\u3067\u8eca\u3067\u884c\u3063\u305f\u306e\u306f\u521d\u3081\u3066\u3067\u3001\u3042\u308b\u7a0b\u5ea6\u4f59\u88d5\u3092\u3082\u3063\u3066\u5bb6\u3092\u51fa\u767a\u3057\u305f\u3064\u3082\u308a\u3067\u3057\u305f\u304c\u3001\u571f\u6d66\u5e02\u5185\u306b\u5165\u308b\u3068\u3001\u3081\u3061\u3083\u304f\u3061\u3083\u8eca\u304c\u6df7\u3093\u3067\u3044\u3066\u3001\u6b62\u3081\u3089\u308c\u308b\u304b\u306a\u3068\u601d\u3063\u3066\u3044\u305f\u30e2\u30fc\u30eb\u306e\u307b\u3046\u3082\u3001\u5f53\u65e5\u306f\u99d0\u8eca\u7981\u6b62\u3068\u3044\u3046\u3053\u3068\u306b\u306a\u3063\u3066\u3044\u305f\u308a\u3001\u901a\u884c\u6b62\u3081\u304c\u3042\u3063\u305f\u308a\u3057\u3066\u3001\u5168\u304f\u8eca\u304c\u9032\u307e\u306a\u3044\u72b6\u6cc1\u306b\u3002\u305d\u308c\u3067\u3082\u4f55\u3068\u304b\u99d0\u8eca\u5834\u3092\u898b\u3064\u3051\u3088\u3046\u3068\u3001\u5e02\u5185\u3092\u3046\u308d\u3046\u308d\u3057\u3066\u3044\u305f\u3068\u3053\u308d\u3001\u3042\u308b\u304a\u3058\u3055\u3093\u304c\u58f0\u3092\u304b\u3051\u3066\u304f\u308c\u3066\u3001\u81ea\u5b85\u306e\u99d0\u8eca\u5834\u306e\u7a7a\u3044\u3066\u3044\u308b\u30b9\u30da\u30fc\u30b9\u3092\u8cb8\u3057\u3066\u304f\u308c\u308b\u3068\u3044\u3046\u5947\u8de1\u306e\u3088\u3046\u306a\u51fa\u6765\u4e8b\u304c\u3042\u308a\u307e\u3057\u305f\u3002\u59bb\u3068\u5a18\u3082\u8eca\u306b\u4e57\u3063\u3066\u3044\u305f\u3053\u3068\u3082\u3042\u308a\u3001\u5fc3\u914d\u3057\u3066\u58f0\u3092\u304b\u3051\u3066\u304f\u3060\u3055\u3063\u305f\u306e\u3060\u3068\u601d\u3044\u307e\u3059\u304c\u3001\u672c\u5f53\u306b\u611f\u8b1d\u3067\u3057\u305f\u3002

\uff1c\u672c\u65e5\u306e\u984c\u6750\uff1e
<\/strong>OUTPUT\u53e5<\/strong>\uff08SQL Server<\/strong>\uff09<\/p>\n

<\/strong>\u4eca\u56de\u306f\u3001SQL Server<\/span>\u306e\u6a5f\u80fd\u3067\u3001OUTPUT<\/span>\u53e5\u306b\u3064\u3044\u3066\u53d6\u308a\u4e0a\u3052\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002\u3053\u308c\u306f\u3001INSERT\/UPDATE\/DELETE\/MERGE<\/span>\u3067\u8ffd\u52a0\/<\/span>\u66f4\u65b0\/<\/span>\u524a\u9664 \u3055\u308c\u305f\u884c\u306e\u60c5\u5831\u3092\u53d6\u5f97\u30fb\u51fa\u529b\u3067\u304d\u308b\u3068\u3044\u3046\u3082\u306e\u3067\u3001\u3068\u3066\u3082\u4fbf\u5229\u306a\u6a5f\u80fd\u306b\u306a\u308a\u307e\u3059\u3002\u6700\u8fd1\u306e\u958b\u767a\u306e\u6848\u4ef6\u306e\u4e2d\u3067\u3001\u3053\u306e\u51e6\u7406\u3092\u4f7f\u3046\u3053\u3068\u3067\u3001\u958b\u767a\u306e\u8981\u4ef6\u3092\u6e80\u305f\u3059\u3053\u3068\u304c\u3067\u304d\u307e\u3057\u305f\u3002<\/p>\n

\u4f8b)
\u30c6\u30fc\u30d6\u30eb\u306b\u30c7\u30fc\u30bf\u3092\u767b\u9332\/\u66f4\u65b0\/\u524a\u9664\u3059\u308b\u3068\u304d\u306b\u3001\u901a\u5e38\u306f\u767b\u9332\/\u66f4\u65b0\/\u524a\u9664\u3057\u305f\u30c7\u30fc\u30bf\u3092\u305d\u306e\u6642\u70b9\u3067\u306f\u78ba\u8a8d\u3067\u304d\u305a\u3001\u7d50\u679c\u306e\u30c7\u30fc\u30bf\u3068\u51e6\u7406\u524d\u306e\u30c7\u30fc\u30bf\u3092\u6bd4\u8f03\u3059\u308b\u3053\u3068\u3067\u78ba\u8a8d\u3067\u304d\u308b\u3068\u3044\u3046\u304b\u305f\u3061\u306b\u306a\u308b\u304b\u3068\u601d\u3044\u307e\u3059\u304c\u3001OUTPUT\u53e5\u3092\u4f7f\u3046\u3053\u3068\u3067\u3001\u305d\u306e\u6642\u70b9\u3067\u305d\u308c\u3089\u306e\u30c7\u30fc\u30bf\u3092\u51fa\u529b\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u308b\u306e\u3067\u3001\u305d\u308c\u3092\u78ba\u8a8d\u3057\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

\u307e\u305a\u3001\u4eca\u56de\u78ba\u8a8d\u3059\u308b\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u3001\u521d\u671f\u306e\u30c7\u30fc\u30bf\u3092\u767b\u9332\u3057\u307e\u3059\u3002<\/p>\n

CREATE TABLE \u30c8\u30e9\u30f3A(
NO\u00a0\u00a0\u00a0 INT NOT NULL,
\u540d\u79f0\u00a0 NVARCHAR(20),
\u6570\u91cf\u00a0 INT,
CONSTRAINT PK_\u30c8\u30e9\u30f3A PRIMARY KEY(NO));<\/p>\n

INSERT INTO \u30c8\u30e9\u30f3A VALUES(1, ‘\u540d\u79f0\uff11’, 100);
INSERT INTO \u30c8\u30e9\u30f3A VALUES(2, ‘\u540d\u79f0\uff12’, 200);<\/p>\n

\"\u753b\u50cf1\"<\/a><\/p>\n

\u305d\u308c\u3067\u306f\u3001INSERT\/UPDATE\/DELETE \u306e\u51e6\u7406\u3092\u5b9f\u884c\u3059\u308b\u3068\u3068\u3082\u306b\u3001OUTPUT\u53e5\u306b\u3088\u3063\u3066\u51fa\u529b\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

\u307e\u305a\u3001INSERT \u304b\u3089
INSERT INTO \u30c8\u30e9\u30f3A OUTPUT inserted.*
VALUES(3, ‘\u540d\u79f0\uff13’, 300);<\/p>\n

\"\u753b\u50cf2\"<\/p>\n

<\/a>\u901a\u5e38\u3001INSERT\u6587\u3092\u5b9f\u884c\u3059\u308b\u3068\u3001
(1 \u884c\u51e6\u7406\u3055\u308c\u307e\u3057\u305f)
\u3068\u3044\u3046\u3088\u3046\u306a\u30e1\u30c3\u30bb\u30fc\u30b8\u306e\u307f\u304c\u51fa\u529b\u3055\u308c\u307e\u3059\u304c\u3001OUTPUT\u53e5\u3092\u4f7f\u7528\u3059\u308b\u3053\u3068\u3067\u3001INSERT\u3057\u305f\u30c7\u30fc\u30bf\u3092\u51fa\u529b\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n

\u6b21\u306b\u3001UPDATE\u3092\u8a66\u3057\u307e\u3059\u3002
UPDATE \u30c8\u30e9\u30f3A SET
\u6570\u91cf = 400
OUTPUT deleted.*, inserted.*
WHERE NO = 3;

<\/a>\"\u753b\u50cf3\"<\/p>\n

<\/a>\u4e0a\u8a18\u7d50\u679c\u306e\u3088\u3046\u306b\u3001deleted.* \u3067\u66f4\u65b0\u524d\u306e(\u524a\u9664\u3055\u308c\u308b)\u30c7\u30fc\u30bf\u3001inserted.* \u3067\u66f4\u65b0\u5f8c\u306e(\u8ffd\u52a0\u3055\u308c\u308b)\u30c7\u30fc\u30bf\u3092\u51fa\u529b\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n

\u6b21\u306b\u3001DELETE\u3092\u8a66\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

DELETE FROM \u30c8\u30e9\u30f3A
OUTPUT deleted.*
WHERE NO = 3;
<\/a>\"\u753b\u50cf4\"<\/a>
<\/a><\/p>\n

\u4e0a\u8a18\u306e\u3088\u3046\u306b\u3001DELETE\u3055\u308c\u308b\u30c7\u30fc\u30bf\u3092\u51fa\u529b\u3057\u3066\u3044\u308b\u306e\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002<\/p>\n

\u6b21\u306b\u3001MERGE\u51e6\u7406\u3067OUTPUT\u53e5\u3092\u4f7f\u7528\u3059\u308b\u305f\u3081\u306b\u3001\u30c8\u30e9\u30f3A\u3068\u540c\u69d8\u306e\u5b9a\u7fa9\u3092\u6301\u3064\u30ef\u30fc\u30af\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u3001\u305d\u3053\u306b\u3082\u30c7\u30fc\u30bf\u3092\u767b\u9332\u3057\u307e\u3059\u3002<\/p>\n

CREATE TABLE \u30ef\u30fc\u30afA(
NO\u00a0\u00a0 INT NOT NULL,
\u540d\u79f0 NVARCHAR(20),
\u6570\u91cf INT,
CONSTRAINT PK_\u30ef\u30fc\u30afA PRIMARY KEY(NO));<\/p>\n

INSERT INTO \u30ef\u30fc\u30afA VALUES(1 ‘\u4fee\u6b63\u540d\u79f0\uff11’, 1000);
INSERT INTO \u30ef\u30fc\u30afA VALUES(3 ‘\u540d\u79f0\uff13’, 3000);
INSERT INTO \u30ef\u30fc\u30afA VALUES(4 ‘\u540d\u79f0\uff14’, 4000);<\/p>\n

\"\u753b\u50cf5\"<\/a><\/p>\n

<\/a>\u305d\u308c\u3067\u306f\u3001MERGE\u3092\u5b9f\u884c\u3059\u308b\u524d\u306b\u3001\u518d\u5ea6\u30c8\u30e9\u30f3A\u30c6\u30fc\u30d6\u30eb\u306e\u30c7\u30fc\u30bf\u3092\u78ba\u8a8d\u3057\u307e\u3059\u3002<\/p>\n

SELECT * FROM \u30c8\u30e9\u30f3A;
<\/a>\"\u753b\u50cf6\"<\/a><\/p>\n

<\/a>\u30c8\u30e9\u30f3A\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3057\u3066\u3001\u30ef\u30fc\u30afA\u306e\u30c7\u30fc\u30bf\u3092\u53c2\u7167\u3057\u3066\u3001\u30ad\u30fc\u304c\u30de\u30c3\u30c1\u3059\u308b\u3082\u306e\u306f\u66f4\u65b0\u3057\u3001\u30de\u30c3\u30c1\u3057\u306a\u3044\u3082\u306e\u3067\u3001\u30c8\u30e9\u30f3\u306b\u306f\u306a\u304f\u30ef\u30fc\u30af\u306b\u5b58\u5728\u3059\u308b\u3082\u306e\u306f\u767b\u9332\u3057\u3001\u30c8\u30e9\u30f3\u306b\u306f\u3042\u308b\u304c\u30ef\u30fc\u30af\u306b\u306f\u5b58\u5728\u3057\u306a\u3044\u3082\u306e\u306f\u524a\u9664\u3059\u308bMERGE\u51e6\u7406\u3092\u5b9f\u884c\u3057\u3066\u307f\u307e\u3059\u304c\u3001\u3053\u306e\u3068\u304d\u306b\u3001OUTPUT\u53e5\u306b\u3088\u3063\u3066\u3001\u66f4\u65b0\u30fb\u767b\u9332\u30fb\u524a\u9664\u306e\u30c7\u30fc\u30bf\u3092\u51fa\u529b\u3057\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

MERGE INTO \u30c8\u30e9\u30f3A
USING \u30ef\u30fc\u30afA
ON (\u30c8\u30e9\u30f3A.NO = \u30ef\u30fc\u30afA.NO)
WHEN MATCHED THEN
UPDATE SET
\u00a0 \u540d\u79f0 = \u30ef\u30fc\u30afA.\u540d\u79f0
\u00a0,\u6570\u91cf = \u30ef\u30fc\u30afA.\u6570\u91cf
WHEN NOT MATCHED THEN
INSERT (NO, \u540d\u79f0, \u6570\u91cf)
VALUES(\u30ef\u30fc\u30afA.NO, \u30ef\u30fc\u30afA.\u540d\u79f0, \u30ef\u30fc\u30afA.\u6570\u91cf)
WHEN NOT MATCHED BY SOURCE
DELETE
OUTPUT inserted.*,\u00a0 deleted.*, $action;<\/p>\n

<\/a>\"\u753b\u50cf7\"<\/a><\/p>\n

<\/a>$action \u3068\u3044\u3046\u9805\u76ee\u306b\u3001UPDATE\/INSERT\/DELETE \u306e\u3069\u306e\u51e6\u7406\u304c\u884c\u308f\u308c\u305f\u306e\u304b\u304c\u51fa\u529b\u3055\u308c\u307e\u3059\u3002
\u3057\u305f\u304c\u3063\u3066\u3001\u3053\u306eOUTPUT\u306e\u7d50\u679c\u3092\u4e00\u6642\u30c6\u30fc\u30d6\u30eb\u306a\u3069\u306b\u767b\u9332\u3059\u308c\u3070\u3001MERGE\u6587\u306a\u3069\u306e\u51e6\u7406\u306e\u5f8c\u306b\u3001\u767b\u9332\u30fb\u524a\u9664\u30fb\u66f4\u65b0\u3055\u308c\u305f\u30c7\u30fc\u30bf\u3092\u305d\u306e\u5f8c\u306e\u51e6\u7406\u3067\u6d3b\u7528\u3059\u308b\u3053\u3068\u304c\u53ef\u80fd\u306b\u306a\u308a\u307e\u3059\u3002
\u6700\u7d42\u7684\u306a\u30c8\u30e9\u30f3A\u306e\u7d50\u679c\u3092\u78ba\u8a8d\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

SELECT * FROM \u30c8\u30e9\u30f3A;<\/p>\n

<\/a>\"\u753b\u50cf8\"
<\/a><\/p>\n

<\/a>\u4eca\u65e5\u306f\u4ee5\u4e0a\u307e\u3067<\/p>\n

<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002 \u4e0a\u306e\u5199\u771f\u306f\u300111\/4\u306b\u65e5\u672c\u4e09\u5927\u82b1\u706b\u5927\u4f1a\u306e\u4e00\u3064\u3068\u3044\u308f\u308c\u308b\u571f\u6d66\u306e\u82b1\u706b\uff08\u6b63\u5f0f\u540d\u306f\u571f\u6d66\u5168\u56fd\u82b1\u706b\u7af6\u6280\u5927\u4f1a\uff09\u306b\u5bb6\u65cf\u3067\u898b\u306b\u884c\u3063\u305f\u6642\u306e\u5199\u771f\u306b\u306a\u308a\u307e\u3059\u3002\u5bb6\u65cf\u3067\u8eca\u3067\u884c\u3063\u305f\u306e\u306f\u521d\u3081\u3066\u3067\u3001\u3042\u308b\u7a0b\u5ea6\u4f59\u88d5\u3092\u3082\u3063\u3066\u5bb6 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[2],"tags":[],"class_list":["post-2441","post","type-post","status-publish","format-standard","hentry","category-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2441","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2441"}],"version-history":[{"count":7,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2441\/revisions"}],"predecessor-version":[{"id":2476,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2441\/revisions\/2476"}],"wp:attachment":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}