 | |
Copy a table in SQL Server 2005
microsoft.public.sqlserver.server microsoft.public.sqlserver.server
 |
| | Thread Tools | Display Modes |
#91
 
| |||
| |||
|
Re: Copy a table in SQL Server 2005 -
01-15-2008
, 10:44 PM
|
ADP file |
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
#92
| ||||||||||||||||
| ||||||||||||||||
|
|
On Tuesday, January 15, 2008 9:12 AM David wrote: Hello all I want to make a copy of a table in SQL Server 2005, a back-up of my tab= le = if you will. I don't see any way of doing this, I am I right in assuming= = that one can't copy a table in an SQL Sever 2005 database? ie: MyTable -> MyTableCOPY =3D 2 Tables in the same database Thank you for your time. Regards David -- = Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Tuesday, January 15, 2008 9:22 AM Leon Mayne wrote: Just use select * into newtable from oldtable |
Quote:
|
On Tuesday, January 15, 2008 9:25 AM Uri Dimant wrote: David You can script out (with the data) the table and save it on the disk. "David" <someone (AT) someisp (DOT) com> wrote in message news  p.t4y5ugvcrasovn (AT) pavilion (DOT) ..Hello all I want to make a copy of a table in SQL Server 2005, a back-up of my table if you will. I don't see any way of doing this, I am I right in assuming that one can't copy a table in an SQL Sever 2005 database? ie: MyTable -> MyTableCOPY = 2 Tables in the same database Thank you for your time. Regards David -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Tuesday, January 15, 2008 9:30 AM Uri Dimant wrote: Leon If the database got corrupted you are still not be able to get back the table. I think the way to save the datai is having proper backup of the database or if the database is big, just script out into the text file the table and keep it on the disk. "Leon Mayne" <leon (AT) rmv_me (DOT) mvps.org> wrote in message news:ubieII4VIHA.1164 (AT) TK2MSFTNGP02 (DOT) phx.gbl... |
Quote:
|
On Tuesday, January 15, 2008 9:37 AM Leon Mayne wrote: Read the original post. They arn't talking about proper backups, they just want a copy of the table in the same database. |
Quote:
|
On Tuesday, January 15, 2008 9:52 AM Uri Dimant wrote: Leon The OP does not say that he wants the copy in the same db. He just said that both tables are located in the same db |
Quote:
|
On Tuesday, January 15, 2008 10:28 AM Leon Mayne wrote: "ie: MyTable -> MyTableCOPY = 2 Tables in the same database" But you could be right. Whatever. |
Quote:
|
On Tuesday, January 15, 2008 11:06 AM Kalen Delaney wrote: Hi David and Leon This simple SELECT INTO will copy the table structure and all the data to a new table. It will not copy indexes, constraints, or triggers. -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://blog.kalendelaney.com "Leon Mayne" <leon (AT) rmv_me (DOT) mvps.org> wrote in message news:ubieII4VIHA.1164 (AT) TK2MSFTNGP02 (DOT) phx.gbl... |
Quote:
|
On Tuesday, January 15, 2008 11:13 AM Leon Mayne wrote: Correct. These schema objects would have to be copied manually, but if you just want a quick backup copy of a table to play around with the data then select into works well enough. |
Quote:
|
On Tuesday, January 15, 2008 11:40 AM Kalen Delaney wrote: I agree, the data is the most important thing and the indexes, etc can always be recreated. The OP should make note of what triggers, indexes and constraints there were so that he will know to recreate them if he has to revert to the copied table because it get accidentally dropped. (Of course, it's all hypothetical, right, really nobody accidentally drops a table. ;-) ) -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://blog.kalendelaney.com "Leon Mayne" <leon (AT) rmv_me (DOT) mvps.org> wrote in message news:C3367EE5-E728-46E5-8C83-3782ADF0141C (AT) microsoft (DOT) com... |
Quote:
|
On Tuesday, January 15, 2008 11:45 AM Leon Mayne wrote: I do not think I have ever dropped a table on a live database accidentally, but I have run rm -rf * in / on a Solaris box once. Damn su! |
Quote:
|
On Tuesday, January 15, 2008 12:35 PM David wrote: Hello Kalen Thanks for the reply, that would be a big problem..especially the indexes. I had thought of doing a select into a new table, but the indexes would be lost. Regards David On Tue, 15 Jan 2008 16:06:03 -0000, Kalen Delaney replies (AT) public_newsgroups (DOT) com> wrote: -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Tuesday, January 15, 2008 12:41 PM David wrote: Sorry, this is what I said.. ie: MyTable -> MyTableCOPY =3D 2 Tables in the same database That is, I want a copy of my table in the same database. Regards David On Tue, 15 Jan 2008 14:52:51 -0000, Uri Dimant <urid (AT) iscar (DOT) co.il> wrote:= d = = = ile = -- = Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Tuesday, January 15, 2008 2:48 PM Kalen Delaney wrote: You can always script the indexes from the original table and build them on the new table by just slight modification of the scripts. It's not a one step operation to copy the table and all constraints and indexes however. -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://blog.kalendelaney.com "David" <someone (AT) someisp (DOT) com> wrote in message news p.t4zfabm9rasovn (AT) pavilion (DOT) .. |
Quote:
|
On Tuesday, January 15, 2008 3:33 PM Norman Yuan wrote: The simplest way to do it by just standard copy/paste would be to use MS Access ADP file to connect to targeting database. Then you can simply do the standard Windows copy/paste (tables, Views, SPs, UDF...). Actually, the copy/paste is not limited within the same database/same SQL Server (when copy/paste between different SQL Server/database, you need to open two ADP sessions, though). Only this very convenient feature keeps me using ADP for most of my routine SQL Server development/manage tasks, rather than Enterprise Manager or SSMS. I just wondering, why SQL Server team does not learn this from Access team and make this feature avaialble in SSMS. "David" <someone (AT) someisp (DOT) com> wrote in message news p.t4y5ugvcrasovn (AT) pavilion (DOT) ..Hello all I want to make a copy of a table in SQL Server 2005, a back-up of my table if you will. I don't see any way of doing this, I am I right in assuming that one can't copy a table in an SQL Sever 2005 database? ie: MyTable -> MyTableCOPY = 2 Tables in the same database Thank you for your time. Regards David -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Tuesday, January 15, 2008 11:44 PM David wrote: Thank you Norman...err could you explain your method in simple terms or via a step through please? I can get my SQL Server tables to list in ADP, but how exactly do I copy the table..seems that all I am doing in ADP is linking the SQL Server table to the page. Thanks for your time Regards David On Tue, 15 Jan 2008 20:33:11 -0000, Norman Yuan <FakeName (AT) FakeEmail (DOT) Not wrote: -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#93
| |||||||||||||||||
| |||||||||||||||||
|
|
On Tuesday, January 15, 2008 9:12 AM David wrote: Hello all I want to make a copy of a table in SQL Server 2005, a back-up of my tab= le = if you will. I don't see any way of doing this, I am I right in assuming= = that one can't copy a table in an SQL Sever 2005 database? ie: MyTable -> MyTableCOPY =3D 2 Tables in the same database Thank you for your time. Regards David -- = Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Tuesday, January 15, 2008 9:22 AM Leon Mayne wrote: Just use select * into newtable from oldtable |
Quote:
|
On Tuesday, January 15, 2008 9:25 AM Uri Dimant wrote: David You can script out (with the data) the table and save it on the disk. "David" <someone (AT) someisp (DOT) com> wrote in message news p.t4y5ugvcrasovn (AT) pavilion (DOT) ..Hello all I want to make a copy of a table in SQL Server 2005, a back-up of my table if you will. I don't see any way of doing this, I am I right in assuming that one can't copy a table in an SQL Sever 2005 database? ie: MyTable -> MyTableCOPY = 2 Tables in the same database Thank you for your time. Regards David -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Tuesday, January 15, 2008 9:30 AM Uri Dimant wrote: Leon If the database got corrupted you are still not be able to get back the table. I think the way to save the datai is having proper backup of the database or if the database is big, just script out into the text file the table and keep it on the disk. "Leon Mayne" <leon (AT) rmv_me (DOT) mvps.org> wrote in message news:ubieII4VIHA.1164 (AT) TK2MSFTNGP02 (DOT) phx.gbl... |
Quote:
|
On Tuesday, January 15, 2008 9:37 AM Leon Mayne wrote: Read the original post. They arn't talking about proper backups, they just want a copy of the table in the same database. |
Quote:
|
On Tuesday, January 15, 2008 9:52 AM Uri Dimant wrote: Leon The OP does not say that he wants the copy in the same db. He just said that both tables are located in the same db |
Quote:
|
On Tuesday, January 15, 2008 10:28 AM Leon Mayne wrote: "ie: MyTable -> MyTableCOPY = 2 Tables in the same database" But you could be right. Whatever. |
Quote:
|
On Tuesday, January 15, 2008 11:06 AM Kalen Delaney wrote: Hi David and Leon This simple SELECT INTO will copy the table structure and all the data to a new table. It will not copy indexes, constraints, or triggers. -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://blog.kalendelaney.com "Leon Mayne" <leon (AT) rmv_me (DOT) mvps.org> wrote in message news:ubieII4VIHA.1164 (AT) TK2MSFTNGP02 (DOT) phx.gbl... |
Quote:
|
On Tuesday, January 15, 2008 11:13 AM Leon Mayne wrote: Correct. These schema objects would have to be copied manually, but if you just want a quick backup copy of a table to play around with the data then select into works well enough. |
Quote:
|
On Tuesday, January 15, 2008 11:40 AM Kalen Delaney wrote: I agree, the data is the most important thing and the indexes, etc can always be recreated. The OP should make note of what triggers, indexes and constraints there were so that he will know to recreate them if he has to revert to the copied table because it get accidentally dropped. (Of course, it's all hypothetical, right, really nobody accidentally drops a table. ;-) ) -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://blog.kalendelaney.com "Leon Mayne" <leon (AT) rmv_me (DOT) mvps.org> wrote in message news:C3367EE5-E728-46E5-8C83-3782ADF0141C (AT) microsoft (DOT) com... |
Quote:
|
On Tuesday, January 15, 2008 11:45 AM Leon Mayne wrote: I do not think I have ever dropped a table on a live database accidentally, but I have run rm -rf * in / on a Solaris box once. Damn su! |
Quote:
|
On Tuesday, January 15, 2008 12:35 PM David wrote: Hello Kalen Thanks for the reply, that would be a big problem..especially the indexes. I had thought of doing a select into a new table, but the indexes would be lost. Regards David On Tue, 15 Jan 2008 16:06:03 -0000, Kalen Delaney replies (AT) public_newsgroups (DOT) com> wrote: -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Tuesday, January 15, 2008 12:41 PM David wrote: Sorry, this is what I said.. ie: MyTable -> MyTableCOPY =3D 2 Tables in the same database That is, I want a copy of my table in the same database. Regards David On Tue, 15 Jan 2008 14:52:51 -0000, Uri Dimant <urid (AT) iscar (DOT) co.il> wrote:= d = = = ile = -- = Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Tuesday, January 15, 2008 2:48 PM Kalen Delaney wrote: You can always script the indexes from the original table and build them on the new table by just slight modification of the scripts. It's not a one step operation to copy the table and all constraints and indexes however. -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://blog.kalendelaney.com "David" <someone (AT) someisp (DOT) com> wrote in message news p.t4zfabm9rasovn (AT) pavilion (DOT) .. |
Quote:
|
On Tuesday, January 15, 2008 3:33 PM Norman Yuan wrote: The simplest way to do it by just standard copy/paste would be to use MS Access ADP file to connect to targeting database. Then you can simply do the standard Windows copy/paste (tables, Views, SPs, UDF...). Actually, the copy/paste is not limited within the same database/same SQL Server (when copy/paste between different SQL Server/database, you need to open two ADP sessions, though). Only this very convenient feature keeps me using ADP for most of my routine SQL Server development/manage tasks, rather than Enterprise Manager or SSMS. I just wondering, why SQL Server team does not learn this from Access team and make this feature avaialble in SSMS. "David" <someone (AT) someisp (DOT) com> wrote in message news p.t4y5ugvcrasovn (AT) pavilion (DOT) ..Hello all I want to make a copy of a table in SQL Server 2005, a back-up of my table if you will. I don't see any way of doing this, I am I right in assuming that one can't copy a table in an SQL Sever 2005 database? ie: MyTable -> MyTableCOPY = 2 Tables in the same database Thank you for your time. Regards David -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Tuesday, January 15, 2008 11:44 PM David wrote: Thank you Norman...err could you explain your method in simple terms or via a step through please? I can get my SQL Server tables to list in ADP, but how exactly do I copy the table..seems that all I am doing in ADP is linking the SQL Server table to the page. Thanks for your time Regards David On Tue, 15 Jan 2008 20:33:11 -0000, Norman Yuan <FakeName (AT) FakeEmail (DOT) Not wrote: -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Quote:
|
On Friday, June 10, 2011 10:04 AM jignesh kaila wrote: Hello all you just need to right click to table and past to destination path nothing else ThanQ |
#94
| |||
| |||
|
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.