Tip SSMS: Script your Entire Table including the Data – a hidden gem.

  • Article

Have you ever written a TSQL SELECT statement that builds INSERT Statements so you can script the entire table? Usually we do it to make it simple to create & load a “Reference or Lookup” table? Sure, Most DBA’s have.
This Waay Cool little feature is buried in the options. You’ll never need to write your own again.

The Basics:

Most people have found you can script out a single table or stored procedure. That capability was there in SQL 2000. Just Right-Click a Table, View, Stored Proc or other object.

image

The Handy:

But click a little higher, at the Database Node & you discover this little gem. Generate Scripts…

image

This opens the “Generate SQL Server Scripts Wizard” which might not seem that exciting. Until you get to the 3rd pane & discover this little sleeper. Script Data = True
It is set to False by default. So it will only reward the inquisitve.

image

And faster than you can say “U Beauty”, instant reference table, just run script.

image

I’m not saying that SSIS is redundant, Bulk-Copy is highly valuable. But for those little reference tables, this is a winner.

In case you are wondering.
Yes the Generate Scripts Wizard is included in SQL 2005.
No the Script Data option is not in SQL 2005.

So I’d recommend you upgrade (there is heaps of other value you are missing out on, things that save big $$$). But if you can’t, a work around is to install the SQL 2008 tools on one box & this should then work for you. I’ve not checked if this is possible from the SQL 2008 Express Edition. I suspect you may need SQL Server 2008 Developer Edition. 

Enjoy
      Dave.

Crap thought for the Post: Is it possible to catch both Swine Flu & Averian(Bird) Flu while playing a game of Reversi? And if you did, would you dream of Pigs flying backward?

Technorati Tags: SQL Server,SQL Server Management Studio,SSMS,Tools