CODEWITHSUNDEEP
sqlsql-server
user3424829
user3424829

Reputation: 141

how to auto incremented a column of a table variable while inserting values in it

DECLARE @BusinessUnit AS Table( [id] varchar(10),sn int );
begin 

INSERT INTO @BusinessUnit([id])
SELECT name
FROM test 
WHERE course = 'Mca';
select * from @BusinessUnit;
end

how i assign auto increment value in sn field when the records are inserted in it. I Need the following type of output:

id 'Deepak' sn  1 and id 'Rohan'  sn  2  like that

Upvotes: 4

Views: 6358

Answers (2)

mohan111
mohan111

Reputation: 8865

Here is another way to do 

DECLARE @tblcustomers table  (id int, customername varchar(10));

insert into @tblcustomers values (1, 'aaa');
insert into @tblcustomers values (2, 'bbb');
insert into @tblcustomers values (3, 'ccc');
insert into @tblcustomers values (4, 'ddd');
insert into @tblcustomers values (5, 'eee');
insert into @tblcustomers values (6, 'fff');

Select * into #t from @tblcustomers
DECLARE @tblpurchase table  (Name VARCHAR(10), Sn int);
insert into @tblpurchase
SELECT Customername,ROW_NUMBER()over(ORDER BY ID  )As RN  FROM #t
select * from @tblpurchase

To get Identity column RowId

Upvotes: 0

Szymon
Szymon

Reputation: 43023

Modify your table variable to use IDENTITY property for sn column:

DECLARE @BusinessUnit AS Table( [id] varchar(10), sn int IDENTITY (1,1) );

The rest of your code should stay the same.

Upvotes: 10

Related Questions