Unique Value for One Insert

I have a table called Test and It has two columns called Name and Phone_Number and I am inserting data into the test table from another table called sample. I am doing something like this:

Insert into Test
select First_Name AS Name,
       Contact_Number AS Phone_Number
 from Sample

Now I want to add a third column to the Test table called ID. This ID has to be unique for each Insert.Example:

For Instance if that insert statement inserts 2 records into the test table I want the ID to be 1 and next time when I insert 3 more records then those should have ID as 2 and so on

I thought of creating an Identity but if u insert 3 r开发者_如何转开发ecords at 1 time it will give you 1,2 and 3 but I want unique value for each insert.

Can anyone help me on this?

From your question I can see that you do not want a PK, you want to call your INSERT statement several times and for each time you call it, you need an unique ID (ID for each insert batch).

Something like this: (Oracle)

insert into Test (Name, Phone_Number, ID)
  select First_Name, Contact_Number, (select nvl(max(ID), 0) + 1 from Test)
    from Sample;

And for SQL Server

insert into Test (Name, Phone_Number, ID)
  select First_Name, Contact_Number, (select isnull(max(ID), 0) + 1 from Test)
    from Sample

EDIT: isnull is the correct function

To get around concurrency issues (multi-user), and blocking/deadlock issues (a MAX() query can block or be incorrect if it doesn't), you need to create an IDENTITY elsewhere.

Here's an example - first create a dummy table with an identity column to provide the unique numbers.

create table Test_IDGen (id int identity, dummy int)

The TSQL batch to get the next number would be something like this

insert Test_IDGen(dummy) values (null)
delete Test_IDGen with (readpast)  -- cleanup, don't store data unnecessarily
select SCOPE_IDENTITY()

Obviously it is better to put it into a stored proc and return the value of the last select, or at least assign it to a variable.

e.g.

..
declare @batchid int
select @batchid = SCOPE_IDENTITY()

Insert into Test
select First_Name AS Name,
       Contact_Number AS Phone_Number,
       @batchid
 from Sample

create IDENTITY(1,1) column and use your insert statement or try this:

insert into Test
select First_Name as Name, Contact_Number as Phone_Number, NEWID() as calledId
from Sample

update: same id for all inserts

declare @newid as uniqueidentifier
set @newid = NEWID()

insert into Test
select First_Name as Name, Contact_Number as Phone_Number, @newid as calledId
from Sample

Try something like this:

Insert into Test
select First_Name AS Name,
   Contact_Number AS Phone_Number,
   insertID=(select lastID=max(insertID)+1 from Sample)
from Sample