Solved

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

Posted on 2014-01-29
4
1,155 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

837 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