SQL Server Insert Into Select

Question

I'm trying to INSERT a value in a column, the result of a SELECT this sentence works fine:

insert into tempdepa (Trabajadores) (select count (DepartmentName) from DimEmployee where DepartmentName = @d)

But I want to insert that SELECT into a specific column:

insert into tempdepa (Trabajadores) (select count (DepartmentName) from DimEmployee where DepartmentName = @d)

where id = @cont

This last where id = @cont is from the first table (tempdepa)... I tried

insert into tempdepa (Trabajadores where id = @cont) (select count (DepartmentName) from DimEmployee where DepartmentName = @d)

but it doesn't work. How can I do it?

You probably need to use an UPDATE instead of an INSERT if you want to change the value of an existing row! The INSERT cannot be "parametrized" with a WHERE clause - it just inserts a new row. — marc_s
– marc_s, Commented Aug 12, 2014 at 15:50

Iłya Bursov · Accepted Answer · 2014-08-12 15:42:34Z

5

If you need to insert new row, you need to do:

insert into tempdepa (Trabajadores, id) select count(DepartmentName), @cont from DimEmployee where DepartmentName = @d

if you want to update existing one:

update tempdepa set Trabajadores=(select count (DepartmentName) from DimEmployee where DepartmentName = @d) where id = @cont

answered Aug 12, 2014 at 15:42

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alastair Campbell Over a year ago

Lashane's got it. An INSERT is only used when you are creating a whole new row. You need to use an UPDATE if you are changing a field on an existing row.

MartinGarcia007 Over a year ago

WOW THANK YOU ALL! YOU WERE RIGHT @LASHANE I DIDNT REALIZED THAT I WAS INSERTIN NEW ROWS... BUT I NEEDED TO UPDATE :D THANKS!!!!!!!!!!!

Collectives™ on Stack Overflow

SQL Server Insert Into Select

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related