copy of existing table and data

My query is very simple, I am new to SQL.
I want to create copy of existing table and data.
Pls suggest a command !
Thanks in advance
SanjayEverything in the database? If so, I suggest backup and restore. If not, che
ck out some of the tools
at http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.
gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Thanks for the prompt response.
I think, i have failed to pass on my message.
I want to create a new table using existing table with its structure and
records.
Sanjay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYKfdypeHHA.4300@.TK2MSFTNGP02.phx.gbl...
> Everything in the database? If so, I suggest backup and restore. If not,
> check out some of the tools at
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>|||Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:

> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need t
o
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
>

copy of existing table and data

My query is very simple, I am new to SQL.
I want to create copy of existing table and data.
Pls suggest a command !
Thanks in advance
SanjayEverything in the database? If so, I suggest backup and restore. If not, check out some of the tools
at http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Thanks for the prompt response.
I think, i have failed to pass on my message.
I want to create a new table using existing table with its structure and
records.
Sanjay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYKfdypeHHA.4300@.TK2MSFTNGP02.phx.gbl...
> Everything in the database? If so, I suggest backup and restore. If not,
> check out some of the tools at
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>> My query is very simple, I am new to SQL.
>> I want to create copy of existing table and data.
>> Pls suggest a command !
>> Thanks in advance
>> Sanjay
>|||Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:
> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need to
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> > My query is very simple, I am new to SQL.
> >
> > I want to create copy of existing table and data.
> > Pls suggest a command !
> >
> > Thanks in advance
> > Sanjay
> >
>
>

Copy datafile on server to server?

Hi all,
Is there a command to copy a file on a sql server directory to another
directory on the same server?
I want to take the database off-line, then copy the datafile from one
directory to another. I am remote to the server and want a speedy copy made
by the server not my client.
After, I would bring the database back on-line and have the copy available
to download locally.
Thanks,
John.You can use sp_detach_db, then xp_cmdshell to execute a "DOS" copy command
and then sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is there a command to copy a file on a sql server directory to another
> directory on the same server?
> I want to take the database off-line, then copy the datafile from one
> directory to another. I am remote to the server and want a speedy copy
made
> by the server not my client.
> After, I would bring the database back on-line and have the copy available
> to download locally.
> Thanks,
> John.
>|||Hi,
To Add on to Tibers post, you can create a batch file in SQL Server and
schedule as as Job.
1. Perform the back of database (Use Restore Database)
2. Map the drive of remote server ( Net use command)
3. Copy the file to remote server and remove the mapping (Use net use drive
letter /d)
4. Load this backup file in remote server
This dont require your server to go offline.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is there a command to copy a file on a sql server directory to another
> directory on the same server?
> I want to take the database off-line, then copy the datafile from one
> directory to another. I am remote to the server and want a speedy copy
made
> by the server not my client.
> After, I would bring the database back on-line and have the copy available
> to download locally.
> Thanks,
> John.
>|||Is this command run from Query Analyzer? If not where is it to be ran from?
John.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23ifWUrlwDHA.2528@.TK2MSFTNGP10.phx.gbl...
> You can use sp_detach_db, then xp_cmdshell to execute a "DOS" copy command
> and then sp_attach_db.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> > Is there a command to copy a file on a sql server directory to another
> > directory on the same server?
> > I want to take the database off-line, then copy the datafile from one
> > directory to another. I am remote to the server and want a speedy copy
> made
> > by the server not my client.
> >
> > After, I would bring the database back on-line and have the copy
available
> > to download locally.
> >
> > Thanks,
> > John.
> >
> >
>|||This is great idea; one that I keep forgetting about because my backup file
is over a gig and is too big for me to copy. What do you mean by "(Use
Restore Database)? Are you just saying that I would want to use the Restore
Database functions once I have the backup local?
John.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eZw9JrmwDHA.1364@.tk2msftngp13.phx.gbl...
> Hi,
> To Add on to Tibers post, you can create a batch file in SQL Server and
> schedule as as Job.
> 1. Perform the back of database (Use Restore Database)
> 2. Map the drive of remote server ( Net use command)
> 3. Copy the file to remote server and remove the mapping (Use net use
drive
> letter /d)
> 4. Load this backup file in remote server
> This dont require your server to go offline.
> Thanks
> Hari
> MCDBA
>
>
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> > Is there a command to copy a file on a sql server directory to another
> > directory on the same server?
> > I want to take the database off-line, then copy the datafile from one
> > directory to another. I am remote to the server and want a speedy copy
> made
> > by the server not my client.
> >
> > After, I would bring the database back on-line and have the copy
available
> > to download locally.
> >
> > Thanks,
> > John.
> >
> >
>|||Yes.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John Rugo" <jrugo@.patmedia.net> wrote in message news:%232JAATwwDHA.560@.TK2MSFTNGP11.phx.gbl...
> Is this command run from Query Analyzer? If not where is it to be ran from?
> John.
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:%23ifWUrlwDHA.2528@.TK2MSFTNGP10.phx.gbl...
> > You can use sp_detach_db, then xp_cmdshell to execute a "DOS" copy command
> > and then sp_attach_db.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "John Rugo" <jrugo@.patmedia.net> wrote in message
> > news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > Hi all,
> > > Is there a command to copy a file on a sql server directory to another
> > > directory on the same server?
> > > I want to take the database off-line, then copy the datafile from one
> > > directory to another. I am remote to the server and want a speedy copy
> > made
> > > by the server not my client.
> > >
> > > After, I would bring the database back on-line and have the copy
> available
> > > to download locally.
> > >
> > > Thanks,
> > > John.
> > >
> > >
> >
> >
>|||John
Another appoach
After backuping your database you can compres it by using WINRAR or WINZIP
and then copy it to another server.
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:us4SIUwwDHA.2340@.TK2MSFTNGP12.phx.gbl...
> This is great idea; one that I keep forgetting about because my backup
file
> is over a gig and is too big for me to copy. What do you mean by "(Use
> Restore Database)? Are you just saying that I would want to use the
Restore
> Database functions once I have the backup local?
> John.
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:eZw9JrmwDHA.1364@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > To Add on to Tibers post, you can create a batch file in SQL Server and
> > schedule as as Job.
> >
> > 1. Perform the back of database (Use Restore Database)
> > 2. Map the drive of remote server ( Net use command)
> > 3. Copy the file to remote server and remove the mapping (Use net use
> drive
> > letter /d)
> > 4. Load this backup file in remote server
> >
> > This dont require your server to go offline.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> > "John Rugo" <jrugo@.patmedia.net> wrote in message
> > news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > Hi all,
> > > Is there a command to copy a file on a sql server directory to
another
> > > directory on the same server?
> > > I want to take the database off-line, then copy the datafile from one
> > > directory to another. I am remote to the server and want a speedy
copy
> > made
> > > by the server not my client.
> > >
> > > After, I would bring the database back on-line and have the copy
> available
> > > to download locally.
> > >
> > > Thanks,
> > > John.
> > >
> > >
> >
> >
>|||Hi,
Yes John, you are correct.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:us4SIUwwDHA.2340@.TK2MSFTNGP12.phx.gbl...
> This is great idea; one that I keep forgetting about because my backup
file
> is over a gig and is too big for me to copy. What do you mean by "(Use
> Restore Database)? Are you just saying that I would want to use the
Restore
> Database functions once I have the backup local?
> John.
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:eZw9JrmwDHA.1364@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > To Add on to Tibers post, you can create a batch file in SQL Server and
> > schedule as as Job.
> >
> > 1. Perform the back of database (Use Restore Database)
> > 2. Map the drive of remote server ( Net use command)
> > 3. Copy the file to remote server and remove the mapping (Use net use
> drive
> > letter /d)
> > 4. Load this backup file in remote server
> >
> > This dont require your server to go offline.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> > "John Rugo" <jrugo@.patmedia.net> wrote in message
> > news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > Hi all,
> > > Is there a command to copy a file on a sql server directory to
another
> > > directory on the same server?
> > > I want to take the database off-line, then copy the datafile from one
> > > directory to another. I am remote to the server and want a speedy
copy
> > made
> > > by the server not my client.
> > >
> > > After, I would bring the database back on-line and have the copy
> available
> > > to download locally.
> > >
> > > Thanks,
> > > John.
> > >
> > >
> >
> >
>

Copy Database Wizard

I want to copy a database that is currently in use. The
DTS package fails if any user is connected. What command
would I issue to block all new connections to the
database? With new connections blocked and existing ones
finished, I should be able to copy the file the way the
wizard configures the package.Check out the ALTER DATABASE command in BOL
Nathan H.O.|||I assume that you are recommending something like:
ALTER DATABASE myDB SET SINGLE_USER
This command locks when the database is already in use.
>--Original Message--
>Check out the ALTER DATABASE command in BOL
>Nathan H.O.
>|||Not if you specify the proper ROLLBACK option.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Dan" <deletethisprefixandthefirstperiod.cathedr@.dshs.wa.gov> wrote in message
news:2aab201c39270$35205650$a601280a@.phx.gbl...
> I assume that you are recommending something like:
> ALTER DATABASE myDB SET SINGLE_USER
> This command locks when the database is already in use.
>
> >--Original Message--
> >Check out the ALTER DATABASE command in BOL
> >
> >Nathan H.O.
> >