dbTalk Databases Forums  

Copy a table in SQL Server 2005

microsoft.public.sqlserver.server microsoft.public.sqlserver.server


Discuss Copy a table in SQL Server 2005 in the microsoft.public.sqlserver.server forum.

Reply
 
Thread Tools Display Modes
  #91  
Old   
David
 
Posts: n/a

Default Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:44 PM







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:

Quote:
ADP file


--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/




Reply With Quote
  #92  
Old   
jignesh kaila
 
Posts: n/a

Default Re: Copy a table in SQL Server 2005 - 06-10-2011 , 09:04 AM






Hello all



you just need to right click to table and past to destination path nothing else


ThanQ

Quote:
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
newsp.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
newsp.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
newsp.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/

Reply With Quote
  #93  
Old   
jignesh kaila
 
Posts: n/a

Default Re: Copy a table in SQL Server 2005 - 06-10-2011 , 09:07 AM



it is very simple task

Quote:
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
newsp.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
newsp.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
newsp.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

Reply With Quote
  #94  
Old   
Aaron Kempf
 
Posts: n/a

Default Re: Copy a table in SQL Server 2005 - 11-09-2011 , 06:29 PM



I use Access Data Projects to right-click copy table.. it seems to work quite well.

it also works for rows, columns in design view- or the data itself.

it also works right-click copy sprocs..

I'm amazed that SSMS doesn't have this same functionality.

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.