July 30, 2012 at 11:35 pm
Suppose i have 2 table as Emp and Emp2.
select empid from Emp where deptid=2
The output of the above query is
empid
1
2
3
5
7
9
10
16
17
Now,Insert all the above records in Emp2 from Emp where deptid=2
which should look something like this
update Emp2 set empid= empid from Emp where deptid=2
So my question is : What would be the exact query for achieving the above
July 31, 2012 at 12:51 am
Assuming none of the rows you want to insert into emp2 are already in that table then:
insert into emp2(empid)
select empid
from emp
where deptid = 2
Should put you on the right lines.
Mike
July 31, 2012 at 1:29 am
I tried your query but it is not working for me. This query does not overwrite the existing value in emp2 but it adds additional records. I want something which would overwrite the records.
July 31, 2012 at 1:33 am
shahsn11 (7/31/2012)
I want that the record in another table i.e in our case emp2 should be overwritten. In my case there can be some records in emp2 which would already would be having some record for deptno:2
Overwritten with what?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2012 at 1:41 am
Maybe the OP wants to use something like the MERGE statement http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx
Dave
July 31, 2012 at 1:41 am
In your original question you did say the records had to be INSERTED in to the new table, which is what my statement did.
Can you post full DDL of both tables, and some sample data that shows exactly what you are trying to do.
Do you want to INSERT new rows into emp2, UPDATE existing rows, or a combination of the two?
Mike
July 31, 2012 at 1:49 am
Mike John (7/31/2012)
In your original question you did say the records had to be INSERTED in to the new table, which is what my statement did.Can you post full DDL of both tables, and some sample data that shows exactly what you are trying to do.
Do you want to INSERT new rows into emp2, UPDATE existing rows, or a combination of the two?
Mike
Precisely put. In the OP's favour, I have noticed that those who are new to SQL Server do not readily distinguish between the meanings of the words 'Insert' and 'Update'...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2012 at 2:37 am
Hi,
I might not be able to put my question in proper way so let me put the same question other way around.
-------- //some logic
-------- //some logic
where empid = (select empid from Emp where deptid=2)
Now in the above query the empid returns more than one empid. Hence the above syntax or way of implementation is wrong. I would like to know what would you do when you encounter a situation like this
July 31, 2012 at 2:40 am
I'm afraid that what you are asking for is not clear enough for me to offer a solution.
Please see the link in my signature for details of how to post DDL and sample data in such a way as to make this clear. If you take the time to do this, a working & tested solution will be usually be provided within a matter of minutes.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2012 at 2:52 am
you should post proper ddl statement,sample data and expected results,so that it will be easy to unserstand and give u some solution
July 31, 2012 at 2:58 am
dilipd006 (7/31/2012)
you should post proper ddl statement,sample data and expected results,so that it will be easy to unserstand and give u some solution
Are you trying to boost your points by rewriting my posts? 😀
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2012 at 5:03 am
shahsn11 (7/31/2012)
Hi,I might not be able to put my question in proper way so let me put the same question other way around.
-------- //some logic
-------- //some logic
where empid = (select empid from Emp where deptid=2)
Now in the above query the empid returns more than one empid. Hence the above syntax or way of implementation is wrong. I would like to know what would you do when you encounter a situation like this
First you should use "in" not "=" if you have multiple records returned
where empid in (select empid from Emp where deptid=2)
Second: post the exact DDL , and as far as i have understood ..you may be looking something like MERGE as Dave mentioned...check this url..
http://technet.microsoft.com/en-us/library/bb510625.aspx
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
July 31, 2012 at 5:22 am
I would again try to make to understand what i am trying to achieve.Since i am a newbie , the first thing they teach to me was C.
I was trying to implement the same logic as that of C over here. First i would present a very simple C logic followed by what i want.
for(int i=0;i<=5;i++)
{
-----------
-----------//some logic
}
In the above code i am able to perform the logic for each value of i.
In SQL Server over here 'i' is nothing but 'empid'. i want to perform a set of logical step(Update) for every value empid in employee table.
July 31, 2012 at 5:30 am
SQL is very unlike c. It is fundamentally a set based language, so very rare that you need loops and so on.
I think you need a merge statement to solve this, and if you can post the DDL of both tables one of us will almost certainly give you the full statement you need.
It is hard for anyine to help when they do not know exactky what the tables look like.
Mike
August 1, 2012 at 12:51 pm
it also appears like you are missing a primary key, thus allowing multiple rows with the same empid to be inserted.
As suggested, ddl, data, and expected results would be helpful.
Leonard
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply