Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » can I get the cuerry of an existing...
Add to briefcase

can I get the cuerry of an existing table...??? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Ahmed_07
Posted Wednesday, October 31, 2012 2:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 05, 2012 12:45 AM
Points: 11, Visits: 16

I wanted to copy a table and its data to another db in another pc.... I was wondering wether there is a way to generate a query that holds all the data of original table so I could just execute it to the other 2nd table and have exact data.

thank you
Post #1379194
anthony.green
Posted Wednesday, October 31, 2012 2:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833
A number of ways to do this.

Create a linked server on 1 of the machines then use a cross server query to pull out the data, something like 
select * into destinationdb.dbo.tablename from linkedservername.sourcedb.dbo.tablename

Use the import export data wizard to copy the data from one place to another
Create a custom SSIS package which does what you need
Get a copy of SSMSTools Pack, and use the generate data scripts functionality to script out the data.
BCP Out and then BCP In


Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
Post #1379199
mwagh
Posted Wednesday, October 31, 2012 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:51 AM
Points: 5, Visits: 31
IF u don't wish to connect the two databases BCP in/out will be a good solution for you.Perform a BCP out operation on the source and get the data on a file.Then insert the data in the file onto a database using BCP in command.
Post #1379206
Jason-299789
Posted Wednesday, October 31, 2012 3:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:57 AM
Points: 838, Visits: 2,200
Another option is to use SSIS or the Import wizard to pull the data over from the other server.



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1379214
Ahmed_07
Posted Wednesday, October 31, 2012 4:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 05, 2012 12:45 AM
Points: 11, Visits: 16
I could have done this... but my B server is a remote server, that don't accespt IMPORT db...

Create a linked server on 1 of the machines then use a cross server query to pull out the data, something like 
select * into destinationdb.dbo.tablename from linkedservername.sourcedb.dbo.tablename

thankx will try the other options too
Post #1379233
Sean Lange
Posted Wednesday, October 31, 2012 7:55 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 8,980, Visits: 8,540
You don't need SSMSToolsPack to generate a script for the data. You can right click the database Taks -> Generate Scripts to bring up the Script wizard. On the Set Scripting Options section click the Advanced button. The last option in the first section of options (General) is "Types of data to script". By default this is set to Schema only. You can change that to Data only or Schema and data. If you choose the last one it will generate your create object script and inserts for all of the data.

--EDIT--

Of course SSMS Tools pack is a pretty darn handy extension though.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1379320
Ahmed_07
Posted Monday, November 05, 2012 12:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 05, 2012 12:45 AM
Points: 11, Visits: 16

Indeed I don't need SSMSToolsPack


this was a tremendous help thank you so much and works JUST FINE




Sean Lange (10/31/2012)
You don't need SSMSToolsPack to generate a script for the data. You can right click the database Taks -> Generate Scripts to bring up the Script wizard. On the Set Scripting Options section click the Advanced button. The last option in the first section of options (General) is "Types of data to script". By default this is set to Schema only. You can change that to Data only or Schema and data. If you choose the last one it will generate your create object script and inserts for all of the data.

--EDIT--

Of course SSMS Tools pack is a pretty darn handy extension though.
Post #1380901
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.