identity-insert FROM的语法没有要插入的列？

I have defined the following tables

我已经定义了以下表格

CREATE TABLE dbo.T_Comments_Paths
(
     path_id bigint IDENTITY(1,1) NOT NULL
    ,CONSTRAINT [PK_T_Comments_Paths] PRIMARY KEY(path_id)
);


CREATE TABLE dbo.T_Comments 
(
     COM_Id int IDENTITY(1,1) NOT NULL 
    ,COM_Text NATIONAL CHARACTER VARYING(255) NULL 
    ,CONSTRAINT [PK_T_Comments] PRIMARY KEY(COM_Id)
);

If I need to get a path-id for a comment, for a single-value, I can get it like this:

如果我需要获得注释的路径ID,对于单值,我可以这样得到它:

DECLARE @outputTable TABLE (path_id bigint); 
INSERT INTO T_Comments_Paths OUTPUT INSERTED.path_id INTO @outputTable DEFAULT VALUES; 
SET @__pathuid = (SELECT TOP 1 id FROM @outputTable);

However, I fail to find the syntax for getting the inserted ids (multiple) for an insert from another table.

但是,我找不到从另一个表获取插入的插入ID(多个)的语法。

e.g. I want to do this:

例如我想做这个:

DECLARE @outputTable TABLE (path_id bigint, com_id bigint); 
INSERT INTO T_Comments_Paths
OUTPUT INSERTED.path_id, com_id INTO @outputTable DEFAULT VALUES  
FROM T_Comments

this yields

"Incorrect syntax near FROM-keyword"

“FROM-keyword附近的语法不正确”

How can I do that (without cursor) ?
Note: I need to be compatible with MySQL, so I can't use newid(), because there's no uuid-type in MySQL, and I don't want to use varchar or varbinary either...

我怎么能这样做(没有光标)?注意:我需要与MySQL兼容,所以我不能使用newid(),因为MySQL中没有uuid类型,我也不想使用varchar或varbinary ...

1 个解决方案

#1

If I understand correctly, your problem boils down to this: Adding n new rows to a identity column and then bring those newly added values to update an empty column in a table with n rows (@outputTable) without worrying about matching.

如果我理解正确,你的问题归结为:向标识列添加n个新行,然后将这些新添加的值更新为具有n行(@outputTable)的表中的空列,而不必担心匹配。

Setting up for testing

设置测试

CREATE TABLE #T_Comments_Paths (
    path_id BIGINT IDENTITY(1,1) NOT NULL
    , CONSTRAINT [PK1] PRIMARY KEY (path_id)
);

CREATE TABLE #T_Comments (
    com_id BIGINT IDENTITY(1,1) NOT NULL
    , com_text NVARCHAR(20) NULL
    , CONSTRAINT [PK2] PRIMARY KEY (com_id)
);

INSERT INTO #T_comments (com_text)
VALUES
('com1')
, ('com2');

**SOLUTION 1 **

**解决方案1 *

If you are willing to add an extra column to the @outputTable (aka rowNo), you can get a shorter solution like this:

如果您愿意为@outputTable(又名rowNo)添加一个额外的列,您可以得到一个更短的解决方案,如下所示:

--Add a few values to make #T_Comment_Paths not empty, for testing purpose, making things not matching
INSERT INTO #T_Comments_Paths DEFAULT VALUES;
INSERT INTO #T_Comments_Paths DEFAULT VALUES;
INSERT INTO #T_Comments_Paths DEFAULT VALUES;

DECLARE @currentID BIGINT;
SELECT @currentID = IDENT_CURRENT('#T_Comments_Paths');
-- @currentID should be 3

DECLARE @outputTable TABLE (path_id bigint, com_id bigint, rowNo bigInt); 

INSERT INTO @outputTable (com_id, rowNo)
SELECT
    com_id
    , ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
    #T_comments;

MERGE #T_Comments_Paths tgt
USING @outputTable src
ON tgt.path_id = src.path_id
WHEN NOT MATCHED THEN INSERT DEFAULT VALUES;

MERGE @outputTable tgt
USING (
    SELECT
        path_id
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNo
    FROM
        #T_Comments_Paths
    WHERE
        path_id > @currentID
) src
ON tgt.RowNo = src.RowNo
WHEN MATCHED THEN UPDATE SET
    tgt.path_id = src.PATH_ID;

SELECT *
FROM
    @outputTable;

SELECT *
FROM
    #T_Comments_Paths

DROP TABLE #T_Comments;
DROP TABLE #T_Comments_Paths;

**SOLUTION 2 **

**解决方案2 **

If you insist on only have 2 columns in the @outputTable, then this is a solution (longer)

如果你坚持在@outputTable中只有2列,那么这是一个解决方案(更长)

--Add a few values to make #T_Comment_Paths not empty, for testing purpose
INSERT INTO #T_Comments_Paths DEFAULT VALUES;
INSERT INTO #T_Comments_Paths DEFAULT VALUES;
INSERT INTO #T_Comments_Paths DEFAULT VALUES;
DECLARE @currentID BIGINT;
SELECT @currentID = IDENT_CURRENT('#T_Comments_Paths');
-- @currentID should be 3

DECLARE @outputTable TABLE (path_id bigint, com_id bigint); 
DECLARE @outputMiddleTable TABLE (rowNo bigint, com_id bigint);


INSERT INTO @outputTable (com_id)
SELECT
    com_id
FROM
    #T_comments;

WITH cte AS (
    SELECT
        com_id
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNo
    FROM
        @outputTable
)
INSERT INTO @outputMiddleTable (rowNo,com_id)
SELECT RowNo, com_id
FROM cte;

MERGE #T_Comments_Paths tgt
USING @outputTable src
ON tgt.path_id = src.path_id
WHEN NOT MATCHED THEN INSERT DEFAULT VALUES;

WITH cte1 AS (
SELECT
    path_id
    , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNo
FROM
    #T_Comments_Paths
WHERE
    path_id > @currentID
), cte2 AS (
SELECT
    cte1.path_id
    , t1.com_id
FROM
    @outputMiddleTable t1
    JOIN cte1 ON t1.rowNo = cte1.RowNo
)
UPDATE ot
SET path_id = cte2.path_id
FROM @outputTable ot
    JOIN cte2 ON ot.com_id = cte2.com_id

SELECT *
FROM
    @outputTable;


DROP TABLE #T_Comments;
DROP TABLE #T_Comments_Paths;

#1