Copying tables with SQL Server 2005

FAQ

Profile

Copying tables with SQL Server 2005
Goto page Previous 1, 2

Database Forums (Home) -> Tools

Next: Database Size Limiting

Author

Message

Russell Fields

External

Since: Feb 21, 2007
Posts: 457

(Msg. 16) Posted: Wed Mar 12, 2008 8:57 am Post subject: Re: Copying tables with SQL Server 2005 [Login to view extended thread Info.] Archived from groups: microsoft>public>sqlserver>tools (more info?)

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" wrote in message > 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" wrote in message > >> 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 > >> Stay informed about: Copying tables with SQL Server 2005

Samson J. Loo

External

Since: Mar 08, 2008
Posts: 3

(Msg. 17) Posted: Sun Mar 16, 2008 6:21 pm Post subject: Re: Copying tables with SQL Server 2005 [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

If that is the case, then the Export Data will not work either... "Russell Fields" wrote in message > 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" wrote in message > >> 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" wrote in message >> >>> 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 >> > > >> Stay informed about: Copying tables with SQL Server 2005

Samson J. Loo

External

Since: Mar 08, 2008
Posts: 3

(Msg. 18) Posted: Sun Mar 16, 2008 6:22 pm Post subject: Re: Copying tables with SQL Server 2005 [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

If that is the case, then the Export Data will not work either... Samson "Russell Fields" wrote in message > 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" wrote in message > >> 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" wrote in message >> >>> 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 >> > > >> Stay informed about: Copying tables with SQL Server 2005

Russell Fields

External

Since: Feb 21, 2007
Posts: 457

(Msg. 19) Posted: Mon Mar 17, 2008 9:49 am Post subject: Re: Copying tables with SQL Server 2005 [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Quite right. I was assuming that the table definition already existed. Perhaps not true! - RLF "Samson J. Loo" wrote in message > If that is the case, then the Export Data will not work either... > > Samson > > > "Russell Fields" wrote in message > >> 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" wrote in message >> >>> 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" wrote in message >>> >>>> 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 >>> >> >> > >> Stay informed about: Copying tables with SQL Server 2005

Tibor Karaszi

External

Since: Jan 29, 2004
Posts: 891

(Msg. 20) Posted: Mon Mar 17, 2008 3:17 pm Post subject: Re: Copying tables with SQL Server 2005 [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

> 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" wrote in message > If that is the case, then the Export Data will not work either... > > Samson > > > "Russell Fields" wrote in message > >> 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" wrote in message >> >>> 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" wrote in message >>> >>>> 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 >>> >> >> > >> Stay informed about: Copying tables with SQL Server 2005

Samson

External

Since: Jul 20, 2006
Posts: 4

(Msg. 21) Posted: Mon Mar 17, 2008 3:17 pm Post subject: Re: Copying tables with SQL Server 2005 [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Yes, you are correct. I was referring to the Export Wizard. -- Regards, Samson "Tibor Karaszi" wrote: > > 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" wrote in message > > > If that is the case, then the Export Data will not work either... > > > > Samson > > > > > > "Russell Fields" wrote in message > > > >> 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" wrote in message > >> > >>> 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" wrote in message > >>> > >>>> 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 > >>> > >> > >> > > > > > >> Stay informed about: Copying tables with SQL Server 2005

mary jo lenzing

External

Since: Feb 21, 2011
Posts: 2

mary jo lenzing

External

Since: Feb 21, 2011
Posts: 2

Display posts from previous:

Related Topics:

SQL Server 2005 - Server side trace script - Hi, I have been beating my head against the wall on this one, and am hoping someone can point out what I am doing wrong here. I am trying to set up a trace (using script) that will tracks all the "RPC:Completed" events and give me some of t...

Copying database between servers - I need to move a SQL 2005 database between 2 servers. But the 2 servers have different path structure due to language difference. One server has the databse installed under "c:\Program Files ..." and the other under "C:\Programmer...&quo...

Copying a database with the "Copy Database Wizard" - I am using the Copy Database wizard to copy an SQL 2000 database to a 2005 database on another server. When I get to the "Schedule the Package" wizard and click "Next>" I get an error that says "Proxy account has to be selec...

debugging in sql server 2005 - Can you 'step' through code in sql server 2005 express like you can in visual studio.net 2005? I want to be able to debug code on my own workstation since my company does not have a 'test' sql server for me to use. Does the developer's version of sql....

Manage SQL Server 2005 while keeping SQL Server 2000 - I've got SQL Server 2000 running on my PC, and I want to continue doing so. But now I also need to manage a remote SQL Server 2005 database. I think that I need to install SQL Server Management Studio to do so. (Please correct me if that's wrong.) Can...

	Database Forums (Home) -> Tools	All times are: Pacific Time (US & Canada) Goto page Previous 1, 2
Page 2 of 2

You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum