SQL SERVER – 2008 – Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table

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)

About these ads

132 thoughts on “SQL SERVER – 2008 – Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table

  1. 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)

  2. 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.

  3. 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’
    */

  4. 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

  5. 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

  6. @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. :)

  7. 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

  8. @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’)

  9. 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. ;-)

  10. 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.

  11. 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….

  12. 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

  13. 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.

  14. 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

  15. 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?

  16. 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

  17. 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.

  18. 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.

  19. 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

  20. 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.

  21. 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!

  22. 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!

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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).

  28. señor ashis como puedo transferir informacion de una base sql server 2008 a otra sqlserver 2008
    solo la informacion de los registros

  29. 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..

  30. I have to copy one table into other table but both are on different servers . please suggest . generating scripts is throwing error .

  31. 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)

  32. 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

  33. 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

  34. 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!

  35. 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

  36. 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 ;)

  37. 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

  38. 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

  39. 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

  40. 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’.

  41. Awesome………………….!!!!!!!!!
    Saved my precious time from traditional method of taking backup.
    Thanks for posting……….!!!!!!!

  42. 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

  43. Pingback: SQL SERVER – Generate Script for Schema and Data – SQL in Sixty Seconds #021 – Video « SQL Server Journey with SQL Authority

  44. 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?

  45. 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?

  46. 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?

  47. Pingback: SQL SERVER – Cycle Clipboard Ring in SSMS – SQL in Sixty Seconds #045 – Video | SQL Server Journey with SQL Authority

  48. 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

  49. 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???

  50. Pingback: SQL SERVER – Weekly Series – Memory Lane – #040 | Journey to SQL Authority with Pinal Dave

  51. Thanks……………I always come 2 your posts for my sql server problems…to get definite answers…………..Thanks………….

  52. 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: ‘-’ , ‘_’ ,’ ‘*’)

  53. 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

  54. Pingback: SQL SERVER – Copy Database – SQL in Sixty Seconds #067 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s