CODEWITHSUNDEEP
c#asp.netsqlsql-server
sarju kabariya
sarju kabariya

Reputation: 13

how to insert imagepath in table

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

Answers (5)

Dis Shishkov
Dis Shishkov

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

user707727
user707727

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

Mike Perrenoud
Mike Perrenoud

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

Nenad Zivkovic
Nenad Zivkovic

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

Keyur Dobariya
Keyur Dobariya

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

Related Questions