Generate insert statements from the specific table data using SQL Server

0

When you are deploying your application, sometimes you may want to generate SQL script with the insert statement of the specific database table. The following article shows two easiest ways to do this with SQL Server and SQL Server tools.

  1. Using Database Publishing Wizard (“SqlPubWiz.exe”)

If you install Visual Studio 2008 / Visual Studio 2008 Express full version, it automatically installs the Microsoft SQL Server Data Publishing Wizard in the following location.
[SQL Server Installation Location]\90\Tools\Publishing[version]\ SqlPubWiz.exe

If it is not there, you can download it from the following URL
http://go.microsoft.com/fwlink/?LinkId=119368

SQLPubWiz location

SQLPubWiz location

When you run the “SqlPubWiz.exe”, it opens the database publishing wizard

Database Publishing Wizard

Database Publishing Wizard

Click next and enter the database server connection information accordingly.

Database Publishing Wizard - Database Server Connection

Database Publishing Wizard – Database Server Connection

Click next, and it asks you to select the database, which you want to generate the data from

Database Publishing Wizard - Select Database

Database Publishing Wizard – Select Database

Next screen asks to select the Object type. You can use this database publishing wizard to generate data and schema both. In here I consider only for the data generation part. Now choose “Tables” as an option and click next. (Make sure to uncheck “Script all objects in the selected database”)

Database Publishing Wizard - Choose Object Type

Database Publishing Wizard – Choose Object Type

Then, the next ask to select the table that you want to generate the data from. Select whatever the table you want and click next.

Database Publishing Wizard - Choose Tables

Database Publishing Wizard – Choose Tables

Next step asks to specify the output location. You can enter the file name and the location there.

Database Publishing Wizard - Select an Output Location

Database Publishing Wizard – Select an Output Location

By clicking Next, It shows to choose the publishing options. There are three publishing types, Schema only, Data only, or Schema and data. Here you have to select “Data only”.

Database Publishing Wizard - Select Publishing Option

Database Publishing Wizard – Select Publishing Option

Click next and it generates the insert data script for you.

Database Publishing Wizard - Publishing Progress

Database Publishing Wizard – Publishing Progress

Database Publishing Wizard - Generated Script

Database Publishing Wizard – Generated Script

  1. Using SQL Server Generate Script wizard
SQL Server Generate Script

SQL Server Generate Script

When you select “Generate Script” menu item, it opens the Generate SQL Server Scripts Wizard dialog.

SQL Server Generate Script - Wizard Dialog

SQL Server Generate Script – Wizard Dialog

By clicking next, it asks to select the database that you want to generate the script.

SQL Server Generate Script Wizard - Select Database

SQL Server Generate Script Wizard – Select Database

By clicking Next, It shows to select the script options dialog box. In here, you change the “Script Data” as “True” and click the next button

SQL Server Generate Script Wizard - Choose Script Option

SQL Server Generate Script Wizard – Choose Script Option

Next screen asks to select the Object type. Now choose “Tables” as an option and click next.

SQL Server Generate Script Wizard - Choose Object Type

SQL Server Generate Script Wizard – Choose Object Type

Next screen asks you to select the tables that you want to generate the data from. Select whatever the tables and click next

SQL Server Generate Script Wizard - Choose Tables

SQL Server Generate Script Wizard – Choose Tables

Next step asks to specify the output options. If you select “Script to file” as an option, then you can enter the file name and the location there.

SQL Server Generate Script Wizard - Output Option

SQL Server Generate Script Wizard – Output Option

Next two steps are very straight forward. It shows the script generation summary, and by clicking next it generates the script with insert statements for the selected tables.

SQL Server Generate Script Wizard - Script Summary

SQL Server Generate Script Wizard – Script Summary

SQL Server Generate Script Wizard - Script Progress

SQL Server Generate Script Wizard – Script Progress

SQL Server Generate Script Wizard - Generated Script

SQL Server Generate Script Wizard – Generated Script

Advertisements

Online tools for troubleshooting web, SQL, XML and JSON

0

Have you ever wanted to troubleshoot someone else web page that contains the combination of JavaScript, CSS and HTML? Have you ever wanted to troubleshoot someone else SQL query without entering to the SQL server? Here are some useful online tools that make your life easier.

Following tools provide an online debugging environment for HTML, CSS and JavaScript.

1. jsFiddle

jsFiddle is the most popular JavaScript sandbox. It provides instantly ready coding environment for you to begin experimenting in as soon as the page loads.

http://jsfiddle.net/

JS Fiddle

2. JS Bin

JS Bin is another JavaScript sandbox. The design of this app is quite minimal and allows you to focus on the code. By default, the page only shows the HTML and lives preview panes, but you can easily add JavaScript to that as well.

http://jsbin.com/

JS Bin

3. CSSDesk

CSSDesk is a CSS sandbox that allows you to put your HTML and CSS codes there and view the preview instantly. It is very useful for testing out codes before implementing them in your website permanently.

http://cssdesk.com/

CSS Desk

4. CodePen

CodePen is an app for sharing and playing around with front end code. It’s a site for building stuff from HTML, CSS and JavaScript.

http://codepen.io/pen/

Code Pen

Following tool can use for easy online testing and sharing of database problems and solutions.

1. SQL Fiddle

SQL Fiddle is a tool for easy online testing and sharing of database problems and their solutions. If you do not know SQL or basic database concepts, this site is not going to be very useful to you. However, if you are a database developer, there are a few different use-cases of SQL Fiddle intended for you:

  • You want help with a tricky query, and you’d like to post a question to a Q/A site
  • You want to compare and contrast SQL statements in different database back-ends
  • You do not have a particular database platform readily available, but you would like to see what a given query would look like in that environment

http://sqlfiddle.com/

SQL Fiddle

Following tools act as sandbox environment for JSON,  XML development, including (E)XSLT, XML validation (DTD, schema, RelaxNG) and XQuery.

1. XMLPlayground

XMLPlayground is a sandbox environment for XML development, including (E)XSLT, XML validation (DTD, schema, RelaxNG) and XQuery. It has five development panels into which you can either insert or upload code:

  • XML – your source XML. You can render it as a node tree via the ‘tree’ tab.
  • (E)XSLT – transform your XML with XSLT (1.0). EXSLT an XSL includes are supported.
  • Validation – validate your XML with Schema, DTD or RelaxNG.
  • CSS – style your output
  • XQuery – query or transform your XML with XQuery code (via XQIB)

http://xmlplayground.com/

XML Playground

2. JSON Fiddle

With JSON Fiddle, simulating and posting JSON in Fiddler is just as easy.

http://jsonfiddle.net

json fiddle