te')); return $arr; } /* 遍历用户所有主题 * @param $uid 用户ID * @param int $page 页数 * @param int $pagesize 每页记录条数 * @param bool $desc 排序方式 TRUE降序 FALSE升序 * @param string $key 返回的数组用那一列的值作为 key * @param array $col 查询哪些列 */ function thread_tid_find_by_uid($uid, $page = 1, $pagesize = 1000, $desc = TRUE, $key = 'tid', $col = array()) { if (empty($uid)) return array(); $orderby = TRUE == $desc ? -1 : 1; $arr = thread_tid__find($cond = array('uid' => $uid), array('tid' => $orderby), $page, $pagesize, $key, $col); return $arr; } // 遍历栏目下tid 支持数组 $fid = array(1,2,3) function thread_tid_find_by_fid($fid, $page = 1, $pagesize = 1000, $desc = TRUE) { if (empty($fid)) return array(); $orderby = TRUE == $desc ? -1 : 1; $arr = thread_tid__find($cond = array('fid' => $fid), array('tid' => $orderby), $page, $pagesize, 'tid', array('tid', 'verify_date')); return $arr; } function thread_tid_delete($tid) { if (empty($tid)) return FALSE; $r = thread_tid__delete(array('tid' => $tid)); return $r; } function thread_tid_count() { $n = thread_tid__count(); return $n; } // 统计用户主题数 大数量下严谨使用非主键统计 function thread_uid_count($uid) { $n = thread_tid__count(array('uid' => $uid)); return $n; } // 统计栏目主题数 大数量下严谨使用非主键统计 function thread_fid_count($fid) { $n = thread_tid__count(array('fid' => $fid)); return $n; } ?>sql server - How to use an identity column for both automatic values and manual inserts? - Stack Overflow

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>sql server - How to use an identity column for both automatic values and manual inserts? - Stack Overflow

    sql server - How to use an identity column for both automatic values and manual inserts? - Stack Overflow

    programmeradmin4浏览0评论

    I have a table with an identity column that automatically increments the value by one when a new row is inserted. Every once in a while, I need to be able to manually enter the value for that identity column. I know this isn't typical practice; however, this database is a part of a new system that occasionally requires old data to be added to it where that old data must maintain its original Id. The program supplying the data confirms prior to the insert that the manually supplied id is not a duplicate. Besides that, the identity column is the table's primary key, so the database shouldn't accept a duplicate value.

    It seems that, in order to manually enter a value, I will need to use SET IDENTITY_INSERT [TableName] ON prior to the insert and SET IDENTITY_INSERT [TableName] OFF following the insert.

    Beyond that, I'm wondering if it is possible to write one INSERT statement that handles both an automatic or manual insert. Ideally, if it saw a NULL value for the id value, it'd automatically popular the id value; however, if there were an id value supplied, it would use that. Here is what I have so far:

    -- Create original table with existing sample data
CREATE TABLE #o 
(
    [Id] int identity(10, 1),
    [First Name] varchar(50),
    [Last Name] varchar(50)
)

INSERT INTO #o ([First Name], [Last Name])
    SELECT *
    FROM (
        VALUES ('Dwight', 'Schrute'), ('Jim', 'Halpert')) x(a, b)

SELECT *
FROM #o

-- Create a table to supply a row of data where the Id could be
-- NULL (requiring the use of the automatically supplied identity
-- value) or it could be a unique value to insert into the table
DECLARE @id int = 4

SELECT *
INTO #t
FROM (
    VALUES(@id, 'Michael', 'Scott')
    ) t([Id], [First Name], [Last Name])

SELECT *
FROM #t

-- If the value of the id is not NULL, change IDENTITY_INSERT to ON
IF (SELECT [Id] FROM #t) IS NOT NULL
    
    SET IDENTITY_INSERT #o ON

-- Insert the new data
INSERT INTO #o ([Id], [First Name], [Last Name])
    SELECT 
        #t.[Id], #t.[First Name], #t.[Last Name]
    FROM #t
    LEFT JOIN #o ON #o.[Id] = #t.[Id]
    WHERE #o.[Id] IS NULL

-- Turn off the IDENTITY_INSERT
SET IDENTITY_INSERT #o OFF

SELECT *
FROM #o
ORDER BY [Id]

DROP TABLE #t
DROP TABLE #o

    This works if a value is supplied for the Id; however, if NULL is used for the @Id variable to indicate that SQL should provide the Id, then I get the following error:

    Msg 544, Level 16, State 1, Line 37
    Cannot insert explicit value for identity column in table '#o' when IDENTITY_INSERT is set to OFF.

    I know I could rewrite the INSERT statement to look like this for when the Id is NULL:

    -- Insert the new data
INSERT INTO #o ([First Name], [Last Name])
    SELECT #t.[First Name], #t.[Last Name]
    FROM #t
    LEFT JOIN #o ON #o.[Id] = #t.[Id]
    WHERE #o.[Id] IS NULL

    But of course, this wouldn't work for when the Id is not NULL and, again, I'm wondering if I can do it with one INSERT statement.

    NOTE: I am using a LEFT JOIN and WHERE statement because this is a part of a larger stored procedure to UPDATE when the provided Id exists in the data table and INSERT when it does not.

    I have a table with an identity column that automatically increments the value by one when a new row is inserted. Every once in a while, I need to be able to manually enter the value for that identity column. I know this isn't typical practice; however, this database is a part of a new system that occasionally requires old data to be added to it where that old data must maintain its original Id. The program supplying the data confirms prior to the insert that the manually supplied id is not a duplicate. Besides that, the identity column is the table's primary key, so the database shouldn't accept a duplicate value.

    It seems that, in order to manually enter a value, I will need to use SET IDENTITY_INSERT [TableName] ON prior to the insert and SET IDENTITY_INSERT [TableName] OFF following the insert.

    Beyond that, I'm wondering if it is possible to write one INSERT statement that handles both an automatic or manual insert. Ideally, if it saw a NULL value for the id value, it'd automatically popular the id value; however, if there were an id value supplied, it would use that. Here is what I have so far:

    -- Create original table with existing sample data
CREATE TABLE #o 
(
    [Id] int identity(10, 1),
    [First Name] varchar(50),
    [Last Name] varchar(50)
)

INSERT INTO #o ([First Name], [Last Name])
    SELECT *
    FROM (
        VALUES ('Dwight', 'Schrute'), ('Jim', 'Halpert')) x(a, b)

SELECT *
FROM #o

-- Create a table to supply a row of data where the Id could be
-- NULL (requiring the use of the automatically supplied identity
-- value) or it could be a unique value to insert into the table
DECLARE @id int = 4

SELECT *
INTO #t
FROM (
    VALUES(@id, 'Michael', 'Scott')
    ) t([Id], [First Name], [Last Name])

SELECT *
FROM #t

-- If the value of the id is not NULL, change IDENTITY_INSERT to ON
IF (SELECT [Id] FROM #t) IS NOT NULL
    
    SET IDENTITY_INSERT #o ON

-- Insert the new data
INSERT INTO #o ([Id], [First Name], [Last Name])
    SELECT 
        #t.[Id], #t.[First Name], #t.[Last Name]
    FROM #t
    LEFT JOIN #o ON #o.[Id] = #t.[Id]
    WHERE #o.[Id] IS NULL

-- Turn off the IDENTITY_INSERT
SET IDENTITY_INSERT #o OFF

SELECT *
FROM #o
ORDER BY [Id]

DROP TABLE #t
DROP TABLE #o

    This works if a value is supplied for the Id; however, if NULL is used for the @Id variable to indicate that SQL should provide the Id, then I get the following error:

    Msg 544, Level 16, State 1, Line 37
    Cannot insert explicit value for identity column in table '#o' when IDENTITY_INSERT is set to OFF.

    I know I could rewrite the INSERT statement to look like this for when the Id is NULL:

    -- Insert the new data
INSERT INTO #o ([First Name], [Last Name])
    SELECT #t.[First Name], #t.[Last Name]
    FROM #t
    LEFT JOIN #o ON #o.[Id] = #t.[Id]
    WHERE #o.[Id] IS NULL

    But of course, this wouldn't work for when the Id is not NULL and, again, I'm wondering if I can do it with one INSERT statement.

    NOTE: I am using a LEFT JOIN and WHERE statement because this is a part of a larger stored procedure to UPDATE when the provided Id exists in the data table and INSERT when it does not.

    • sql-server
    • t-sql
    • identity-insert
    Share Improve this question edited Feb 17 at 18:37 Dale K 27.3k15 gold badges57 silver badges83 bronze badges asked Feb 17 at 15:00 LeahLeah 1475 bronze badges 4
    • You can probably hack something with instead of triggers but i wouldn't bother, especially since the identity on insert seems to be something more of a one-off – siggemannen Commented Feb 17 at 15:02
    • @siggemannen Thanks for the input. If it can't be done using one INSERT, would you write an IF statement to choose which INSERT statement to use depending on the value of the Id? – Leah Commented Feb 17 at 15:04
    • Here is a question that seems to be getting at a similar thing, but no answer was really provided that included the use of identity columns: stackoverflow/questions/67510344/… – Leah Commented Feb 17 at 15:05
    • Don't you already know somehow if the values are coming in from history or from something new? – siggemannen Commented Feb 17 at 15:10
    Add a comment  | 

    1 Answer 1

    Reset to default 1

    Honestly, I think altering the INSERT appropriately for if you want to use the IDENTITY or not is the "best™" solution. If you just want the IDENTITY omit the column from the INSERT INTO... clause and don't provide a value in the SELECT/VALUES clause. If you do want to provide a value, enable IDENTITY_INSERT and then do include the column in the INSERT INTO... clause and SELECT/VALUE clause.

    You could, however, use a SEQUENCE which could enable to you to not change the INSERT INTO... clause and, assuming you are using a parametrised statement, use the next value from the SEQUENCE if the value is NULL.

    If, however, you need multiple rows, and you're using a TVP, then you could pass NULL values for the ID and then UPDATE the NULL values in the logic before INSERTing the values. If this data is coming from a different table, however, you'd need to use an intermediary table (such as a temporary table) and UPDATE that. You can't use NEXT VALUE FOR for some rows and not for others; FOR NEXT VALUE isn't allowed in a CASE, ISNULL, and several other functions that would permit the logic, nor can it be used in a UNION (ALL) query.

    CREATE SEQUENCE dbo.YourTableIDSequence
    AS int
    START WITH 10
    INCREMENT BY 1;

GO
CREATE TABLE dbo.YourTable ([Id] int NOT NULL
                                CONSTRAINT DF_YourTableID
                                    DEFAULT NEXT VALUE FOR dbo.YourTableIDSequence
                                CONSTRAINT PK_YourTable PRIMARY KEY,
                            [First Name] varchar(50),
                            [Last Name] varchar(50));
GO
--Parameters from outside
DECLARE @ID int = NULL,
        @Firstname varchar(50) = 'Leah',
        @LastName varchar(50) = 'Unknown';

--Your actual batch starts here:
IF @ID IS NULL
    SET @ID = NEXT VALUE FOR dbo.YourTableIDSequence;

INSERT INTO dbo.YourTable (Id,
                          [First Name],
                          [Last Name])
VALUES(@ID, @Firstname, @LastName);
GO

--Parameters from outside
DECLARE @ID int = 12345,
        @Firstname varchar(50) = 'Thom',
        @LastName varchar(50) = 'A';

--Your actual batch starts here:
IF @ID IS NULL
    SET @ID = NEXT VALUE FOR dbo.YourTableIDSequence;

INSERT INTO dbo.YourTable (Id,
                          [First Name],
                          [Last Name])
VALUES(@ID, @Firstname, @LastName);
GO

CREATE TYPE dbo.YourTableType AS TABLE (ID int NULL,
                                        FirstName varchar(50) NOT NULL,
                                        LastName varchar(50) NOT NULL);
GO
--Parameters from outside
DECLARE @YT YourTableType;

INSERT INTO @YT (Id,
                 FirstName,
                 LastName)
VALUES(NULL,'Joe','Blogss'),
      (987654,'Jane','Smith');
--Your batch starts here

UPDATE @YT
SET ID = NEXT VALUE FOR dbo.YourTableIDSequence
WHERE ID IS NULL;

INSERT INTO dbo.YourTable (Id,
                           [First Name],
                           [Last Name])
SELECT ID,
       FirstName,
       LastName
FROM @YT;
GO

SELECT *
FROM dbo.YourTable;

GO

--Clean up
DROP TABLE dbo.YourTable;
DROP SEQUENCE dbo.YourTableIDSequence;
DROP TYPE dbo.YourTableType;

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论