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
Hi
I want to copy table’s data to clipboard, & I want to use sql script
what is the script?
You rocks ! Man
very helpful post.thanks a lot.
Hi,
i’m newbie in sql server 2012…i’m using SqlServer Express 2012:
1- How can i attach my database to my (LocalDb)\MyInstance and in the same time to my LocalHost\MyInstane.
2- how can i protect my database against external use( access to data, table, views, stored procedure…),in other word, how to encrypt my database.
NB: TDE is not supported in LocalDb
m using SQL server 2008. i’ve created a database, but it isn’t present in the folder App_data in my project…
how to copy the database in this folder???
thanx . You solve my problem :)
I proud to se this kind of feature in sql 2008.
Thanks……………I always come 2 your posts for my sql server problems…to get definite answers…………..Thanks………….
iam copying table from one database to another database, while writing query it gives error , give me ans.
NOTE: in my server name is contains special characters (ex: ‘-‘ , ‘_’ ,’ ‘*’)
hi sir i didn’t find the “script data ” option in table/view options section in my sqlserver2008.please help me on this thank u in advance
how to generated the data script without Identity column, if table consists of Identity column.
My script is little simple:
#DO Import_from_db WRITE to_target_db
#Execute
Isn’t easier way?
:)
You can use the Export Data Option Under the tasks…
Right click on database -> Tasks -> Export Data.. and follow the wizard…
Thanks for your reply Prasanna.
hi sir i didn’t find the “script data ” option in table/view options section in my sqlserver2008.please help me on this thank u in advance
Types of Data to Script options there in Advanced. Select Schema and Data option.
It will work.
In SQL server 2012 (French edition) the option to import data is not present. in Table/view options ???
Does the generated script include everything? Including indexes, foreign keys, all constraints, etc?
very good article…. your tips helped me…. content is crisp and clear. thank you very much
Great. Thanks Ram!
This is slightly different for SSMS 2014. On the Set Scripting Options section, click on Advanced, then under General look for Types of data to script and change it to data only or schema and data.