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

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

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

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.

Solarwinds

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

On Choose Script Option Screen of Script Wizard under section Table/View Options Look at Script Data row and Turn the Option to True.

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

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.

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

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

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

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

Clicking on Finish button will generate a review screen containing the required objects along with script generating data.

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

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

Clicking on Finish button one more time will generate the requested output.

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

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 (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – 2008 – Design Process Decision Flow
Next Post
SQLAuthority News – Author’s Birthday – Top 7 Commenters – Volunteers

Related Posts

137 Comments. Leave new

  • Thanks a lot

    Reply
  • Hi
    I want to copy table’s data to clipboard, & I want to use sql script
    what is the script?

    Reply
  • You rocks ! Man

    Reply
  • Ronok Bhowmik
    June 13, 2013 6:29 pm

    very helpful post.thanks a lot.

    Reply
  • Anis_Seghaier
    June 14, 2013 7:06 pm

    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

    Reply
  • 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???

    Reply
  • thanx . You solve my problem :)

    Reply
  • I proud to se this kind of feature in sql 2008.

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

    Reply
  • 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: ‘-‘ , ‘_’ ,’ ‘*’)

    Reply
  • 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

    Reply
  • how to generated the data script without Identity column, if table consists of Identity column.

    Reply
  • My script is little simple:
    #DO Import_from_db WRITE to_target_db
    #Execute
    Isn’t easier way?
    :)

    Reply
  • 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

    Reply
  • In SQL server 2012 (French edition) the option to import data is not present. in Table/view options ???

    Reply
  • Does the generated script include everything? Including indexes, foreign keys, all constraints, etc?

    Reply
  • very good article…. your tips helped me…. content is crisp and clear. thank you very much

    Reply
  • This is slightly different for SSMS 2014. On the Set Scripting Options section, click on Advanced, then under General look for Types of data to script and change it to data only or schema and data.

    Reply

Leave a Reply

Menu