如何在没有身份的情况下自动增加id？

I'm trying do to a bulk insert from another table in sql server. My query is currently like that :

我正在尝试从sql server中的另一个表进行批量插入。我的查询目前是这样的:

INSERT INTO Table1(Id, Value)
SELECT ??, Value
FROM Table2;

Now, my problem is obviously by what I replace ??. Id is an integer column without an identity property. I would like that for each inserted row, Id take the current max(Id) + 1.

现在,我的问题显然是由我取代的。 Id是没有标识属性的整数列。我希望每插入一行,Id取当前最大值(Id)+ 1。

Can I do that directly in my insert command?

我可以直接在insert命令中执行此操作吗?

4 个解决方案

#1

If you were using a newer version of SQL Server (2008+) you could try ROW_NUMBER():

如果您使用的是较新版本的SQL Server(2008+),可以尝试ROW_NUMBER():

DECLARE @BASE INT 
SET @BASE = (SELECT IsNull(MAX(ID),0) FROM Table1)

INSERT INTO Table1(Id, Value)
SELECT 
    @BASE + ROW_NUMBER() OVER (ORDER BY Value) ID, 
    Value
FROM Table2;

SQL Fiddle

Since you are using SQL Server 2000, you could try like bellow:

由于您使用的是SQL Server 2000,因此可以尝试下面的方法:

DECLARE @BASE INT 
SET @BASE = (SELECT IsNull(MAX(ID),0) FROM Table1)

INSERT INTO Table1(Id, Value)
SELECT 
   @BASE +  (SELECT COUNT(*) FROM Table2 AS i2 WHERE i2.Value <= a.Value),
   a.Value
FROM Table2 a

But it will only works if Value in Table2 is unique

但它只有在表2中的值是唯一的情况下才有效

SQL Fiddle

If Table2 has a primary key (field PK), then you could use:

如果Table2有主键(字段PK),那么您可以使用:

INSERT INTO Table1(Id, Value)
SELECT 
   @BASE +  (SELECT COUNT(*) FROM Table2 AS i2 WHERE i2.PK <= a.PK),
   a.Value
FROM Table2 a

#2

Here is one wicked way. We create a temp table with identity to generate new ids. This way we avoid the while loop.

这是一种邪恶的方式。我们创建一个带有标识的临时表来生成新的ID。这样我们就避免了while循环。

DECLARE @CurrentMaxID   INT,
        @DynamicQuery   NVARCHAR(MAX)

--TODO : Acquired table lock here on table1

SELECT @FirstNextID = ISNULL(MAX(Id), 0)
FROM Table1 --WITH(TABLOCK)



CREATE TABLE #TempTableWithID(  Table2Id        INT,
                                Table1FuturId   INT IDENTITY(1, 1))

INSERT INTO #TempTableWithID(Table2Id)
SELECT Id   --Here we use identity to generate table1 futur id
FROM Table2


INSERT INTO Table1(Id, value)
SELECT  Temp.Table1FuturId + @FirstNextID,
        Table2.Value
FROM Table2 
INNER JOIN #TempTableWithID AS Temp ON Table2.Id = Temp.Table2Id

--TODO : release table lock here on table1


DROP TABLE #TempTableWithID

#3

If I'm understanding you correctly, this should work.

如果我正确理解你,这应该有效。

CREATE TABLE #tbl1 (ID int, Value float)
CREATE TABLE #tbl2 (ID int, Value float)
INSERT INTO #tbl2 values (4, 2.0)
INSERT INTO #tbl2 values (8, 3.0)
INSERT INTO #tbl2 values (6, 4.0)
INSERT INTO #tbl1 values (1,1.0)
INSERT INTO #tbl1 values (3,3)
INSERT INTO #tbl1 values (9,3)

/*meat and potatoes start*/

INSERT INTO #tbl1(Id, Value)
SELECT (SELECT MAX(ID) FROM #tbl1) + ROW_NUMBER() OVER (ORDER BY Value) ID, Value
FROM #tbl2;

/*meat and potatoes end*/

Select * From #tbl1

drop table #tbl1
drop table #tbl2

#4

Why not IDENT_CURRENT() ?

为什么不用IDENT_CURRENT()?

SELECT IDENT_CURRENT('yourtablename')

It gives you the next ID reference. But this only works if the ID column has IDENTITY turned on.

它为您提供下一个ID参考。但这仅在ID列启用了IDENTITY时才有效。

OR you can try a SEQUENCE and the NEXT VALUE FOR.

或者您可以尝试SEQUENCE和NEXT VALUE FOR。

i.e.

CREATE TABLE Test.TestTable  
     (CounterColumn int PRIMARY KEY,  
    Name nvarchar(25) NOT NULL) ;   
GO  

INSERT Test.TestTable (CounterColumn,Name)  
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ;  
GO  

SELECT * FROM Test.TestTable;   
GO