Coolest Thing I Just Learned about SQL 2008

I am in the process of building a new web site, and in the process, using some of the same data tables from another site I built.  (Who wants to reinvent the wheel!?)So, rather than going through the arduous task of creating each column in a new table, one by one, I feel there should be a ‘Copy’ function in SQL Server Management Studio (I’m using the 2008 version).  But, right-click on a table name and, ‘Copy’ is conspicuously missing! 

But what I did find, just today (I’ve been doing this sort of work for many years), is a ‘Script Table As’ entry in the context menu.  Hmmmm…. 

Then off that, there are various table-level commands (Create to, Drop to, Alter to….) and each one has a little right arrow.  So I want to create a new table based on the one I was right-clicking on.  So I hovered over ‘Create to’ and the first option is ‘New Query Editor Window’ – which I figured couldn’t hurt, so I clicked it.  (right-click on table name, then ‘Script Table As’>/’Create To’>/’New Query Editor Window’) 

It put the SQL creation script for that table including all its column specs into a new query window.  Wow!  Very cool.  All I had to change was the first line from: 

USE [CurrentDatabaseName] to USE [DestinationDatabaseName] 

 

Then click the Execute button.  Done!  New table created, an exact copy of the original, but without the data.  

Of course, you have the option of changing the SQL script to alter, omit or add column names, lengths, datatypes, etc., but only if you know what you’re doing!  

You can always use the ‘Design’ feature of SQL Server Management Studio for any subsequent changes to the table to fit the needs of the new application.  

 I guess you can learn something new every day!

Read Full Post »