| Author |
 Topic  |
|
|
krainov
Yak Posting Veteran
57 Posts |
 Posted - 03/15/2013 : 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
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 03/15/2013 : 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 - 03/18/2013 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 03/18/2013 : 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 - 03/18/2013 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 03/18/2013 : 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 - 03/18/2013 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 03/18/2013 : 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 |
|
|
| |
Topic |
|
How to update multiple filed of table 2DB
