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)
 How to update multiple filed of table 2DB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

krainov
Yak Posting Veteran

57 Posts

Posted - 03/15/2013 :  01:36:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 03/15/2013 :  20:28:23  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

krainov
Yak Posting Veteran

57 Posts

Posted - 03/18/2013 :  02:33:55  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 03/18/2013 :  03:02:58  Show Profile  Reply with Quote
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
Go to Top of Page

krainov
Yak Posting Veteran

57 Posts

Posted - 03/18/2013 :  03:10:08  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 03/18/2013 :  03:12:39  Show Profile  Reply with Quote
Can you post sample data and expected output?
We have to check whether title format or exact title?


--
Chandu
Go to Top of Page

krainov
Yak Posting Veteran

57 Posts

Posted - 03/18/2013 :  03:53:16  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 03/18/2013 :  04:15:08  Show Profile  Reply with Quote
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
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.08 seconds. Powered By: Snitz Forums 2000