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
 General SQL Server Forums
 New to SQL Server Programming
 Pulling my hair out...PLEASE help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asiddle
Starting Member

35 Posts

Posted - 08/01/2006 :  12:30:31  Show Profile  Reply with Quote
Ok so every week I have to download a file containing in excess of 40000 products. I then have to change the category ids to suit my system. Is there a script or something that I could create so I just run it each week to do a search and replace and then loop until all category ids have been changed.

Table = ecommstore
Column = section

I would need to change say from 84 to 1 then 86 to 2, 87 to 3 etc etc

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 08/01/2006 :  12:51:50  Show Profile  Reply with Quote
Does ur change is same or following a pattern all the time ?

or if u r worried about hair
http://www.hairlosstalk.com/discussions

Srinika

Edited by - Srinika on 08/01/2006 12:53:59
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/01/2006 :  12:56:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Just create a table that maps the imported values to the ones you need.

Then import the raw data into a table, and join to your mapping table to retrieve the corresponding ID.

I suspect that you may need to learn the basics of SQL to implement this, since it's a pretty core concept of relational databases; I strongly urge you to do some reading about SQL to get familiar with JOINS and SELECT statements and the like.

- Jeff

Edited by - jsmith8858 on 08/01/2006 13:02:01
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/01/2006 :  13:00:49  Show Profile  Reply with Quote

How about:
Instead of "changing" the values, how about adding a new column. Also maybe a new map table (ie product_category_map) Use your internal value to associate with the rest of your model but leave the exteranal value as an attribute of the product table. Your new values will be the one that has <null> for the interal id and you can update those values with a single update statement (joining to your map table).

Be One with the Optimizer
TG

Edited by - TG on 08/01/2006 13:01:16
Go to Top of Page

asiddle
Starting Member

35 Posts

Posted - 08/01/2006 :  13:06:10  Show Profile  Reply with Quote
Guys whilst I really really appreciate all your help I need this in basic terms. I rarely touch sql and have had to learn 6 - 7 new things in the last month to get to where I am know. I am sure to you guys this is very simple, but to me this has now become a show stopper. I am not short of sitting in a corner dribbling from the amount I have had to take in. My brain is now on overload with a continous migrain. Please just treat me like an idiot and explain.

Thanks to all
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/01/2006 :  13:30:42  Show Profile  Reply with Quote
>>Please just treat me like an idiot and explain.

If you've gotten to this point I doubt you're an idiot but you may be over your head. Read this post and try to define a specific (simple) problem you want solved. Then post what you you've got so far and enough detail for what you need so we can help. Hang in there...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG
Go to Top of Page

asiddle
Starting Member

35 Posts

Posted - 08/01/2006 :  13:47:13  Show Profile  Reply with Quote
I have 2 databases of which one has a column called pSection and the other a column called category. Now I need to convert the numbers in category to numbers recognised by the program in psection. eg.

Category psection
1 10
17 11
18 12

At the moment, each week when the new product list is published I manually do a find and replace to change the category to a psection id. I am hoping that I could create a script to execute each week to automatically change each one for me. At the moment I have nothing as I cant seem to find anything on the web (or I am not looking for the right thing) which will explain how to and why hence why I am now here. I guess I need that little bit of insight to get me started.
Go to Top of Page

asiddle
Starting Member

35 Posts

Posted - 08/01/2006 :  13:49:37  Show Profile  Reply with Quote
Oh what I forgot to say, whilst it may be obvious, the only db that is used contains the pSection Id. The db with category is just something my supplier supplies with all the latest changes in product etc, but obviously I need to update from it, so the conversion of cat to psec is very important, ensuring all product ire in the right section in the ecomm site
Go to Top of Page

asiddle
Starting Member

35 Posts

Posted - 08/01/2006 :  13:55:24  Show Profile  Reply with Quote
I did try this

DECLARE @find int(4),
@replace int(4),
@patfind int(4)

SELECT @find = '1',
@replace = '10'

SELECT @patfind = '%' + @find + '%'

UPDATE store
SET pSection = STUFF( pSection,
PATINDEX( @patfind, pSection ),
DATALENGTH( @find ),
@replace )
WHERE pSection LIKE @patfind

But unfortunately it just kicked up loads of error. Add to that I have no idea how to get it to loop so I could enter all the cat to psec id changes
Go to Top of Page

asiddle
Starting Member

35 Posts

Posted - 08/01/2006 :  14:15:59  Show Profile  Reply with Quote
Ok so I have found this works

UPDATE products
SET pSection = '72'
WHERE pSection = '1'

Not the best way I am sure but once created I just need to run it each week
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/01/2006 :  14:37:16  Show Profile  Reply with Quote
Ok, you didn't follow the link instructions too well but see if this gives you any ideas:

set nocount on

--this is your Mapping table
create table #map (psec int, cat int)
insert #map (psec, cat) values (1,72)
insert #map (psec, cat) values (2,73)
insert #map (psec, cat) values (3,74)
insert #map (psec, cat) values (72,209)

--This is your product table
create table #prod (psec int, prodName varchar(10))
insert #prod (psec, prodName) values (72, 'prod1')
insert #prod (psec, prodName) values (73, 'prod2')
insert #prod (psec, prodName) values (74, 'prod3')
insert #prod (psec, prodName) values (209, 'prod4')

select * from #prod

--This is your update statment using your mapping table

update p set
	p.psec = m.psec
from	#prod p
join	#map m on m.cat = p.psec

select * from #prod

drop table #prod
drop table #map
--======================================
output:
psec        prodName   
----------- ---------- 
72          prod1
73          prod2
74          prod3
209         prod4

psec        prodName   
----------- ---------- 
1           prod1
2           prod2
3           prod3
72          prod4


EDIT:
I added another row to the mapping table and the product table to illustrate that you won't have circular reference problems.


Be One with the Optimizer
TG

Edited by - TG on 08/02/2006 13:28:02
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/01/2006 :  17:38:19  Show Profile  Reply with Quote
Did you read the link?

It's in my sig as well



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2165 Posts

Posted - 08/02/2006 :  07:06:40  Show Profile  Reply with Quote
Here are a few more links.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
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.12 seconds. Powered By: Snitz Forums 2000