Solved

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

Posted on 2014-01-29
4
1,131 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
  • 3
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 33
SQL Server - Slabs 9 37
SQL Server merge records in one table 2 11
conditional join based on column 4 0
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now