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.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
in Sql Server 2000 and Sql Server 2005 this kind of utility is not available so here is a way to do so
SELECT * into TabInstMaster FROM OPENDATASOURCE( ‘SQLOLEDB’, ‘Data Source=Servername IP\Name;User ID=USERNAME; Password= PASSWORD’ ).fullyqualifiedtablepath(db.owner.table)
Nice, new feature in sql2008 That’s not possible in sql2000 or sql2005,
In past when i want to transfer data from one server to another server i find the tool which create the script like this,
but in sql2008 it’s very simple now as you explain this functionality.
Dear Mr. Pinaldave,
I am happy to see this feature in 2008.
Thank you very much for Introduced
Gangadhar.
Ashish,
Does the script above generate the ‘INSERT SCRIPT’ for a table? (so that it can be sent to some one over email and can be executed in SSMS to recreate data)?
I found this feature added in SSMS 2008 very interesting. In fact, it is something MYSQL had several years back.
In SQL Server 2000/2005, I used to write a simple dirty query when I wanted to move data quickly from one location to another. Well, it is handy only when you need something quickly and the table has lesser number of columns.
A query like the following can generate an INSERT script for a given table.
SELECT ‘
INSERT INTO Customers(CustomerID, CustomerName)
SELECT ‘ + CAST(CustomerID AS VARCHAR) +
‘,”’ + CustomerName + ””
FROM Customers
/*
INSERT INTO Customers(CustomerID, CustomerName)
SELECT 1,’Jacob’
INSERT INTO Customers(CustomerID, CustomerName)
SELECT 2,’Pinal’
INSERT INTO Customers(CustomerID, CustomerName)
SELECT 3,’Binoy’
INSERT INTO Customers(CustomerID, CustomerName)
SELECT 4,’Sreejumon’
*/
Hi Pinal,
Very good Feature in 2008, Thanks for posting it
Hi Pinal,
Nice feature indeed, use it quite a bit. One of things to be noted is that in the Option Script for Server Version, there are choices for Sql server 2005 and Sql Server 2000. This provides for backward compatability. I found this very useful since we have databases on both SQL Server 2005 and SQL Server 2008.
Thank you
It is a pity the SQL Server 2008 wizard does not resort to row value constructors yet.
Pinal,
Nice to see the feature integrated within the database.
In SQL 2000 and SQL 2005 the same thing could be achieved using SQL Server Database Publishing Wizard. Link here,
http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
I used to use this tool to generate data from SQL 2005 using the option in Server Explorer from VS 2005.
Thanks for sharing
There is a cool free tool from SQLTeam known as Scriptio which lets you choose the objects of interest to be Scripted.
I guess that will also do the necessary
Thanks for really nice information.
You always rock..
@Jacob Sir
No this one not generate insert scripts yea but i use it when i want to move data over two servers and so. i have a procedure for this which needs a table in your DB which stores information about the other server and the tables which we going to send over two servers. I will send my script to Pinal sir, so if he wish to upload it on blog so this is useful to people who working on SQL Server 2000 and SQL Server 2005. :)
i will also try to write a script which create all the insert startements and create statements for all objects.
Very good Ashish, I look forward to see your script.
Hello Sir
I want to copy table structure & its data in Sql Server 2005.
How i will do?
Please help me.
Jignesh,
One option is the Database Publishing Wizard posted by Ponnu.
If you have the Management Studio Of SQL Server 2008, you can connect to a SQL Server 2005 instance and can generate the schema and data for a SQL Server 2005 database.
regards
Jacob
@Jignesh
You can simply generate Scripts for table through SSMS by right clicking on your table and Script Create to in this way you can .. and if you want to script out all the tables in your database then here is a script for that i find it some where on internet and i am still struggling for generate Insert Scripts
so soon you will be find out some thing till then here is script to all create tables…
select ‘create table [' + so.name + '] (‘ + o.list + ‘)’ + CASE WHEN tc.Constraint_Name IS NULL THEN ” ELSE ‘ALTER TABLE ‘ + so.Name + ‘ ADD CONSTRAINT ‘ + tc.Constraint_Name + ‘ PRIMARY KEY ‘ + ‘ (‘ + LEFT(j.List, Len(j.List)-1) + ‘)’ END
from sysobjects so
cross apply
(SELECT
‘ ['+column_name+'] ‘ +
data_type + case data_type
when ‘sql_variant’ then ”
when ‘text’ then ”
when ‘decimal’ then ‘(‘ + cast(numeric_precision_radix as varchar) + ‘, ‘ + cast(numeric_scale as varchar) + ‘)’
else coalesce(‘(‘+case when character_maximum_length = -1 then ‘MAX’ else cast(character_maximum_length as varchar) end +’)',”) end + ‘ ‘ +
case when exists (
select id from syscolumns
where object_name(id)=so.name
and name=column_name
and columnproperty(id,name,’IsIdentity’) = 1
) then
‘IDENTITY(‘ +
cast(ident_seed(so.name) as varchar) + ‘,’ +
cast(ident_incr(so.name) as varchar) + ‘)’
else ”
end + ‘ ‘ +
(case when IS_NULLABLE = ‘No’ then ‘NOT ‘ else ” end ) + ‘NULL ‘ +
case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN ‘DEFAULT ‘+ information_schema.columns.COLUMN_DEFAULT ELSE ” END + ‘, ‘
from information_schema.columns where table_name = so.name
order by ordinal_position
FOR XML PATH(”)) o (list)
left join
information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = ‘PRIMARY KEY’
cross apply
(select ‘[' + Column_Name + '], ‘
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH(”)) j (list)
where xtype = ‘U’
AND name NOT IN (‘dtproperties’)
Thanks Jacob
Now my problem is solved.
@Jignesh,
You are welcome! Glad to know it helped.
As Ponnu said, this can be achieved with the Publishing Wizard. But I’m guessing that this offers a higher level of granular control? :-)
Too bad you can’t use the wizard to configure the table query to use a “where filter” or a “columns to include filter” like DB Artisan. ;-)
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/downloads/details.aspx?FamilyID=7DE00386-893D-4142-A778-992B69D482AD&displaylang=en
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.
@Raj,
detach…
copy mdf+ldf in usb
paste
attach…
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!
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
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
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
[...] Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table [...]
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.
[...] Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table [...]
Thanks a lot
Hi
I want to copy table’s data to clipboard, & I want to use sql script
what is the script?
You rocks ! Man