-1

My object is to keep track the frequency of viewing of records.

For example- A visitor is searching books. His search matches with 1200 books. When the result list is being sent back to the visitor, I would like to store ID of those 1200 books in another table. So that I can get the data of how may times a book was searched.

Hypothetically, the sql might be something like-

SELECT BookId, BookName FROM Books(INSERT INTO SearchLogs(BookId) VALUES(BookId))

I don't even know whether it is possible or not. I am using SQL Server 2008 R2.

Any idea?

s.k.paul
  • 7,099
  • 28
  • 93
  • 168
  • Why ? you can do it in two statements within a single batch.... `SELECT BookId, BookName FROM Book; INSERT INTO SearchLogs(BookId) VALUES(BookId)` – Charles Bretana Apr 12 '15 at 13:17
  • It would be possible to do this in one statement with the `output` clause though in order to access the other columns [you would need to use `merge` rather than `insert`](http://stackoverflow.com/q/5365629/73226) – Martin Smith Apr 12 '15 at 13:26
  • So you want to `INSERT` some `BookId`s into `SearchLogs` and **then** see the `BookId`s and `BookName`s you just inserted? – nhgrif Apr 12 '15 at 13:37
  • @nhgrif Nope, but just the reverse. – s.k.paul Apr 12 '15 at 14:02
  • 1
    I think this is a buisness layer task. – Fabio Apr 12 '15 at 19:07
  • If you want to count how many times a book was searched for, wouldn't it be more efficient to update a counter per book rather than to insert the ID of the book repeatedly into a table? – flup Apr 12 '15 at 20:24
  • @flup, Thank you for the suggestion. Would you please post any code snippet? Another issue is here, I want to keep the datetime stamp. – s.k.paul Apr 13 '15 at 10:56

2 Answers2

1

How about something like this:

INSERT INTO SearchLogs(BookId) 
SELECT BookId FROM Books
--WHERE CLAUSE IF NEEDED
nhgrif
  • 61,578
  • 25
  • 134
  • 173
renakre
  • 8,001
  • 5
  • 46
  • 99
  • I guess the number of items in `select` and `insert` need to match? can you try this `INSERT INTO SearchLogs(BookId) SELECT BookId FROM Books` just to see if it works? – renakre Apr 12 '15 at 13:34
  • Your solution only insert records in SearchLogs table. but does not return any records from Books table. – s.k.paul Apr 12 '15 at 14:00
  • you are right. With this approach, however, we have a VERY efficient data insertion (we did not use loops, and insert all data at once: https://technet.microsoft.com/en-us/library/ms189872%28v=sql.105%29.aspx). You can just add the `SELECT BookId, BookName FROM Books` statement after the `INSERT`, this will not create any performance problems. – renakre Apr 12 '15 at 14:09
  • It will create a performance problem because same query with same WHERE clause need to run twice in SELECT AND INSERT query – Fabio Apr 12 '15 at 20:08
1

I assume you have some function in your program code which return a result of SELECT query.
Use both queries separatly. Just add in this function code which executes INSERT query with ID's from SELECT query in your log table.

Both queries can be run under same connection...

I think this kind of task is a for buisness logic layer.

Fabio
  • 31,528
  • 4
  • 33
  • 72