40

If I have a table with data in a database in SQL Server, how can I generate a script that will create the table and add the data too?

If I right click on the table then select Script Table As > Create to > File, that generates a script to just create the table without the data. What I need is a script that creates the table and adds the data that is already existing in the table or 2 separate scripts, one that creates the table and one that adds the data.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
General_9
  • 2,249
  • 4
  • 28
  • 46

4 Answers4

97

Here what you have to do:

  1. right click the database (not the table) and select tasks --> generate scripts
  2. Next --> select the requested table/tables (from select specific database objects)
  3. next --> click advanced --> types of data to script = schema and data
  4. next and finish
Alex
  • 5,971
  • 11
  • 42
  • 80
  • There's a catch here, this only (to my best knowledge) works only for *static* data, because the generated scripts are creating static inserts (line by line). If the script has to copy the data over and over while the source (database table(s)) change, this SSMS feature is useless for the "data" part. Or did I miss anything? – Oak_3260548 May 20 '19 at 11:47
1

Use SSMS scripting feature (Rightclick on database->Tasks->Generate Scripts)

or

use SSMS Tools Pack

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
0

Be aware that generating scripts (schema and data) does not export duplicate ROWS

Do a test (only need 2 columns and half a dozen rows) - I wondered what was happening - in the end it turned out to be a good thing in this instance

Peter
  • 1
-1

Here You can see step by step process of Generate script in SQL Server Management Studio :

Step 1: Right Click on Your Database --> Select Task-->Generate Script..

Step 2: Select Next in Script Wizard

Step 3: Select Script Entire Database

Step 4:Choose Your File Name

and Finish

Now you can use your script file.

KKDev
  • 141
  • 1
  • 3
  • 17