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
In fact Database Publishing Wizard was created before the launch of SQL Server 2008 and was the only tool available for moving data and schema (except for the 3rd party tools). Given that SQL Server 2008 added this feature out of the box, it may be a better choice to go with.
Great article on how to solve a problem I have faced many times.
Why at all waste time in generating script and then rerunning it again to transfer data from one system to another. just
detach…
the database you want to transfer from one system to another
Copy its mdf and ldf file in a pendrive or rar them and mail
paste them in another system
then
attach….
Take backup and restore it
Thats a better choice
Note that if you detach database it would be disconnected and applications accessing them will throw error
In production environment this is not generally recommended
Thanks a lot. It works perfectly
Yousuf Khan,
Not always you will have the freedom to copy the backup or mdf/ldf file to the server box. Usually it happens with shared hosting. There are times when you dont have access to the computer, but will be given a webbased interface where you can run your db scripts only.
regards
Jacob
Hello, i have a similar issue, i have to DB, at beginning both have the same schema but i had added some tables and columms to one of the DB, and i would like to make all these changes up to the second DB, but the 2 DB do not cantain the same data, so i dont want to lose it, do you know how can i update the scheme of the second DB with the first one, but without losing all the data?
thanks in advance.
@Mariano
There are a number of tools available that allow you to compare the schema of two databases and generate the update script to synchronize the changes.
I used Visual Studio Team Edition for Database Professionals (dbpro) which comes with a number of such tools/utilities. http://www.microsoft.com/en-us/download/details.aspx?id=15192
regards
Jacob
I have provided a solution for moving complete db from one system to another. And after that one can run scipt on which system he wants.
In cases
when you dont have access to the computer, but will be given a webbased interface where you can run your db scripts only.
transfer of db is not at all required
I have made web based application like dynamic query builder i.e. asp.net front end sql server back end and application running throug IIS on systems which acess them throug web address provided
These web based application dont require transfer of db on all systems but the application is deployed using IIS on server and others acess it
GOOD ONE!
And other good add on from Jacob, where he mentioned that those who are feeling isolated with 2005, can generate the script by connecting sql server2008 to your instance. (If you have the Management Studio Of SQL Server 2008)
If I recall correctly from another post of Pinal’s, I would like to bring a small change in the generated script. It would really help when you have a long insert script in hand.
Except first we can replace rest of the insert line with UNION ALL, which will finally add up to performance.
But curious to know…
Why dint this tool generate a script with UNION ALL?
Hi,
Scripting data option in sql server 2008 is really a good option, i have tried this, it works. I found it good for Non-xml data in the database.
I have 2 tables which has 4000 records, 420000 records in these two tables 1 field is of xml datatype, in which if i try to have a script of these two tables separately, even then it is giving error as timeout exception. For this we have to do separately in a different way. Is there any way to script these data using sql server 2008, Please let me know
Hi,
I want to copy a database from one sql server to a different sql server.
How can i do this???? Please let me know
@Raj,
Probably backup and restore would be the best choice for you. Take a backup from the old server and restore in the new server.
Hi,
Sometimes when we try to attach a copy pasted .mdf and .ldf file of a SQL Server Database from one system to another it gives an error unable to attach read the Hyperlink for more details.
In such case Create a New Folder paste the .mdf and .ldf
inside it .
Right Click the Folder->
Go to Properties of that folder->
Go to Security->
Go to Edit Security->
Assign Full Control to all users, users like SYSTEM etc.
SQL Server throws this error sometimes because of Security features.
Nice to have at last. Is there a way to make it not script identity fields though? I would prefer not to have hard coded identity field values in my script.
hi raj . you can easily do it by generating scripts
Hi Pinal Sir,
I wish to attach database in sql server 2008 from sql server 2005. how can i do this . Pls tell me both ways :- by scripting as well as by attaching wizard
Read about sp_attach_db in SQL Server help file
Hi,
I have written an sp in ms sqlserver 2005 to access alist as input, the following is the code.
CREATE procedure dbo.getDataBasedOnList
@activityList varchar(500)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL =
‘select * from tl_activity_tbl where activity_id in(‘ + @activityList + ‘)’
EXEC(@SQL)
END
GO
–EXEC dbo.getDataBasedOnList ‘300000293, 355000347 ,110000041, 80000032’
What i need is instead of list i need to pass an xml string like this and need to store the iterated values to a tem table
‘mailid1NoNomailid2NoNo’
please provide a sample code for the same.
Sorry the xml format which i ve send is not properly displayed… the following is the correct format.
“mailid1NoNomailid2NoNo”
Thanks for publishing this article . It was a big help to me. In MS 2008 I have missed database publishing wizard from 2005 , and was not able to find out how to do this in 2008 until I cam across your article.
Thanks for writing!
Thank you very much Pinal …
Does anyone know if the wizard can be sidestepped and the process of exporting a database to a file with data be scripted? I need to schedule this to be done nightly. I also need to set the compatibility level to sql server 2000 so I can get a dtabase to a client.
If anyone knows of the location of a script or tutorial on this I’d be grateful as I’ve not comacross anything.
Thank you!