One Database to Another - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

One Database to Another

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-05-01 : 23:13:45

I want to create a query to perform in a Procedure.
Database1 to Database2 both Tables are the same. I am not sure how to do it.
Table is simple for now Userid, FirstName, LastName. I would like if it exists to do an Update.

Would the Insert look something like this?

INSERT Database2.NEWTABLE (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM Database1.Oldtable

Thank you

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-05-01 : 23:59:36

OK got this to work, GOOGLE! but can I do an IF Exists, if it does then do a Update? LIKE SO

if Exists
(INSERT TSPeople (OwnerID, FirstName, LastName) Select OwnerID, FirstName, LastName From IFW.dbo.People)
ELSE
UPDATE (TSPeople (OwnerID, FirstName, LastName) VALUE Select OwnerID, FirstName, LastName From IFW.dbo.People))

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-05-02 : 00:51:39

Updated code, still no joy; This does nothing as it stands.

IF Exists(Select OwnerID, FirstName, LastName From IFW.dbo.People)
UPDATE TSPeople SET FirstName = 'FirstName'
ELSE
INSERT TSPeople (OwnerID, FirstName, LastName) Select OwnerID, FirstName, LastName From IFW.dbo.People

IF I Do This

IF Exists(Select OwnerID, FirstName, LastName From IFW.dbo.People)
BEGIN
UPDATE TSPeople SET FirstName = 'FirstName'
END
ELSE
Begin
INSERT TSPeople (OwnerID, FirstName, LastName) Select OwnerID, FirstName, LastName From IFW.dbo.People
END
It only updates.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-02 : 00:54:35

[code]
INSERT Database2.dbo.NEWTABLE (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM Database1.dbo.Oldtable
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-02 : 00:57:26

quote:
Originally posted by nhaas

Updated code, still no joy; This does nothing as it stands.

IF Exists(Select OwnerID, FirstName, LastName From IFW.dbo.People)
UPDATE TSPeople SET FirstName = 'FirstName'
ELSE
INSERT TSPeople (OwnerID, FirstName, LastName) Select OwnerID, FirstName, LastName From IFW.dbo.People

IF I Do This

IF Exists(Select OwnerID, FirstName, LastName From IFW.dbo.People)
BEGIN
UPDATE TSPeople SET FirstName = 'FirstName'
END
ELSE
Begin
INSERT TSPeople (OwnerID, FirstName, LastName) Select OwnerID, FirstName, LastName From IFW.dbo.People
END
It only updates.

-- INSERT where as not exists


INSERT TSPeople (OwnerID, FirstName, LastName) 
Select OwnerID, FirstName, LastName 
From   IFW.dbo.People p
where not exists (select * from TSPeople x where x.OwnerID = p.OwnerID)

-- UPDATE where as difference

UPDATE d
SET    FirstName = s.FirstName,
       LastName  = s.LastName
FROM   TSPeople d inner join IFW.dbo.People s
       ON d.OwnerID = p.OwnerID
WHERE  d.FirstName <> s.FirstName
or     d.LastName  <> s.LastName

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-05-03 : 00:21:09

I put the query in as like it shows and I get an error on line 6 on "where"

UPDATE where as difference

thank you for the help

SQL 2000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-03 : 07:32:11

quote:
Originally posted by nhaas

I put the query in as like it shows and I get an error on line 6 on "where"

UPDATE where as difference

thank you for the help

SQL 2000

That suppose to be a comment line on the following codes

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-05-11 : 13:01:19

khtan;

they work great! thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources