Using Insert, Count and Select to update Table

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

Transact-SQL (2000)

Using Insert, Count and Select to update Table

Author

Topic

aragorn5
Starting Member

1 Post

Posted - 2008-07-17 : 09:08:16

Hello, I've not had much experiance with T-SQL and wondered if anyone could help.
I am trying to populate a table(UpdateLogs) by selecting data from one table(IISLogs) and inserting it into UpdateLogs. I am using the COUNT statement to produce the data I require forthe new table.

I am using the following select statement;

select count(csUriStem) as '/shares.asp'
from IISLogs
where date = CONVERT(DATETIME, '2008-05-29 00:00:00', 102) and csUriStem ='/asp/shares.asp'

This statement brings back a count of all the occurences of '/asp/shares.asp' within the date specified.

My problem is I have 30 columns in Updatelogs to fill called '/asp/shares.asp', '/asp/schemes.asp', '/asp/logins.asp' etc etc.

I am not sure how to select multiple items of data from another table using a WHERE and a COUNT statement and insert the results into a row containing multiple columns in another table.
I also need to continually update the new table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 12:53:38

Do you mean you need to populate each csUriStem value and its count onto logs table ? then this is what you want

UPDATE UpdateLogs
SET 
u.[/asp/shares.asp]=SUM(CASE WHEN csUriStem ='/asp/shares.asp' THEN 1 ELSE 0 END),
u.[/asp/schemes.asp]=SUM(CASE WHEN csUriStem ='/asp/schemes.asp' THEN 1 ELSE 0 END),
... other columns similarly
FROM IISLogs
where date ='2008-05-29'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources