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 >>
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.
On Choose Script Option Screen of Script Wizard under section Table/View Options Look at Script Data row and Turn the Option to True.
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.
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
Clicking on Finish button will generate a review screen containing the required objects along with script generating data.
Clicking on Finish button one more time will generate the requested output.
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.
[youtube=http://www.youtube.com/watch?v=lSh3fq-MikE]Reference : Pinal Dave (https://blog.sqlauthority.com)
137 Comments. Leave new
Thank you ! I’ve been looking for this for hours !!
I’m using 2008 R2 sql server,
million thanks to you Sir..!!!!
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 ;)
how can i do above given step with a single query for a database complete objects .?????????????
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
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
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
how to copy database with it’s table and it’s data both from server to local database
Hi Pinal,
Thank you for a great post. I used your tip to move data between Dev and Prod.
Jon
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’.
Awesome………………….!!!!!!!!!
Saved my precious time from traditional method of taking backup.
Thanks for posting……….!!!!!!!
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
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?
Thank you a lot for sharing… :)
thanks for sharing – this is a handy tip!
-Jesal
This is avery halpful feature that you have posted. Thank you very much.
Is it possible to add “Go” keyword right after each INSERT statement ? This way sql server wont take much system memory.
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?
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?
It gives me error as following what to do ?
Exception of type ‘System.OutOfMemoryException’ was thrown.
Thanks a lot