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
Thanks solved an issue for me!
Very useful article. Thanks!
Thanx a lot Pinal sir….that is simple ,easy and quick….solved my problem..:)
Hi,
Really good read.
Thanks for such good work.
Hi,
when I try, the script data option doen’t appear. How can i resolve this??
thanks
How i can get the existing table structure to new table without records using query language…?
i am using the fallowing stmt
create table newtable as select * from mytable where 1=2;
it is working fine in Oracle……..
how can i do this in ms sql2005
select * int newtable from mytable where 1=2;
I am not able to see Script Data: option ,Please tell me the way how can i get that option if it is not there?
I too have same problem,
“Script Data” not available in my sql server 2005, please help me
Hi Sir,
This article really useful lots of thanks to you.
I have one application in VS 2005. in c#. In My application i add one mdf file the mdf file name is Database1.mdf. And In My PC in SqlServer 2005 i create one database. In both database the few column same. Now I want to in both database matching rows show in datagridview
its working.. and its very usefull
this is one cool tool we can use for getting Data of table with thousands of records.Thanks keep up the good work
Thanks alot,it solve my problem !!!
This is great – now I can copy the whole database in script.
Hi Pinal,
Your articles are very helpful.I am glad to find this article which I was searching for.
The generate script wizard works good if couple of tables are selected. I want to script whole database and query editor window is not sufficient to script the whole database. I tried to script it to a file but I don’t know how to run it in management studio. Is there any tool?
please help!!
Thanks
Prasanna
Script out tables, procedures seperately and run them
Hi pinal,
I want to know some compatibility issue between sqlserver2005 and 2008, i created a db in 2008 then i generate a script with schema and data now i run that script in 2005 but 2 tables are missing,i dont know the reason ,even it did not gave any error in script ……………pls help
What are the two tables missing? Note that the script that works in 2008 may not work in 2005
ya,but y it does not work,two tables r related with configuration data
this is a nice feature in 2008, i like it . Thank you for posting it
Great Topic, Save me hoursssss of works. Thanks a lot.
The below are four types type of the database recovery on different server will help you in clearing the ambiguity.
1) Backup the database and restore the database on same/different server.
2) Detach the database, copy the database files (mdf, ndf and ldf) to the desitination server and attach database.
3) By using Copy database wizard.
4) Script the database objects and execute on the destination database (As mentioned above method, this is the only option choose to change collation settings to the objects).
in sql 2008 r2 no “script data” item found !!
Follow this:
In set scripting options….
Advance->General->Types of data to script->Data only
señor ashis como puedo transferir informacion de una base sql server 2008 a otra sqlserver 2008
solo la informacion de los registros