Solved

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

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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