How to update multiple filed of table 2DB

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 2008 Forums

Transact-SQL (2008)

How to update multiple filed of table 2DB

Author

Topic

krainov
Yak Posting Veteran

57 Posts

Posted - 2013-03-15 : 01:36:38

Hi everybody! Please help me with the following task in web project.
I have an old DB (let's say OldDB) and all my old model data in it.
Couple of months ago I have created a new one (NewDB), and imported data from old one (the data model is completly different).
So the problem is that the current web project is indexed in google, bing and others and a a have something like 50000 links from other sites. And I want to write the http module which checks the query string and parses it. So if old string format is detected and old data model ID is passed, it retrives data by ID from old data base. And I need that old IDs in my new tables.
So I need to write a SQL procedure which will select all records from current news table like:

SELECT [Id]
,[NewsItemTypeId]
,[OldId]
,[Title]
,[Description]
,[Body]
,[Published]
,[AddedOnUtc]
,[StartOnUtc]
,[MetaKeywords]
,[MetaDescription]
,[MetaTitle]
,[PictureId]
FROM [NewSokolnikiDB].[dbo].[NewsItem]

Then I need something like

FOREACH Record in foregoing selection:
Select from OLDDB
WHERE Title = "selection.Title"
Update NewDB.Table
Set OldId = Id from OldDB

Like this ))) I'm not so good in SQL syntax.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-03-15 : 20:28:23

how many rows are we talking about here?


update tgt
   set tgt.OldId = src.id
 from OldDB.[dbo].[NewsItem] src
join NewDB.[dbo].[Table] tgt
on src.Title = tgt.Title

But you need to answer few questions: give detail of what you are trying to accomplish? post the

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

krainov
Yak Posting Veteran

57 Posts

Posted - 2013-03-18 : 02:33:55

quote:
Originally posted by yosiasz

how many rows are we talking about here?

update tgt
set tgt.OldId = src.id
from OldDB.[dbo].[NewsItem] src
join NewDB.[dbo].[Table] tgt
on src.Title = tgt.Title

But you need to answer few questions: give detail of what you are trying to accomplish? post the

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Ok!
Here are my two tables:
Old DB

and New DB

I need to set the New DB field "OldId" with value from Old DB field "MATERIAL_ID" WHERE their Titles are equal. And we are talking about > 5000 rows.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 03:02:58

UPDATE ni
SET oldId = Material_id
FROM [New DB].dbo.NewsItem ni
JOIN [Old DB].dbo.Material_Lang ml
ON ni.title = ml.title

--
Chandu

krainov
Yak Posting Veteran

57 Posts

Posted - 2013-03-18 : 03:10:08

quote:
Originally posted by bandi

UPDATE ni
SET oldId = Material_id
FROM [New DB].dbo.NewsItem ni
JOIN [Old DB].dbo.Material_Lang ml
ON ni.title = ml.title

--
Chandu

The method works as follows:

UPDATE ni
SET oldId = Material_id
FROM [NewSokolnikiDB].dbo.NewsItem ni
JOIN [admin_expo].dbo.KVC_MATERIAL_LANG ml
ON ni.title COLLATE SQL_Latin1_General_CP1251_CI_AS = ml.title

But I got (11 row(s) affected) instead of 5652

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 03:12:39

Can you post sample data and expected output?
We have to check whether title format or exact title?

--
Chandu

krainov
Yak Posting Veteran

57 Posts

Posted - 2013-03-18 : 03:53:16

Thanks yosiasz and bandi!
I tried to query the wrong table. My news stored at the Publications table in old DB. So everything is fine now.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 04:15:08

quote:
Originally posted by krainov

Thanks yosiasz and bandi!
I tried to query the wrong table. My news stored at the Publications table in old DB. So everything is fine now.

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources