copy of existing table and data

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
>
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...
>
>

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye
1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options).
3. Create your destination db.
4. Run the generated script.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:

> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>
|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:

> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
>
>

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options).
3. Create your destination db.
4. Run the generated script.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:
> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I would like to copy the structure of a DB (tables,relations, indexes,
> > default values, ecc...) into a new DB on the same server.
> >
> > I don't want to copy the data in the tables. Is this possible? How?
> >
> > Thank you very much
> >
> > Bye
> >
> >
>
>

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options
).
3. Create your destination db.
4. Run the generated script.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:

> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:

> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
>
>

copy db

in sql 2005 -- what's the best way for me to make a complete copy of my database - including indexes and keys and everything??
i tried backup but when restoring to the second i get an error with multiple media sets.What error are you getting on the restore?|||If you are restoring to the same server, then you need to rename all of the physical files.|||Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I am doing

RESTORE DATABASE trafficr
FROM DISK = 'c:\backups\traffic.bak'
WITH MOVE 'traffic' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\trafficr.mdf',
MOVE 'traffic_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\trafficr_log.ldf',

REPLACE|||What's with the commas at the end of each line?|||this is the syntax i'm using for sql server - -is it wrong?

i think it once worked but not working now - maybe with the way i did the backup? how should i do the backup so one media|||post the output of this:

RESTORE headeronly FROM DISK = 'c:\backups\traffic.bak'

you may have more than one backup in the file.sqlsql

Copy database compability

Hi,

In sql 2000 was possible to copy entire database objects from one db to another. This includes tables, pk, indexes, views, sp, functions, logins and so on.

This is very usefull because at the same time it creates a full log with entire database scripting, it can be used to track changes in databases.

In sql 2005, I can't find a solution like this.

Someone have an idea to this?

Hi

Even in SQL2005 you can copy whole database with complete schema intact using SSIS (Transfer database task).

Thanx

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
> >
> >
>