46

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.

Improve this question
1

4 Answers 4

Reset to default
114

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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?
1

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

or

use SSMS Tools Pack

Improve this answer

Comments

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

Improve this answer

Comments

-2

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.

Improve this answer

3 Comments

What does this answer add compared to the two other answers posted 3.5 years before?
boldfaced instructions.
Ah yes way clearer now /s

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.