How to simply generate the CREATE SQL script for a table and data? [duplicate]

Question

So i'm used to PHPMySQL where if I want to transfer a table from one database to another, I:

• go to the table
• click "export"
• CTRL-C
• go to the other database, insert SQL, CTRL-V

In MS SQL Server 2008 Express, I try:

• right-click, script table as, CREATE TO
  • but this only gives me the CREATE TABLE sql, not the INSERT INTO sql
• right-click, script table as, INSERT TO
  • this gives me INSERT TO sql but assumes that I am going to fill in the data (!)
• so I fire up the SQL Server 2008 Express Import/Export Data Wizard, but it doesn't seem to give me the simple CREATE/INSERT INTO script that I want either. :-(

So how can I get a simple SQL dump of a table in MS SQL Server 2008 Express, the kind that PHPMySQL gives me:

-- phpMyAdmin SQL Dump -- version 2.11.9.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Nov 23, 2008 at 03:34 PM -- Server version: 5.0.67 -- PHP Version: 5.2.6  SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";  -- -- Database: `test` --  -- --------------------------------------------------------  -- -- Table structure for table `members` --  CREATE TABLE IF NOT EXISTS `members` (   `id` int(11) NOT NULL auto_increment,   `firstName` varchar(50) collate latin1_general_ci NOT NULL,   `lastName` varchar(50) collate latin1_general_ci NOT NULL,   `age` int(11) NOT NULL,   PRIMARY KEY  (`id`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4 ;  -- -- Dumping data for table `members` --  INSERT INTO `members` (`id`, `firstName`, `lastName`, `age`) VALUES (1, 'Jim', 'Taylor', 34), (2, 'John', 'McGregor', 23), (3, 'Alice', 'Anderson', 33); 

Answer 1

From the SQL Server Management Studio you can right click on your database and select:

Tasks -> Generate Scripts - Next -> Select objects that you need from list -> click on the advanced Then simply proceed through the wizard. Make sure to set 'Script Data' to TRUE when prompted to choose the script options.

SQL Server 2008

SQL Server 2008 R2

Further reading:

Robert Burke: SQL Server 2005 - Scripting your Database

Taken from stack overflow: How to get script of SQL Server data?