 | |
#181
 
| |||
| |||
|
Re: Copying tables with SQL Server 2005 -
03-17-2008
, 09:09 AM
|
If that is the case, then the Export Data will not work either... It depends on what you mean by "the Export Data". If you mean the Import or Export *Wizard* and also refer to SQL Server Management Studio, then you are correct. However, SQL Server 2000 (Enterprise Manager) has a third option in the Wizard "Copy Objects and Data Between SQL Server Databases". This option *do* include whatever you want (constraints, permissions etc). This option isn't available in the 2005 SSMS *Wizard*. It is however available if you design an SSIS package in Business Intelligence Developer Studio. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Samson J. Loo" <samsonjr2 (AT) cox (DOT) net> wrote in message news:F0C67A1E-C587-4DF7-B5CE-F2CB9593135B (AT) microsoft (DOT) com... If that is the case, then the Export Data will not work either... Samson "Russell Fields" <russellfields (AT) nomail (DOT) com> wrote in message news:u$nwUCEhIHA.4712 (AT) TK2MSFTNGP04 (DOT) phx.gbl... Yes, this gets the table structure and the data. It does not get any indexes, constraints, etc. that it might also be desirable to have. RLF "Samson J. Loo" <samsonjr2 (AT) cox (DOT) net> wrote in message news:49D2C753-6BD4-4182-A07A-E45B04D58E4F (AT) microsoft (DOT) com... You can use the SELECT INTO statement --BEGIN TRANSACTION SELECT * INTO Database2.dbo.Contacts FROM Database1.dbo.Contacts Verify the table SELECT * FROM Database2.dbo.Contacts The run the following if everything is correct -- COMMIT TRANSACTION or Roll it back if that is not what you are looking for. -- ROLLBACK TRANSACTION Samson "Parrot" <Parrot (AT) discussions (DOT) microsoft.com> wrote in message news:E43C78BC-B558-4892-A9E9-D5CCE28FCF40 (AT) microsoft (DOT) com... How do you copy a table from one database to another database using SQL Server Management Studio Express for SQL Server 2005? I was able to do this easily with the Import/Export function in SQL Server 2000 but have failed to find the equivalent in SQL Server Management Studio Express. Do I need other software to do this? Dave |
#182
| ||||||||||||||||||||||
| ||||||||||||||||||||||
|
|
On Tuesday, February 26, 2008 3:30 PM Parro wrote: How do you copy a table from one database to another database using SQL Server Management Studio Express for SQL Server 2005? I was able to do this easily with the Import/Export function in SQL Server 2000 but have failed to find the equivalent in SQL Server Management Studio Express. Do I need other software to do this? Dave |
Quote:
|
On Tuesday, February 26, 2008 4:03 PM Russell Fields wrote: Parrot, Right click on the database in Object Explore, Tasks \ Export Data ... and choose your behaviors. RLF |
Quote:
|
On Tuesday, February 26, 2008 6:18 PM Erland Sommarskog wrote: Parrot (Parrot (AT) discussions (DOT) microsoft.com) writes: The easiest way would undoubtedly be to write an INSERT statement... I don't think the Import/Export tasks that Russell mentioned are available in Mgmt Studio Express. But maybe you could make use of what the Database Publishing Wizard produces. You find it at http://www.microsoft.com/downloads/d...displaylang=en -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
Quote:
|
On Tuesday, February 26, 2008 6:56 PM Parro wrote: Thanks for your replies. It looks like the only way is to use an INSERT command but I can't understand why the export/import function has been removed from the Tasks menu. It seems like 2005 is a step down from 2000. Dave "Erland Sommarskog" wrote: |
Quote:
|
On Tuesday, February 26, 2008 6:57 PM Russell Fields wrote: Whoops. Must read more closely. The Database Publishing Wizard looks like it might be the thing. RLF |
Quote:
|
On Wednesday, February 27, 2008 7:05 AM Tibor Karaszi wrote: SQL Server 2000's Enterprise Manager should be compared to 2005's SQL Server Management Studio. You said you are using SQL Server Management Studio *Express* which is a *free* stripped SSMS. So, you are basically comparing apples with oranges. The Wizard in question *is* available in the pay version of Management Studio. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Parrot" <Parrot (AT) discussions (DOT) microsoft.com> wrote in message news:677B3F75-BC68-4D50-B6B8-5E6BA338D81B (AT) microsoft (DOT) com... |
Quote:
|
On Wednesday, February 27, 2008 11:41 AM Parro wrote: I am not using SQL Server 2005 Express. I have the installation disk for SQL Server 2005 Standard Edition. Does it not have the 2005 SQL Server Management Studio like my SQL Server 2000 installation disk? If not, then that is a downgrade. Dave "Tibor Karaszi" wrote: |
Quote:
|
On Wednesday, February 27, 2008 11:56 AM Russell Fields wrote: Then if you have the full SSMS then go back to my first answer. If you do not see what I described, make sure that you are at least up to SP2 on your SQL Server 2005 tools. RLF |
Quote:
|
On Wednesday, February 27, 2008 2:20 PM Tibor Karaszi wrote: Below is a quote from your first post: Can you see the contradiction between the two statements? :-) It might be so that you didn't install Management Studio from the CD. I.e., you installed SSMS from an Express download. I suggest that you in SSMS do Help, About to verify. I do know that the Import and Export wizards are available in the regular SSMS. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Parrot" <Parrot (AT) discussions (DOT) microsoft.com> wrote in message news:B8652D47-4E4F-4DE4-91F8-D48492163B58 (AT) microsoft (DOT) com... |
Quote:
|
On Wednesday, February 27, 2008 6:00 PM Parro wrote: I am aware of the fact that I am using SQL Server Management Studio Express rather than the regular Studio. The reason for that is that I couldn't find SQL Server Management after installing SQL Server 2005 so I downloaded the Express version. Are you saying that I can install SQL Server Mangement Studio from my SQL Server 2005 installation disk and if so, do I just install the server components part? Dave "Tibor Karaszi" wrote: |
Quote:
|
On Wednesday, February 27, 2008 6:07 PM Erland Sommarskog wrote: Parrot (Parrot (AT) discussions (DOT) microsoft.com) writes: The tools ship with SQL 2005. However, if you have a single CD with SQL Server Standard, you don't have the tools, because they on CD 2. Which you also should have, unless some did it give you because of ignorance. -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
Quote:
|
On Wednesday, February 27, 2008 6:29 PM Parro wrote: Yes, I do have the 2nd CD for SQL Server 2005. I don't know why it didn't install the first time around. To install SQL Server Management Studio which disk should I insert? I tried both disks and each time it acts like it wants to reinstall SQL Server 2005 which I don't want to do and so I cancelled. Can I install Server Management without re-installing SQL Server itself? Dave "Erland Sommarskog" wrote: |
Quote:
|
On Thursday, February 28, 2008 5:33 AM Tibor Karaszi wrote: Yes, pop in the right CD and specify to install the workstation components. It will not remove your existing database engine components. But you first need to remove SSMS Express, since SSMS express cannot co-exist with the "real" SSMS. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Parrot" <Parrot (AT) discussions (DOT) microsoft.com> wrote in message news:BF9E5A30-2F3E-401D-9540-265C49F6EE05 (AT) microsoft (DOT) com... |
Quote:
|
On Thursday, February 28, 2008 5:55 PM Erland Sommarskog wrote: Parrot (Parrot (AT) discussions (DOT) microsoft.com) writes: Yes. I would start with the first CD, and then select workstation components only. If you were to select Database Engine, you would install a second instance. But I have heard of people who have inserted the Tools disk and then started the setup from there. I will have to admit that sounds a little spooky to me, as the feature selection is on the first disk. Then again, I guess you can run that setup from the command line. -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
Quote:
|
On Saturday, March 08, 2008 10:37 AM Samson J. Loo wrote: You can use the SELECT INTO statement --BEGIN TRANSACTION SELECT * INTO Database2.dbo.Contacts FROM Database1.dbo.Contacts Verify the table SELECT * FROM Database2.dbo.Contacts The run the following if everything is correct -- COMMIT TRANSACTION or Roll it back if that is not what you are looking for. -- ROLLBACK TRANSACTION Samson "Parrot" <Parrot (AT) discussions (DOT) microsoft.com> wrote in message news:E43C78BC-B558-4892-A9E9-D5CCE28FCF40 (AT) microsoft (DOT) com... |
Quote:
|
On Wednesday, March 12, 2008 8:57 AM Russell Fields wrote: Yes, this gets the table structure and the data. It does not get any indexes, constraints, etc. that it might also be desirable to have. RLF |
Quote:
|
On Sunday, March 16, 2008 9:21 PM Samson J. Loo wrote: If that is the case, then the Export Data will not work either... |
Quote:
|
On Sunday, March 16, 2008 9:22 PM Samson J. Loo wrote: If that is the case, then the Export Data will not work either... Samson |
Quote:
|
On Monday, March 17, 2008 9:49 AM Russell Fields wrote: Quite right. I was assuming that the table definition already existed. Perhaps not true! - RLF |
Quote:
|
On Monday, March 17, 2008 10:17 AM Tibor Karaszi wrote: It depends on what you mean by "the Export Data". If you mean the Import or Export *Wizard* and also refer to SQL Server Management Studio, then you are correct. However, SQL Server 2000 (Enterprise Manager) has a third option in the Wizard "Copy Objects and Data Between SQL Server Databases". This option *do* include whatever you want (constraints, permissions etc). This option isn't available in the 2005 SSMS *Wizard*. It is however available if you design an SSIS package in Business Intelligence Developer Studio. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Samson J. Loo" <samsonjr2 (AT) cox (DOT) net> wrote in message news:F0C67A1E-C587-4DF7-B5CE-F2CB9593135B (AT) microsoft (DOT) com... |
Quote:
|
On Monday, March 17, 2008 11:09 AM Samso wrote: Yes, you are correct. I was referring to the Export Wizard. -- Regards, Samson "Tibor Karaszi" wrote: |
Quote:
|
Submitted via EggHeadCafe SharePoint Tip / Thought of the Day WebPart http://www.eggheadcafe.com/tutorials...y-webpart.aspx |
#183
| |||||||||||||||||||||||
| |||||||||||||||||||||||
|
|
On Tuesday, February 26, 2008 3:30 PM Parro wrote: How do you copy a table from one database to another database using SQL Server Management Studio Express for SQL Server 2005? I was able to do this easily with the Import/Export function in SQL Server 2000 but have failed to find the equivalent in SQL Server Management Studio Express. Do I need other software to do this? Dave |
Quote:
|
On Tuesday, February 26, 2008 4:03 PM Russell Fields wrote: Parrot, Right click on the database in Object Explore, Tasks \ Export Data ... and choose your behaviors. RLF |
Quote:
|
On Tuesday, February 26, 2008 6:18 PM Erland Sommarskog wrote: Parrot (Parrot (AT) discussions (DOT) microsoft.com) writes: The easiest way would undoubtedly be to write an INSERT statement... I don't think the Import/Export tasks that Russell mentioned are available in Mgmt Studio Express. But maybe you could make use of what the Database Publishing Wizard produces. You find it at http://www.microsoft.com/downloads/d...displaylang=en -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
Quote:
|
On Tuesday, February 26, 2008 6:56 PM Parro wrote: Thanks for your replies. It looks like the only way is to use an INSERT command but I can't understand why the export/import function has been removed from the Tasks menu. It seems like 2005 is a step down from 2000. Dave "Erland Sommarskog" wrote: |
Quote:
|
On Tuesday, February 26, 2008 6:57 PM Russell Fields wrote: Whoops. Must read more closely. The Database Publishing Wizard looks like it might be the thing. RLF |
Quote:
|
On Wednesday, February 27, 2008 7:05 AM Tibor Karaszi wrote: SQL Server 2000's Enterprise Manager should be compared to 2005's SQL Server Management Studio. You said you are using SQL Server Management Studio *Express* which is a *free* stripped SSMS. So, you are basically comparing apples with oranges. The Wizard in question *is* available in the pay version of Management Studio. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Parrot" <Parrot (AT) discussions (DOT) microsoft.com> wrote in message news:677B3F75-BC68-4D50-B6B8-5E6BA338D81B (AT) microsoft (DOT) com... |
Quote:
|
On Wednesday, February 27, 2008 11:41 AM Parro wrote: I am not using SQL Server 2005 Express. I have the installation disk for SQL Server 2005 Standard Edition. Does it not have the 2005 SQL Server Management Studio like my SQL Server 2000 installation disk? If not, then that is a downgrade. Dave "Tibor Karaszi" wrote: |
Quote:
|
On Wednesday, February 27, 2008 11:56 AM Russell Fields wrote: Then if you have the full SSMS then go back to my first answer. If you do not see what I described, make sure that you are at least up to SP2 on your SQL Server 2005 tools. RLF |
Quote:
|
On Wednesday, February 27, 2008 2:20 PM Tibor Karaszi wrote: Below is a quote from your first post: Can you see the contradiction between the two statements? :-) It might be so that you didn't install Management Studio from the CD. I.e., you installed SSMS from an Express download. I suggest that you in SSMS do Help, About to verify. I do know that the Import and Export wizards are available in the regular SSMS. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Parrot" <Parrot (AT) discussions (DOT) microsoft.com> wrote in message news:B8652D47-4E4F-4DE4-91F8-D48492163B58 (AT) microsoft (DOT) com... |
Quote:
|
On Wednesday, February 27, 2008 6:00 PM Parro wrote: I am aware of the fact that I am using SQL Server Management Studio Express rather than the regular Studio. The reason for that is that I couldn't find SQL Server Management after installing SQL Server 2005 so I downloaded the Express version. Are you saying that I can install SQL Server Mangement Studio from my SQL Server 2005 installation disk and if so, do I just install the server components part? Dave "Tibor Karaszi" wrote: |
Quote:
|
On Wednesday, February 27, 2008 6:07 PM Erland Sommarskog wrote: Parrot (Parrot (AT) discussions (DOT) microsoft.com) writes: The tools ship with SQL 2005. However, if you have a single CD with SQL Server Standard, you don't have the tools, because they on CD 2. Which you also should have, unless some did it give you because of ignorance. -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
Quote:
|
On Wednesday, February 27, 2008 6:29 PM Parro wrote: Yes, I do have the 2nd CD for SQL Server 2005. I don't know why it didn't install the first time around. To install SQL Server Management Studio which disk should I insert? I tried both disks and each time it acts like it wants to reinstall SQL Server 2005 which I don't want to do and so I cancelled. Can I install Server Management without re-installing SQL Server itself? Dave "Erland Sommarskog" wrote: |
Quote:
|
On Thursday, February 28, 2008 5:33 AM Tibor Karaszi wrote: Yes, pop in the right CD and specify to install the workstation components. It will not remove your existing database engine components. But you first need to remove SSMS Express, since SSMS express cannot co-exist with the "real" SSMS. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Parrot" <Parrot (AT) discussions (DOT) microsoft.com> wrote in message news:BF9E5A30-2F3E-401D-9540-265C49F6EE05 (AT) microsoft (DOT) com... |
Quote:
|
On Thursday, February 28, 2008 5:55 PM Erland Sommarskog wrote: Parrot (Parrot (AT) discussions (DOT) microsoft.com) writes: Yes. I would start with the first CD, and then select workstation components only. If you were to select Database Engine, you would install a second instance. But I have heard of people who have inserted the Tools disk and then started the setup from there. I will have to admit that sounds a little spooky to me, as the feature selection is on the first disk. Then again, I guess you can run that setup from the command line. -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
Quote:
|
On Saturday, March 08, 2008 10:37 AM Samson J. Loo wrote: You can use the SELECT INTO statement --BEGIN TRANSACTION SELECT * INTO Database2.dbo.Contacts FROM Database1.dbo.Contacts Verify the table SELECT * FROM Database2.dbo.Contacts The run the following if everything is correct -- COMMIT TRANSACTION or Roll it back if that is not what you are looking for. -- ROLLBACK TRANSACTION Samson "Parrot" <Parrot (AT) discussions (DOT) microsoft.com> wrote in message news:E43C78BC-B558-4892-A9E9-D5CCE28FCF40 (AT) microsoft (DOT) com... |
Quote:
|
On Wednesday, March 12, 2008 8:57 AM Russell Fields wrote: Yes, this gets the table structure and the data. It does not get any indexes, constraints, etc. that it might also be desirable to have. RLF |
Quote:
|
On Sunday, March 16, 2008 9:21 PM Samson J. Loo wrote: If that is the case, then the Export Data will not work either... |
Quote:
|
On Sunday, March 16, 2008 9:22 PM Samson J. Loo wrote: If that is the case, then the Export Data will not work either... Samson |
Quote:
|
On Monday, March 17, 2008 9:49 AM Russell Fields wrote: Quite right. I was assuming that the table definition already existed. Perhaps not true! - RLF |
Quote:
|
On Monday, March 17, 2008 10:17 AM Tibor Karaszi wrote: It depends on what you mean by "the Export Data". If you mean the Import or Export *Wizard* and also refer to SQL Server Management Studio, then you are correct. However, SQL Server 2000 (Enterprise Manager) has a third option in the Wizard "Copy Objects and Data Between SQL Server Databases". This option *do* include whatever you want (constraints, permissions etc). This option isn't available in the 2005 SSMS *Wizard*. It is however available if you design an SSIS package in Business Intelligence Developer Studio. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Samson J. Loo" <samsonjr2 (AT) cox (DOT) net> wrote in message news:F0C67A1E-C587-4DF7-B5CE-F2CB9593135B (AT) microsoft (DOT) com... |
Quote:
|
On Monday, March 17, 2008 11:09 AM Samso wrote: Yes, you are correct. I was referring to the Export Wizard. -- Regards, Samson "Tibor Karaszi" wrote: |
Quote:
|
On Monday, February 21, 2011 12:21 PM mary jo lenzing wrote: "It is however available if you design an SSIS package in Business Intelligence Developer Studio." Is BIDS something that I have to install? I am running Sql Server 2008 and wish to copy certain tables with data, keys and indexes. |
Quote:
|
Submitted via EggHeadCafe Excel JSON Storing Data http://www.eggheadcafe.com/tutorials...ring-data.aspx |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.