Insert - Update Offline DB - 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 2005 Forums

Transact-SQL (2005)

Insert - Update Offline DB

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-18 : 11:10:30

Hi Team,

I have a #MyCenter table as follows. A table with the similar structure is available in another database (offline database). A SQL Server job has to update the offline table every night. It has to insert new records and update the existing records. What is the best procedure for this?

I tried the following, but when the number of columns is high it become highly lenghty. Is there a better way?

CREATE TABLE #MyCenter(
[Center_ID] [int] IDENTITY(1,1) NOT NULL,
[Center_No] [int] NOT NULL,
[OwnerName] [varchar](100) NULL,
CONSTRAINT [PK_Center_Center_ID] PRIMARY KEY NONCLUSTERED ([Center_ID] ASC)
) ON [PRIMARY]

CREATE TABLE #MyCenterOffline(
[Center_ID] [int],
[Center_No] [int] NOT NULL,
[OwnerName] [varchar](100) NULL,
) ON [PRIMARY]

UPDATE #MyCenterOffline
SET Center_No = A.Center_No
FROM #MyCenterOffline O
INNER JOIN #MyCenter A
ON A.Center_ID = O.Center_ID

UPDATE #MyCenterOffline
SET OwnerName = A.OwnerName
FROM #MyCenterOffline O
INNER JOIN #MyCenter A
ON A.Center_ID = O.Center_ID

Thanks
Lijo Cheeran Joseph

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-18 : 11:27:38

What do you mean by "offline"? Is it just the name of the database or is the status of the database actually offline?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-18 : 11:35:17

Thanks for the quick resposne.

By 'Offline' I meant just another database.

Please help.

Thanks
Lijo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:14:39

you need to compare both the tables based on PK value ([Center_ID]) and do one of below

1. if record in yourtable not in offline, insert
2. if record in both tables and any of other fields are different, then do update

since the tables are in different db and in different server (i assume so) you need to set up linked server connection before you do above update/insert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-18 : 12:26:51

Hi Visakh,

Both the databases are on same server.

The main part of the question is - whether I need to write separate query for each column (please see sample below). That seems to be non-acceptable in my scenario. Any better approach available?

UPDATE #MyCenterOffline
SET Center_No = A.Center_No
FROM #MyCenterOffline O
INNER JOIN #MyCenter A
ON A.Center_ID = O.Center_ID

UPDATE #MyCenterOffline
SET OwnerName = A.OwnerName
FROM #MyCenterOffline O
INNER JOIN #MyCenter A
ON A.Center_ID = O.Center_ID

Thanks
Lijo

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-18 : 12:28:24

Yes you can combine them:

UPDATE #MyCenterOffline
SET Center_No = A.Center_No,
OwnerName = A.OwnerName
FROM #MyCenterOffline O
INNER JOIN #MyCenter A
ON A.Center_ID = O.Center_ID

Just keep adding them to the SET portion of the query, separate them with a comma.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:34:14

and for insert do


INSERT #MyCenterOffline (Center_No,OwnerName,.. )
SELECT A.Center_No,A.OwnerName,..
FROM #MyCenter A
LEFT JOIN #MyCenterOffline O
ON A.Center_ID = O.Center_ID
WHERE O.Center_ID IS  NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-18 : 12:56:23

Thank you Visakh and Tara.

End of the day, it was a simple affair. I was thinking in a limited cirlce. Hope I will improve one day