SQL SERVER – 2008 – Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table

Just about a year ago, I had written on the subject of how to insert data from one table to another table without generating any script or using wizard in my article SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE. Today, we will go over a similar question regarding how to generate script for data from database as well as table. SQL Server 2008 has simplified everything.

Let us take a look at an example where we will generate script database. In our example, we will just take one table for convenience.

Right Click on Database >> Tasks >> Generate Scripts >>

SQL SERVER - 2008 - Copy Database With Data - Generate T-SQL For Inserting Data From One Table to Another Table data1

SQL SERVER - 2008 - Copy Database With Data - Generate T-SQL For Inserting Data From One Table to Another Table data2

This will pop up Generate SQL Server Scripts Wizards >> Click on Next >> Select Database >> This will bring up a screen that will suggest to Choose Script Option.

SQL SERVER - 2008 - Copy Database With Data - Generate T-SQL For Inserting Data From One Table to Another Table data3

On Choose Script Option Screen of Script Wizard under section Table/View Options Look at Script Data row and Turn the Option to True.

SQL SERVER - 2008 - Copy Database With Data - Generate T-SQL For Inserting Data From One Table to Another Table data4

The Next Screen will ask about object types. Select all the objects that are required in the generated script. Depending on the previous screen it will show few more screen requesting details about the objects required in script generation.

SQL SERVER - 2008 - Copy Database With Data - Generate T-SQL For Inserting Data From One Table to Another Table data5

SQL SERVER - 2008 - Copy Database With Data - Generate T-SQL For Inserting Data From One Table to Another Table data6

On the very last screen it will request output options. Select the desired output options of Script to file, Script to Clipboard or Script New Query Window.3

SQL SERVER - 2008 - Copy Database With Data - Generate T-SQL For Inserting Data From One Table to Another Table data7

Clicking on Finish button will generate a review screen containing the required objects along with script generating data.

SQL SERVER - 2008 - Copy Database With Data - Generate T-SQL For Inserting Data From One Table to Another Table data8

SQL SERVER - 2008 - Copy Database With Data - Generate T-SQL For Inserting Data From One Table to Another Table data9

Clicking on Finish button one more time will generate the requested output.

SQL SERVER - 2008 - Copy Database With Data - Generate T-SQL For Inserting Data From One Table to Another Table data10

Similarly, if you want to generate data for only one table, you can right click on the table and follow almost a similar wizard. I am sure this neat feature will help everybody who has been requesting for it for a long time.

Watch SQL in Sixty Seconds Episode on same subject.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Best Practices, Database, DBA, SQL Scripts, SQL Utility
Previous Post
SQL SERVER – 2008 – Design Process Decision Flow
Next Post
SQLAuthority News – Author’s Birthday – Top 7 Commenters – Volunteers

Related Posts

137 Comments. Leave new

  • weirdoweirdo113
    November 28, 2011 5:38 am

    Thank you ! I’ve been looking for this for hours !!
    I’m using 2008 R2 sql server,
    million thanks to you Sir..!!!!

    Reply
  • weirdoweirdo113
    November 28, 2011 5:57 am

    Just One Thing, if you are using SQL Server 2008 R2, things and steps on the wizard have changed, after selecting “Generate Scripts” from desired Database:

    1. In the “Set Scripting Options” step, press the “Advanced” button.
    2. Then the “Advanced Scripting Options” window appears, under
    ‘General’ section, scroll to the last row where it says ‘Types of
    data to script’
    3. Change the Option “Schema only” to “Schema and data”
    4. You’re now ready to generate your database and/or tables with data.

    Cheers ;)

    Reply
  • how can i do above given step with a single query for a database complete objects .?????????????

    Reply
  • Rejith Egodahewa
    January 15, 2012 10:18 am

    Dear Author,

    We are using SQL Server 2005 db having more than 1500 tables. How can I copy data from a Similar data base having same no. of tabels..?

    Each table has an identity column (PKey), an incrementing No. which has no relationship to the row of data, and it is referenced by other tables (foreign key) inorder to maintain relationships.

    Please advise…..

    For more details I can send a sample..

    Thanks

    Rejith

    Reply
  • Happy to find this solution, but …
    I have to transfer 500.000 records and SQL Server Management Studio doesn’t accept to read a so big file. Is there another way to execute the script?

    Thanks for help

    Reply
  • Is there a way to script data for the top 50 rows or so rather than the entire table? In other words can a query or temp table be used instead of the entire table to generate the insert statements?
    Thanks,
    Sam

    Reply
  • how to copy database with it’s table and it’s data both from server to local database

    Reply
  • Jon B. Bushey
    March 6, 2012 2:58 am

    Hi Pinal,

    Thank you for a great post. I used your tip to move data between Dev and Prod.

    Jon

    Reply
  • BUt in SQL Server 2008 R2, the options are little different, after clicking “Advance Tab” in 3rd Screen, We should change “Types of data to script” to ‘Schema and Data’.

    Reply
  • Awesome………………….!!!!!!!!!
    Saved my precious time from traditional method of taking backup.
    Thanks for posting……….!!!!!!!

    Reply
  • How to generate script for selected records in a table?
    I want to update the production server with the new records.
    Is this possible in SSMS 2008 R2

    Reply
  • I used this.But when i click script data–>true, Its not working. if scriptdata is false means its working.But i need script data.give me suggestions?

    Reply
  • Thank you a lot for sharing… :)

    Reply
  • Jesal Ashar (@jesa_l)
    August 27, 2012 4:16 am

    thanks for sharing – this is a handy tip!
    -Jesal

    Reply
  • This is avery halpful feature that you have posted. Thank you very much.

    Reply
  • Is it possible to add “Go” keyword right after each INSERT statement ? This way sql server wont take much system memory.

    Reply
  • I’m using SQL Server 2008 R2 Developer Edition, but don’t see the Script Data option under Table/View Options. Other options are available though, such as Script Change Tracking, Script Check Constraints, Script Data Compression Options, Script Foreign Keys, etc. Am I missing something?

    Reply
  • I got the data option now, after reading Royal PS’s comments. Thanks.
    However, I got an “Getting the list of objects from ‘DB_name'” error, after selected “Data only” for Types of data to script. I’m able to script “Schema only” or “Schema and data” though. Can anybody help?

    Reply
  • It gives me error as following what to do ?

    Exception of type ‘System.OutOfMemoryException’ was thrown.

    Reply
  • Thanks a lot

    Reply

Leave a Reply