Reputation: 141
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
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
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