Reputation: 13
I have a table named employee
with the following columns:
empid int (autoincrement,PK),
empname varchar,
imagepath varchar
I want to insert empid
, empname
, and imagepath
where image name should be the value of auto-increment id like ~/images/1.jpg
(here 1 is auto-incremented id of that row)
this is perfect code or not ???
insert into employee (name, path)
values
(
'sarju',
(select '~/images/'+Cast(ISNULL(max(empid),0)+1 as varchar(50))+'.jpg' from emp)
)
Upvotes: 1
Views: 197
Reputation: 656
If you have only one specific folder where will be stored your images, you can just make your field imagepath
as computed
CREATE TABLE employee
(
empid int IDENTITY(1,1) NOT NULL,
empname VARCHAR(50) NOT NULL,
imagepath AS ('~/images/'+cast(empid AS VARCHAR(50))+'.jpg')
)
INSERT INTO employee (empname)
VALUES ('sarju')
INSERT INTO employee (empname)
VALUES ('sarju2')
Upvotes: 0
Reputation: 1287
Edit: While this work, I have to say that Nenad Zivkovics solution is better.
You could use SCOPE_IDENTITY() to get the latest created ID. That would also be safer so that you dont get the wrong ID.
insert into employee (name)
values ( 'sarju')
declare @employeeId as int = SCOPE_IDENTITY()
update employee set
path = '~/images/' + cast(@employeeId as varchar(50)) + '.jpg'
where empid = @employeeId
Upvotes: 0
Reputation: 67948
I may get dinged for this because a lot of people don't like triggers, but this is a perfect example of what they were built for (and I honestly want the world to know that):
CREATE TRIGGER update_imagepath ON dbo.employee FOR insert AS
UPDATE employee
SET imagepath = '~/images/' + CAST((SELECT empid FROM inserted) AS VARCHAR) + '.jpg'
WHERE empid = (SELECT empid FROM inserted)
they do serve a purpose, and things like this are exactly that. See, you just need to INSERT
the name of the employee and the TRIGGER
will run automatically. This is honestly the most straight forward way of handling this. The other benefit of this - if you bulk inserted a bunch of employees or ran insert statements from Management Studio, the trigger still runs! You don't need to dig up that crazy INSERT
statement!
Upvotes: 1
Reputation: 18559
It's not a perfect code because MAX(ID)+1 can be different from next Auto increment value. You can use IDENT_CURRENT function
INSERT INTO employee (name,path)
SELECT 'sarju','~/images/'+CAST(IDENT_CURRENT('employee') AS VARCHAR(50))+'.jpg'
Upvotes: 3
Reputation: 11
insert into employee (name,path) values('sarju',(select '~/images/'+Cast(ISNULL(max(empid),0)+1 as varchar(50))+'.jpg' from emp))
yes, this is possible
Upvotes: 1