Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

insert problem Expand / Collapse
Author
Message
Posted Thursday, December 25, 2008 11:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 6:40 AM
Points: 45, Visits: 502
Hai all,

in insert the data

empid empcode ename
1 0 a
2 0 b
3 0 c
4 0 d
5 0 e

but i want insert the data

empid empcode ename
1 e00001 a
2 e00002 b
3 e00003 c
4 e00004 d
5 e00005 e
......
.......
10000 e10000 z

here empcode is the automatic insert the record.how it is possible
Post #625732
Posted Thursday, December 25, 2008 11:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, January 26, 2014 11:13 AM
Points: 2,242, Visits: 3,641
you can use trigger in this scenario. I'm assuming empid is not an identity column and u're manually inserting data in that column.

===================================
create table employees
(
empid int,
empcode varchar(10),
ename varchar(100)
)

create trigger trg_empCode
on Employees
after insert
AS
update employees set employees.empcode= 'e'+right('0000'+cast(inserted.empid as varchar),5) from inserted
INNER JOIN employees on employees.empid=inserted.empid

insert into employees(empid, ename)
select 1, 'pradeep'

insert into employees (empid, ename)
select 2,'polo'
union all
select 3, 'csit'
union all
select 4, 'singh'

select * from employees

OUTPUT
--------
1 e00001 pradeep
2 e00002 polo
3 e00003 csit
4 e00004 singh
=================================================
Is this what you want?






Pradeep Singh
Post #625737
Posted Friday, December 26, 2008 2:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
So you are trying to format the empno?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069




Madhivanan

Failing to plan is Planning to fail
Post #625780
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse