SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 T-SQL Query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Shadab Shah
Starting Member

India
23 Posts
Posted - 07/31/2012 :  01:03:43  Show Profile  Reply with Quote
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

lionofdezert
Aged Yak Warrior

Pakistan
864 Posts
Posted - 07/31/2012 :  01:25:05  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
INSERT INTO emp2 (empid)
select empid from Emp where deptid=2


--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

Shadab Shah
Starting Member

India
23 Posts
Posted - 07/31/2012 :  01:37:49  Show Profile  Reply with Quote
Insert would not work in my case. Because there may be possibility that data may be present in Emp2 table.Hence i would like to carry on with update.Any new answer please.
Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
864 Posts
Posted - 07/31/2012 :  01:47:25  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
INSERT INTO emp2 (empid)
select empid from Emp
where deptid=2
AND NOT EXISTS (SELECT 1 FROM emp2 WHERE empid = emp.empid)

How can you update a record which never exists on emp2?

--------------------------
http://connectsql.blogspot.com/
Edited by - lionofdezert on 07/31/2012 01:50:13
Go to Top of Page

Shadab Shah
Starting Member

India
23 Posts
Posted - 07/31/2012 :  03:14:36  Show Profile  Reply with Quote
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.
Edited by - Shadab Shah on 07/31/2012 03:32:23
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/31/2012 :  03:55:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
MERGE	dbo.Emp2 AS tgt
USING	dbo.Emp AS src ON src.EmpID = tgt.EmpID
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					EmpID
				)
			VALUES	(
					src.EmpID
				)
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.Col1 = src.Col1;


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000