Archive

Archive for the ‘Tools’ Category

Generate insert statements for existing data

March 30, 2010 3 comments

Once in a while I get this query: Do you know any tool to generate scripts for data in an existing table, of course for SQL Server?

Well, There are quite a few options.

If your company is rich enough to sponsor a tool then you could go for Redgate SQLToolBelt. This has quite a nice set of tools to generate scripts from existing data or new data and to compare scripts etc. 

You could also try out the free tool :  http://www.ssmstoolspack.com But this works only with SQL server Management Studio 2005 SP2 or above. 

If you are using SQL Server 2008 you could use the built in option SSMS in 2008. This is a quick run through to generate Insert statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:

  1. DATABASE NAME: Right Click
  2. TASKS: GENERATE SCRIPTS
  3. Under Table/View Options: Set SCRIPT DATA = TRUE

You will then get the create table statement and all of the INSERT statements for the data straight out of SSMS.

You can also use this simple stored procedure given generously by Vyas.  The procedure that does the script generation is here.

I am using it for quite sometime now. Works perfect for my requirement!

Advertisements

MySQL to SQLServer : Free Migration Assistant

January 13, 2010 Leave a comment

Microsoft announced the first CTP of a SQL Server Migration Assistant (SSMA) for MySQL v1.0 which automates aspects of the migration process to Microsoft SQL Server 2005 & 2008 including SQL Azure Database.

SQL Server Migration Assistant for MySQL is available for free download and preview at SSMA 2008 for MySQL v1.0 CTP1 and will be generally available in the summer of 2010. Customers and partners can also provide feedback via ssmateam@microsoft.com.  (via)

LogParser : Parse your log files easily

September 25, 2009 Leave a comment

Download location.

This simple utility given by Microsoft comes in handy at crucial times. I thought of blogging about this after I saw this Scott Hanselman’s list of power tools for 2009.

This utility helps to query the log files like system log, event log, IIS logs or for that matter any custom log files that are in the format of CSV: comma separated or tab separated. Other custom formats are supported too.

For example:

Lets say I have a file with the following comma separated entries.

firstname,lastname,age,sex

firstname1,lastname1,20,M

firstname2,lastname2,20,F

firstname3,lastname3,22,M

firstname4,lastname4,23,F

And the name of the file is “namelist.log”

I can use logparser.exe to just query the text file.

List all records

logparser “select * from namelist.log” –i:CSV

The above command will result in the list of all values in the text file. See the below image.

image

The query engine used in this logparser is equivalent to SQL engine so that you can use where, group by and order by clauses

List selected fields with where clause:

If we want to list the records that have age greater than 20, we could just use this query

logparser “select firstname,lastname,age from namelist.log where age>20” -i:CSV 

image

List selected fields with where clause and output to a CSV file:

The following query lists the same records as the previous query but this also saves the result to a CSV file.

logparser “select firstname,lastname,age into agegrt20.csv from namelist.log where age>20” -i:CSV                                                          

image

The output will be written to the physical drive (the same path from where this utility is being run).

This utility is extremely useful to parse and read the clumsy log files that are both custom created by us and created by the system. Since the query engine is more powerful it is easier to use the queries directly on the file with out having to dump the file to a table in database. We had a 7 MB log file that had number of entries on user action. We had to extract the list of actions done by the user on a particular file. We will need to use like statement more than once in the query. This log parser parsed the file and gave us the exact result with in 11.2 seconds.

Nice handy tool! Happy parsing!