Really easy/dumb SQL question...

Pardon me, my SQL knowledge is not advanced enough to know how to do this, though I'm sure it's pretty simple:

Let's say I have a table called products, with fields like SKU, name, price. And let's say I have a temporary table with changes to be made (updates) to the current products.. same fields, SKU, name, price. I basically would like to be able to update currently existing entries in the products table, with the changes shown in the temporary table. Example:

PRODUCTS:
T231,Crazy Stick,4.99
023J87,Basketball Hoop,12.99
GB-572,CD Rack,8.99

TEMP. TABLE:
GB-572,Wooden CD Rack,8.99
T231,Crazy Stick,3.95

So I'd like to just merge the products in temp table w/ the ones in products. How can I do this?

Thanks!INSERT INTO PRODUCTS (SKU, name, price, etc)
SELECT SKU, name, price, etc
FROM #TEMP_TABLE|||But wouldn't that insert them as new items, ignoring the current entries? I'd like to update the existing entries in the Products table...|||Oh... well you need to do an UPDATE statement then. Naturally there has to be some sort of common key involved. I assumed you needed an INSERT statement as there was no common key.

General syntax:

UPDATE yourTable
SET yourTable.yourField = #temp.yourField, yourTable.field2 = #temp.field2
FROM yourTable INNER JOIN #temp on yourTable.key=#temp.key|||Ahhh.. thanks a ton!!

Really dumb Stored Proc Question. But I need help and Im lost.

Right now, I'm working on the database of a new project. The project is basically a keyword tracking and referral tracking website directory. My plan is to give the website some java code that will allow me to grab the http_referer and request_uri per page load. (everytime they get traffic)

Right now, I've got some tables I think will work fine. One is the storage table, where I'll store what the javascript grabs.

table.http_ref (columns will be)
uid (Unique ID)
http_referer
request_uri

Then I got the results page I want to parse the http_ref table into. For example.

Here we have a google results page which is a search for baby shoes. This would be the http_referer

http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
And if someone clicks on a link get this page, which let's say I'm tracking in my project.
http://www.thebabymarketplace.com/securestore/c54581.2.html
in my http_ref table I will have
uid
(whatever)
http_referer
http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
request_uri
http://www.thebabymarketplace.com/securestore/c54581.2.html
so I want a stored proc to grab the http_referer column and parse it for the refering search engine
http://www.google.com
and the key words
search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
Then grab the uri_request, which should be the web page visited,
and INSERT into another table where I will hold
keywords
referer
refered page
Any suggestions? I've been going nuts over this.

Then the key point is to parse the http_referer. You should gurantee that all http_referer strings are in a same format, for example the search string begins with '/search?' and the keywords start with '&q=' and locate the end of the string. Then you can use such procedure:

CREATE PROC sp_ParseHttpRef
AS
INSERT INTO Tbl_Parsed
SELECT SUBSTRING(HttpReferer,1,CHARINDEX('/search?',HttpReferer)-1),
RIGHT(HttpReferer,LEN(HttpReferer)-(CHARINDEX('&q=',HttpReferer,CHARINDEX('/search?',HttpReferer))+LEN('&q=')-1)),
Request_URI
FROM Tbl_SourceHttp
go

|||

Thank you very much.

So, can I take this proc and use it as a template to track other search engine referer strings?

For instance:

MSN

http://search.msn.com/results.aspx?q=baby+shoes&FORM=SSRE

or Yahoo

http://search.yahoo.com/search?p=baby+shoes&sm=Yahoo%21+Search&fr=FP-tab-web-t&toggle=1&cop=&ei=UTF-8

and create a Proc for each search engine I'm tracking?

Seems as if I would then have to Schedule the proc's to run, our just call them when I grab the http_referer and request_uri?

|||

Sure you can use the sample as a template, just keep in mind to parse the http_referer correctly. You do not need to schedule the proc to run, you can call it when you grab the http_refer and request_uri in your application, or you can make the proc as an insert trigger on the table which stores the http_refer and request_uri. For example:

create trigger trg_ParseHttp on Tbl_SourceHttp for insert
as
insert into Tbl_Parsed
SELECT SUBSTRING(HttpReferer,1,CHARINDEX('/search?',HttpReferer)-1),
RIGHT(HttpReferer,LEN(HttpReferer)-(CHARINDEX('&q=',HttpReferer,CHARINDEX('/search?',HttpReferer))+LEN('&q=')-1)),
Request_URI
FROM inserted
go

Real Newbie Post...

I was hoping there would be a newbie section in here... But I'll post anyway (And sorry in advance if its a really dumb question)

I am using VWDE and have a SQL DB which has been imported from one of my existing forums, when it shows in the database explorer its show like below

tblArcade (jdName)
tblArcadePB (jdName)

etc..etc...

What I want to know is why this DB has (jdName) after the Table name and my other DB's shown don't?? They are just shown as

tblArcade
tblArcadePB

etc..etc..

My main questions are "How do I remove the brackets from the forum DB" as when I try to preview data in VWDE I get a message

Invalid object name 'tblArcade'.

I know its to do with this extra bit as it only happens to the tables that have this on the end... Hope that makes sense and someone can help me, if you could reply as if I'm really dumb it would be appreciated

Guessing this must be a stupid question...|||Anyone....|||Dammit... I'll keep clicking away in SQL till I get it to work...|||

This is because the table belongs to another schema rather than the default schema of the login which you use in the database connection. To access such database objects, you need to use full qualified names including schema name. For example:

SELECT * FROM jdName.tblArcade

For more information about schemas in SQL Server2005, you can refer to:

http://msdn2.microsoft.com/en-us/library/ms190387.aspx