31

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);
Edward Tanguay
  • 189,012
  • 314
  • 712
  • 1,047

3 Answers3

80

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

enter image description here

SQL Server 2008 R2

enter image description here

Further reading:

Robert Burke: SQL Server 2005 - Scripting your Database

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

Community
  • 1
  • 1
Thangamani Palanisamy
  • 5,152
  • 4
  • 32
  • 39
  • 1
    This should be marked as the answer. No extensions or paid add ons needed. Did the job appropriately. – Exzile Nov 18 '16 at 15:16
  • 2
    for newer sql server management studio version, you should press advanced button and set `Types of data to script` option to 'schema and data' – S.Serpooshan Jan 20 '19 at 12:13
3

Look here: Simon Holywell: SQL Server 2005 Dump to SQL statements.

What Simon says (no pun intended) is this: There is nothing built-in, but there is a "scripting way" to do it. He made a PHP script you can use.

Tomalak
  • 332,285
  • 67
  • 532
  • 628
2

You should use the database publishing wizard to do that

http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard