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

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);

Answers


Try SSMS Tools Pack: Generate Insert statements


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: Get script of SQL Server data


Need Your Help

htaccess add language part to existing redirect rules

php .htaccess redirect

I have quite a complex .htaccess with 30 redirect rules. Now the client wants to add language specific parts to the beginning of the urls.

Monitor GZip Download Progress in Java

java download gzip monitor progress

I download some files in my java app and implemented a download monitor dialog. But recently I compressed all the files with gzip and now the download monitor is kind of broken.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.