 |
|
|
|
Next: Best Practice to update SQL Server Database Table..
|
| Author |
Message |
External
Since: Jan 15, 2008
Posts: 7
|
(Msg. 1) Posted: Tue Jan 15, 2008 2:12 pm
Post subject: Copy a table in SQL Server 2005
Archived from groups: microsoft>public>sqlserver>server (more info?)
|
|
|
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/ |
|
| Back to top |
|
 | |
External
Since: Jan 11, 2008
Posts: 63
|
(Msg. 2) Posted: Tue Jan 15, 2008 2:12 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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" wrote in message
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/ |
|
| Back to top |
|
| |
External
Since: Jan 14, 2008
Posts: 9
|
(Msg. 3) Posted: Tue Jan 15, 2008 2:22 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"David" wrote in message
> 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 >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Oct 27, 2003
Posts: 290
|
(Msg. 4) Posted: Tue Jan 15, 2008 2:22 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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" wrote in message
> "David" wrote in message
>
>> 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 >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Jan 14, 2008
Posts: 9
|
(Msg. 5) Posted: Tue Jan 15, 2008 4:13 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Kalen Delaney" wrote in message
> 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. >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Oct 27, 2003
Posts: 290
|
(Msg. 6) Posted: Tue Jan 15, 2008 4:13 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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" wrote in message
> "Kalen Delaney" wrote in message
>
>> 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. >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Aug 24, 2003
Posts: 1744
|
(Msg. 7) Posted: Tue Jan 15, 2008 4:25 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
David
You can script out (with the data) the table and save it on the disk.
"David" wrote in message
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/ >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Aug 24, 2003
Posts: 1744
|
(Msg. 8) Posted: Tue Jan 15, 2008 4:30 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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" wrote in message
> "David" wrote in message
>
>> 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 >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Jan 14, 2008
Posts: 9
|
(Msg. 9) Posted: Tue Jan 15, 2008 4:30 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Uri Dimant" wrote in message
> 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.
Read the original post. They arn't talking about proper backups, they just
want a copy of the table in the same database. >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Jan 14, 2008
Posts: 9
|
(Msg. 10) Posted: Tue Jan 15, 2008 4:45 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Kalen Delaney" wrote in message
>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.
I don't think I've ever dropped a table on a live database accidentally, but
I have run rm -rf * in / on a Solaris box once. Damn su! >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Aug 24, 2003
Posts: 1744
|
(Msg. 11) Posted: Tue Jan 15, 2008 4:52 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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
"Leon Mayne" wrote in message
> "Uri Dimant" wrote in message
>
>> 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.
>
> Read the original post. They arn't talking about proper backups, they just
> want a copy of the table in the same database. >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Jan 14, 2008
Posts: 9
|
(Msg. 12) Posted: Tue Jan 15, 2008 4:52 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Uri Dimant" wrote in message
> 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
"ie: MyTable -> MyTableCOPY = 2 Tables in the same database"
But you could be right. Whatever. >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Jan 15, 2008
Posts: 7
|
(Msg. 13) Posted: Tue Jan 15, 2008 5:35 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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
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.
>
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Oct 27, 2003
Posts: 290
|
(Msg. 14) Posted: Tue Jan 15, 2008 5:35 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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" wrote in message
>
> 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
> 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.
>>
>
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
External
Since: Jan 15, 2008
Posts: 7
|
(Msg. 15) Posted: Tue Jan 15, 2008 5:41 pm
Post subject: Re: Copy a table in SQL Server 2005 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Sorry, this is what I said..
<ie: MyTable -> MyTableCOPY = 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 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
>
>
>
> "Leon Mayne" wrote in message
>
>> "Uri Dimant" wrote in message
>>
>>> 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.
>>
>> Read the original post. They arn't talking about proper backups, they
>> just
>> want a copy of the table in the same database.
>
>
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ >> Stay informed about: Copy a table in SQL Server 2005 |
|
| Back to top |
|
| |
| Related Topics: | SQL Server 2005 full table scan - sp_spaceused results for a given table show 18 Gb data, 50 Gb index, 110 Gb unused. As a SQL Server newbie, my question is: will the 110 Gb of unused be read as part of a full table scan? Here's an exaggerated scenario to clarify what I'm after: ...
Copy SQL 2005 database.. different servers.. network storage - Hi.. I have to SQL 2005 Standard Servers (Server1 and Server2) Everynight I have to program a copy of a DB in Server1 to Server2.. what is the best way to achieve this??.. DB can't go offline.. I tried creating a copy DB wizard package to program it,...
Make a Copy of Table ? - We would like to make a copy of table in a SQL Server 2005 database (We will make a change of it). Is it the only way to do so is by running a SELECT * INTO New_Table FROM Existing_Table ? In SQL Server 2000, it seems that we can make use of DTS but....
Deployment/copy of Db from remote server - I need to copy both schema and data from a remote db server at my ISP. I used to use a Red Gate solution but dont have access to the proggie anymore. I then tried to use the import/export tool for the SQL 2005 server, but view turned into tables and..
Bulk copy rather than SQL Server Import and Export Wizard .. - I am exporting data to roughly 250 Access 2.0 databases from tables in SQL Server 2005. For each table, I open one copy of the wizard and select a specifically named Access 2.0 database, export the table (it has to be edited to the correct format to.... |
|
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
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
