Mark Forums Read

Copy a table in SQL Server 2005

Go Back

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

Reply

Page 5 of 10

5

#41

Old

Kalen Delaney

Posts: n/a

Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:06 AM

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

Quote:

"David" <someone (AT) someisp (DOT) com> wrote in message
news

p.t4y5ugvcrasovn (AT) pavilion (DOT) ..
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?

Just use
select * into newtable from oldtable

Reply With Quote

#42

Old

Kalen Delaney

Posts: n/a

Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:06 AM

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

Quote:

"David" <someone (AT) someisp (DOT) com> wrote in message
news

p.t4y5ugvcrasovn (AT) pavilion (DOT) ..
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?

Just use
select * into newtable from oldtable

Reply With Quote

#43

Old

Kalen Delaney

Posts: n/a

Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:06 AM

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

Quote:

"David" <someone (AT) someisp (DOT) com> wrote in message
news

p.t4y5ugvcrasovn (AT) pavilion (DOT) ..
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?

Just use
select * into newtable from oldtable

Reply With Quote

#44

Old

Leon Mayne

Posts: n/a

Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:13 AM

"Kalen Delaney" <replies (AT) public_newsgroups (DOT) com> wrote

Quote:

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.

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.

Reply With Quote

#45

Old

Leon Mayne

Posts: n/a

Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:13 AM

"Kalen Delaney" <replies (AT) public_newsgroups (DOT) com> wrote

Quote:

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.

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.

Reply With Quote

#46

Old

Leon Mayne

Posts: n/a

Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:13 AM

"Kalen Delaney" <replies (AT) public_newsgroups (DOT) com> wrote

Quote:

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.

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.

Reply With Quote

#47

Old

Leon Mayne

Posts: n/a

Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:13 AM

"Kalen Delaney" <replies (AT) public_newsgroups (DOT) com> wrote

Quote:

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.

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.

Reply With Quote

#48

Old

Leon Mayne

Posts: n/a

Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:13 AM

"Kalen Delaney" <replies (AT) public_newsgroups (DOT) com> wrote

Quote:

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.

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.

Reply With Quote

#49

Old

Leon Mayne

Posts: n/a

Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:13 AM

"Kalen Delaney" <replies (AT) public_newsgroups (DOT) com> wrote

Quote:

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.

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.

Reply With Quote

#50

Old

Kalen Delaney

Posts: n/a

Re: Copy a table in SQL Server 2005 - 01-15-2008 , 10:40 AM

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

Quote:

"Kalen Delaney" <replies (AT) public_newsgroups (DOT) com> wrote in message
news:u1DrLC5VIHA.1208 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
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.

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.

Reply With Quote

Reply

Page 5 of 10

5

« Previous Thread | Next Thread »

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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 - 2012, Jelsoft Enterprises Ltd.