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 a lot for this guide. Helped me a lot. Have searched for a solution like this for a long time.
Didn’t know about the Script Data feature in SQL 2008.
will you guys please help me to find nonclustered index size without data
nice article but the matter i m searching is not this
Script Data Option is not found in my sql server 2005.
Can i script data in sql 2005.
It is not possible in version 2005
Great article, did exactly what i needed, thanks!
hi..i want to transfer data[tables] from sql server 2005/2008 to oracle 10g now problem is that data is on another machine where sql server 2005 runs and i want to transfer it on another machine where oracle 10g running.i cant install oracle on server machine.in,oracle 10g sql*loader is utility to load data from file.so,in sql server 2005 i import data in plain text file.[.csv file] to use that file to load data from sql loader.now,issue is that data is large so,it takes too much time to create text file for each table..so,is there any another way to generate data to another file like excel,xml, or is that possible to give OLE db connectivity to copy data from one machine to another.please,provide me steps to solve this problem. and any help to migrate data from sql server to oracle.thanks in advance..
The “Cast” and “Convert” words generated by script doesn’t work in visual studio
What is the most efficient way to move records from SQL Server 2008 to Oracle?
I have to copy one table into other table but both are on different servers . please suggest . generating scripts is throwing error .
What was the error you got? Do you want to copy all the objects from one server to another?
Would it be possible to have only a set of rows and not all of them generated in this insert statement ?
I dont think it is possible
you r right but i want to import or export all database objects in mssql.
you r right but i want to import or export all database objects in mssql.
sahil singh(MLM Developer)
you r right but i want to import or export all database objects in mssql.
sahil singh(MLM consultant)
9911425805
hi
I have DB( SQL 2008R2) the several file gorup and db is very big.
i want make srcipe of this db condition includ
1- All table,Sp,viw,trigger and …
2 – jast it have one filegorup (Primery)
In SMSSEE the Script Data option is not immediately apparent.
In the General group there is an option to Script Schema and Data.
Works like a charm.
Another interesting point is that it will script specifically for SQL Server 2000, 2005 and 2008
Skip
Hello Deve & jacob sebastian,
Actually i am getting error when i am using script wizard, i have already created on script and successfully used the same on another server.. But this time i am getting below errror…
Insufficient memory to continue the execution of the program. (System.Windows.Forms)
Please help
Hi,
I am getting the error with a large table. I wonder if you managed to reseolve this?
Regards, Ali
Thanks for posting this. you described in a very clean way ..
i have a problem.
if i do this .this error Appear
Unable to cast object of type ‘System.DBNull’ to type ‘System.String’. (Microsoft.SqlServer.Smo)
please help me!
Hi Pinal,
Thanks for your very useful post.
When I am in the ‘Generate Scripts…’ wizard screen and try to create scripts for table which is very large I will receive the ‘System.OutOfMemoryException’ so thus my scripts didn’t create at the end… I was wondering if you would know how to resolve this? (i.e. I am using SQL Server 2008).
Many thanks,
Ali
Are you trying to generate script only or along with data? If you choose to script with data it may throw that error for large table
You are my superman
Thanks