CODEWITHSUNDEEP
sql-serversql-server-2008auto-increment
UnDiUdin
UnDiUdin

Reputation: 15374

how to do an insert statement using a non auto increment ID?

The same question has been asked for MySQL here, anyway that syntax doesn't work in SQL Server.

I paste the sample from that question (by simplifying it) here because it explains what I need very well.

DECLARE @t1 integer
SET @t1=0;
-- MyTable is a simple Table with 2 fields "MyID" and "MyValue"
insert into MyTable
SELECT  @t1 := @t1+1, --this "should" work in MySQL
  MyAnotherValue
FROM    AnotherTable

Is there a way to achieve the same in SQL Server (without using a cursor)?

Note: this is a to be run once query, it is a maintenance query, so race conditions and locks are not an issue.

Upvotes: 3

Views: 1745

Answers (2)

Jose Rui Santos
Jose Rui Santos

Reputation: 15319

This will work, even if you run it more than once:

insert into MyTable(MyId, MyValue)
  select (select isnull(max(MyId), 0) from MyTable) + -- avoid duplicated keys, if you repeat this insert again
         row_number() over(order by MyAnotherValue),
         MyAnotherValue
    from AnotherTable

The isnull() function covers the case when MyTable is empty

Upvotes: 4

codingbadger
codingbadger

Reputation: 43974

You can achieve this by using Row_Number()

Insert Into dbo.AnotherTable (Col1, Col2)
Select Row_Number() Over(Order By SomeColumn),
       SomeColumn

From dbo.YourTable

Upvotes: 3

Related Questions