?
Solved

copying data from one table to an identical one in another database

Posted on 2014-01-29
4
Medium Priority
?
1,220 Views
Last Modified: 2014-01-30
Hi there!

In SQL Server 2008 (Management Studio) I am able to

"Script Table as > Create To > New query editor window"

and copy-paste-and run it (the code generated) in "myLittleAdmin for SQL" present at Plesk.

Now I need to do "something similar" as far as (just) data is concerned: through SQL Server 2008 Management Studio, I want to copy all data present at a certain table, and paste all the same data in the corresponding (identical-structured) table present at another server, through "myLittleAdmin for SQL" present at Plesk.

I hope I was clear!

Please help!

Thanks,
fskilnik.
0
Comment
Question by:fskilnik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39817570
Microsoft should advertise this functionality of SSMS 2008. The feature you are looking for is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.

This is a quick run through to generate Insert statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:

Right click on the database and go to Tasks -> Generate Scripts
Select the tables (or object) that you want to generate the script against.
Go to Set scripting options and click on the Advanced button.
In the General category, go to Type of data to script
There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK.
You will then get the CREATE TABLE statement and all of the INSERT statements for the data straight out of SSMS.
0
 

Author Comment

by:fskilnik
ID: 39817650
Hi, Surendra!

Your answer is simply MARVELLOUS, because applying your suggestion it will save me SEVERAL HOURS, for sure.

(I will be able to copy not only tables, but also functions, store procedures, views, etc!)

Just one thing: after selecting all objects I want to generate the script against, I saw a "scripting environment" with some option buttons choices, but there was no "Advanced" button there, therefore I could not proceed as you said till the end...

Could you please be more specific from the "Go to Set scripting" on?

Thanks A LOT!
scripting-environment.gif
0
 

Author Comment

by:fskilnik
ID: 39817675
Ops... sorry... I guess NOW I understand what you mean. The proper scripting environment is at the file/image attached HERE, right?!

Thanks A LOT again, Surendra. Really!

Regards,
fskilnik.
scripting-environment2.gif
0
 

Author Comment

by:fskilnik
ID: 39820476
(Ok.)
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question