Copy a table in SQL Server 2005

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/"David" <someone@.someisp.com> wrote in message
news:op.t4y5ugvcrasovn@.pavilion...
> 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|||David
You can script out (with the data) the table and save it on the disk.
"David" <someone@.someisp.com> wrote in message
news:op.t4y5ugvcrasovn@.pavilion...
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/|||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@.rmv_me.mvps.org> wrote in message
news:ubieII4VIHA.1164@.TK2MSFTNGP02.phx.gbl...
> "David" <someone@.someisp.com> wrote in message
> news:op.t4y5ugvcrasovn@.pavilion...
>> 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|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u3D5IM4VIHA.4532@.TK2MSFTNGP02.phx.gbl...
> 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.|||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" <leon@.rmv_me.mvps.org> wrote in message
news:22CAA140-7D18-44BE-A9C4-A997937E5CF3@.microsoft.com...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u3D5IM4VIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> 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.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23OsMlY4VIHA.2268@.TK2MSFTNGP02.phx.gbl...
> 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.|||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@.rmv_me.mvps.org> wrote in message
news:ubieII4VIHA.1164@.TK2MSFTNGP02.phx.gbl...
> "David" <someone@.someisp.com> wrote in message
> news:op.t4y5ugvcrasovn@.pavilion...
>> 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|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u1DrLC5VIHA.1208@.TK2MSFTNGP05.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.|||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@.rmv_me.mvps.org> wrote in message
news:C3367EE5-E728-46E5-8C83-3782ADF0141C@.microsoft.com...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u1DrLC5VIHA.1208@.TK2MSFTNGP05.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.|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:Oop3mV5VIHA.2304@.TK2MSFTNGP06.phx.gbl...
>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!|||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@.public_newsgroups.com> 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/|||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@.iscar.co.il> wrote:=
> Leon
> The OP does not say that he wants the copy in the same db. He just sai=d =
> that
> both tables are located in the same db
>
> "Leon Mayne" <leon@.rmv_me.mvps.org> wrote in message
> news:22CAA140-7D18-44BE-A9C4-A997937E5CF3@.microsoft.com...
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:u3D5IM4VIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> 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 f=ile
>> 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/|||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@.someisp.com> wrote in message
news:op.t4zfabm9rasovn@.pavilion...
> 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@.public_newsgroups.com> 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/|||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@.someisp.com> wrote in message
news:op.t4y5ugvcrasovn@.pavilion...
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/|||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@.FakeEmail.Not>
wrote:
> ADP file
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Copy a table from one DB to antoher in SQL Server 2005

Hi all,

i am completely new to SQL Server. So how can I do that? With
Integration Services?

Thanks for your help
DanielDani (danielsanberger@.googlemail.com) writes:
> i am completely new to SQL Server. So how can I do that? With
> Integration Services?

INSERT db1.dbo.tbl (col1, col2, col3, ...)
SELCET col1, col2, col3, ...
FROM db2.dbo.tbl
WHERE ...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Copy a table from one DB to another DB

HI All,

i have 2 databases x and y

a table in x named as table1

i am looking to copy 'table1' from database x to database y say named 'table2'

Database y doesnt have a table1

Version: SQL Server 2000

How to do this on Query Analyzer and/or Enterprise Manager?

Thanksuse this if table 2 does not exist in y.

select column1,column2
into y.dbo.table2
from x.dbo.table1

if table 2 does exist, use this...

INSERT INTO y.dbo.table2(column1,column2)
SELECT column1,column2
FROM x.dbo.table1|||Generate create table script and run it on y database then use import / export utility to transfer data.

SQL queries posted by Thrasymachus will also create new table and transfer data but if you need table dependent objects like triggers, constraints or indexes on your second database also, this way is useless.|||also, if you are copying millions of rows, better to use bcp with the -b flag.

otherwise your tlog will bloat.

copy a table and all its indexes, constraints, etc

How do I copy a table and all of its indexes, constraints, etc. I need to
do this in a routine where I fear possible errors and would like to restore
these tables and constraints if the process fails.
Tx for any help.
Bernie YaegerYou can script the table out and bcp out all its data. You then on an as
needed basis reapply the script and bcp the data back in.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:POyrb.8933$si2.4437406@.news4.srv.hcvlny.cv.net...
> How do I copy a table and all of its indexes, constraints, etc. I need to
> do this in a routine where I fear possible errors and would like to
restore
> these tables and constraints if the process fails.
> Tx for any help.
> Bernie Yaeger
>|||Hi Allan,
Very good idea! Tx so much.
Bernie
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:OZh4AQxpDHA.964@.TK2MSFTNGP10.phx.gbl...
> You can script the table out and bcp out all its data. You then on an as
> needed basis reapply the script and bcp the data back in.
>
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:POyrb.8933$si2.4437406@.news4.srv.hcvlny.cv.net...
> > How do I copy a table and all of its indexes, constraints, etc. I need
to
> > do this in a routine where I fear possible errors and would like to
> restore
> > these tables and constraints if the process fails.
> >
> > Tx for any help.
> >
> > Bernie Yaeger
> >
> >
>

Copy a Table

In MS Access, if I want to copy a table, I can just highlight it, hit ctrl-c and ctrl-v, and VOILA! I now have a "copy of MyTable" pasted, with all data duplicated.

Um. How do I do the same thing in SQL Server 2005? Can this be done with Server Management Studio? I don't necessarily want to copy relationships to other tables, I just want a stand-alone copy of the table, with a new name, inside the same database.

(EDIT): I just noticed I can achieve this outcome with:

SELECT * INTO TestIT FROM TestCases

...and while that is simple enough to get the job done, I kinda still wonder if there is a GUI-oriented means to accomplish this (like Access does), without writing bits of SQL as above.

the closest function of SQL Mgmt. Studio to what you are describing is the import/export wizard (right-click on a a database in object explorer and select tasks/[import or export]. But I dont think it will allow you to specify the same source and destination (you may want "play" with the wizard a bit to ensure you cannot get it to work for you). Outside of that...NO. A related features is that of scripting data, so you could right-click a table and select Script Table As/"Generate Data", I believe SQL tools will have this feature in the future, but I would think other third-party tools already do this, not sure though if this can be done via the SMO model yet or not...

Copy a table

Can I copy a table from on DB to another - I only want to copy the column
names not the Data
What tool or how should I do this
Thanks
ChrisWhy don't you generate the CREATE TABLE script, and then create the empty
table?
There are lazy ways to do this, e.g. SELECT * INTO newdb.dbo.tablename FROM
OldTable WHERE 1 = 2; but this leaves out many other important things, like
indexes and constraints.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:BCD66035-F4BB-46B4-8FCC-4A4D12828918@.microsoft.com...
> Can I copy a table from on DB to another - I only want to copy the column
> names not the Data
>
> What tool or how should I do this
> Thanks
> Chris

Copy a table

Hi,
How do I copy a table in my SQL Server 2000?
Actually, I want to copy MyTable to MyTable2 in MyDataBase, and the records
inside too.
Any help will be appreciated.
JasonCREATE TABLE MyTable2 (...) ;
INSERT INTO MyTable2 (...)
SELECT ...
FROM MyTable ;
Or:
SELECT ...
INTO MyTable2
FROM MyTable ;
In this second case, the constraints won't be copied so you'll still have to
create them yourself.
This is development/admin of course. It isn't normally good practice to
create tables at runtime.
--
David Portas
SQL Server MVP
--
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:eqRs4HT1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I copy a table in my SQL Server 2000?
> Actually, I want to copy MyTable to MyTable2 in MyDataBase, and the
> records inside too.
> Any help will be appreciated.
>
> Jason
>|||Hi Jason
David gave you the best technique to copy data from one table to another.
The alternative way of doing this is using DTS.
DTS implements the same technique that David used
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David Portas" wrote:
> CREATE TABLE MyTable2 (...) ;
> INSERT INTO MyTable2 (...)
> SELECT ...
> FROM MyTable ;
> Or:
> SELECT ...
> INTO MyTable2
> FROM MyTable ;
> In this second case, the constraints won't be copied so you'll still have to
> create them yourself.
> This is development/admin of course. It isn't normally good practice to
> create tables at runtime.
> --
> David Portas
> SQL Server MVP
> --
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:eqRs4HT1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > How do I copy a table in my SQL Server 2000?
> > Actually, I want to copy MyTable to MyTable2 in MyDataBase, and the
> > records inside too.
> > Any help will be appreciated.
> >
> >
> > Jason
> >
>
>|||Thanks David!
The MicroSoft has the "Copy" and "Paste" in many occasions, why don't they
have the "Copy" "Paste" in the SQL Server? Will this be too stupid or I am
too lazy?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ¼¶¼g©ó¶l¥ó·s»D:2r6dnX2bm-yyuMreRVnyhA@.giganews.com...
> CREATE TABLE MyTable2 (...) ;
> INSERT INTO MyTable2 (...)
> SELECT ...
> FROM MyTable ;
> Or:
> SELECT ...
> INTO MyTable2
> FROM MyTable ;
> In this second case, the constraints won't be copied so you'll still have
> to create them yourself.
> This is development/admin of course. It isn't normally good practice to
> create tables at runtime.
> --
> David Portas
> SQL Server MVP
> --
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:eqRs4HT1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> How do I copy a table in my SQL Server 2000?
>> Actually, I want to copy MyTable to MyTable2 in MyDataBase, and the
>> records inside too.
>> Any help will be appreciated.
>>
>> Jason
>|||Hi,
There are several reason that microsoft has not provided the facility
of copy and paste because each table has a unique and containe unique
record in terms of name and indexes constraints and many more.
Hope this much is sufficient for u to understand.
from
Doller
Jason Huang wrote:
> Thanks David!
> The MicroSoft has the "Copy" and "Paste" in many occasions, why don't they
> have the "Copy" "Paste" in the SQL Server? Will this be too stupid or I =am
> too lazy?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> =BC=B6=BCg=A9=F3==B6l=A5=F3=B7s=BBD:2r6dnX2bm-yyuMreRVnyhA@.giganews.com...
> > CREATE TABLE MyTable2 (...) ;
> >
> > INSERT INTO MyTable2 (...)
> > SELECT ...
> > FROM MyTable ;
> >
> > Or:
> >
> > SELECT ...
> > INTO MyTable2
> > FROM MyTable ;
> >
> > In this second case, the constraints won't be copied so you'll still ha=ve
> > to create them yourself.
> >
> > This is development/admin of course. It isn't normally good practice to
> > create tables at runtime.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> > "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> > news:eqRs4HT1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> >> Hi,
> >>
> >> How do I copy a table in my SQL Server 2000?
> >> Actually, I want to copy MyTable to MyTable2 in MyDataBase, and the
> >> records inside too.
> >> Any help will be appreciated.
> >>
> >>
> >> Jason
> >>
> >
> >