Distribution of data in sql server

Posted on 2014-08-26
Medium Priority
Last Modified: 2014-09-18

I have some ideas to process the following steps but will appreciate it if some experts share their thoughts with me:
(frequency is daily)

1) Information from IIS log file will be exported into a CSV (using IIS Logger in PS) file then a C# code will insert them all in a table (let's call it RawTable).

2) After having all the information for let's say for today, I need to break them into different tables. One table holds onto Client IPs and Server IPs, one table will have the browsers info and cookies information and etc

In general I'm having around 7 tables.  

what technique do you suggest I go with? should I write few stored procedures or a 3rd party tool?''

*This process happens every midnight or early morning.

Question by:akohan
  • 3
  • 3
LVL 20

Expert Comment

by:Marten Rune
ID: 40286884
Definatily go for Stored Procedure. That gives you all the flexibility that a 3'rd party tool wont give you.

If you need "regular expressions" to mine your data, this can be incorporated into the SQL instance using this technique:

Regards Marten
LVL 29

Expert Comment

by:Dan McFadden
ID: 40287623
Why do must you do an export to CSV.  IIS's HTTP logs are well documented and well formed.  Why not just have the C# code consume the original logs?  This way you remove an un-needed step in the loading process.

Why not take it one step further?  instead of importing the https logs file, line for line, into a table... why not process the logs using C#?  Then store the parsed data into your 7+ tables.  This removes a second step in the loading process.

To do this in a different way...

1. consume http logs with Microsoft's Lop Parser tool (http://technet.microsoft.com/en-us/scriptcenter/dd919274.aspx)
2.  run log parser with the various SQL-like queries to get the data you want into your tables.
3. output the results of your queries to a csv file
4. use the SQL Server Bulk Insert command to put the data into your 7+ tables.  http://msdn.microsoft.com/en-us/library/ms178129(v=sql.110).aspx

I guess it all depends on how much work you want to do or if this is just a learning experience.

I'd say, to learn.. go the C# coding path.  To get it done with tools that are readily available and well documented... Log Parser and Bulk Insert.


Author Comment

ID: 40288755
Hi Dan,

Yes, already I am using Log Parser but didn't think of Bulk Insert. I heard it in past but never used it.

I will get back to you guys,
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 29

Expert Comment

by:Dan McFadden
ID: 40300934
Any luck on this?


Author Comment

ID: 40302275
Hello Dan,

Yes, I'm thinking of Sql Server Bulk insert but can it be automated?

The truth is that I'm using LogParser so currently *.log > *.csv >  then my code reads it and inserts into database but now that you have pointed out to Sql Server Bulk insert then thinking of using it.

LVL 29

Accepted Solution

Dan McFadden earned 2000 total points
ID: 40302850
Yes, it can be automated.  Below are links for reference and examples:

Background:  http://msdn.microsoft.com/en-us/library/ms141239(v=sql.110).aspx

Developer Docs:  http://msdn.microsoft.com/en-us/library/ms188365.aspx

Please note, I didn't notice a reference to what version SQL Server you're using, but you can use the drop-down near the center-top of the links to choose your version.  I used SQL Server 2012 in the links.


Author Closing Comment

ID: 40331188
Thank you for the details.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question