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
 General SQL Server Forums
 New to SQL Server Programming
 How to update and insert data in different DB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 12/20/2012 :  05:00:54  Show Profile  Reply with Quote
hello all,

i have two databases and have same table.Here i need to update and insert data from one database to another databse at one time.Already i have update and insert queries but how to both insert and update at one time??? suggest me

P.V.P.MOhan

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/20/2012 :  06:06:12  Show Profile  Reply with Quote
hi mohan,
You can use MERGE statement to do insert and update at a time.
See the following script:
Just replace Database names ( study, ADIN)

USE study
GO
CREATE TABLE testStudy (c1 int, name varchar(10))
insert into testStudy VALUES(1, 'chandu'), (2, 'sailu'), (3, 'manohar')
GO
USE ADIN
GO 
CREATE TABLE testAdin (c1 int, name varchar(10))
insert into testAdin VALUES(1, 'muni'),  (4, 'chandana')
GO
SELECT * FROM study..teststudy -- target table
SELECT * FROM ADIN..testAdin -- source table
GO
USE study
GO
MERGE INTO study.dbo.testStudy AS t1
USING ADIN.dbo.testAdin t2
ON t1.c1  = t2.c1
WHEN MATCHED THEN 
	UPDATE SET t1.name = t2.name
WHEN NOT MATCHED BY TARGET THEN
	INSERT(c1, name) 
	VALUES(t2.c1, t2.name);
GO
SELECT * FROM study..teststudy -- target table
GO
DROP TABLE ADIN..testAdin
DROP TABLE study..teststudy
GO 

Output:
c1	name
1	muni
2	sailu
3	manohar
4	chandana


Then apply this MERGE operation for INSERT as well as UPDATE

--
Chandu
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
343 Posts

Posted - 12/20/2012 :  06:07:08  Show Profile  Reply with Quote
what record do u want to update and to insert at one time ?
Why do you need to perform two different activities at one time?

if you want to understand Merge statement then check below link

http://www.codeproject.com/Articles/37172/Merge-Statement-in-SQL-Server-2008

Vijay is here to learn something from you guys.

Edited by - vijays3 on 12/20/2012 06:10:49
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 12/20/2012 :  06:49:40  Show Profile  Reply with Quote
chandu awesome u clarified my doubt.this doubt is there with me for years thanks man...kudos

P.V.P.MOhan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/20/2012 :  06:58:35  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

chandu awesome u clarified my doubt.this doubt is there with me for years thanks man...kudos

P.V.P.MOhan


Welcome........

--
Chandu
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.09 seconds. Powered By: Snitz Forums 2000