Correct Table Structure - Optional Values

Hello,

I have 3 optional text boxes. I don't know if the best way to set up
the table would be a field for each box, since this would leave gaps in
the table if the user only filled in one box. Is there a good method
to use?? This is kind of like storing check box values, in that there
could be multiple answers.traceyburger@.sw.rr.com wrote:
> Hello,
> I have 3 optional text boxes. I don't know if the best way to set up
> the table would be a field for each box, since this would leave gaps in
> the table if the user only filled in one box. Is there a good method
> to use?? This is kind of like storing check box values, in that there
> could be multiple answers.

You don't give us much to go on but based off what you said, it sounds
as if the data that goes in these text boxes should be in their own table.

Zach|||(traceyburger@.sw.rr.com) writes:
> I have 3 optional text boxes. I don't know if the best way to set up
> the table would be a field for each box, since this would leave gaps in
> the table if the user only filled in one box. Is there a good method
> to use?? This is kind of like storing check box values, in that there
> could be multiple answers.

What do you mean with gaps? With this miniscule information, it sounds
to me that the columns mapping to these text boxes should be nullable.
Thus if a user only enters value in one box, you store NULL in the other
columns.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Copying/Transferring Tables

Is there a way to copy all tables (not the data, just the structure) and their relationship diagrams from one SQL server db to another?
What is the easiest way?
ThanksRight click on the Database,Generate Sql Script and set the condition on all the tabs.In order to transfer the permissions,roles,relationship you need to go under option in the same wizard.|||IMHO DTS is the best choice here as it will take care of all dependent objects and constraints. In this case you would elect NOT to transfer the data or probably user id and permissions. The only problem with this is it will not transfer the relationship diagram.|||I just want to transfer Tables and diagrams. I don't want other objects.
I know I can use a wizard to transfer the tables but it doesn't have an option to transfer the diagrams.

Originally posted by sqlserver2k
Right click on the Database,Generate Sql Script and set the condition on all the tabs.In order to transfer the permissions,roles,relationship you need to go under option in the same wizard.|||I am not aware of any tool or wizard that will transfer a relationship diagram, however the data is contained in a table called dtProperties and you could use the following to transfer a diagram:

set identity_insert dtproperties on
INSERT INTO <target db>.dbo.dtproperties(id, objectid, property, value, uvalue, lvalue, version)
select * from <source db>.dbo.dtproperties
set identity_insert dtproperties offsql

Copying tables between database

Hi
I have 2 user created databases in MS SQL Server 2000. Now I want to copy tables from one of this database to the other. The structure, relationships etc of these tables between these 2 databases are very similar. I searched high and low in BOL but could not find any info
Can some one help me in this please ...
Thanks
Harish MohanbabuHarish, you have many different solutions:
- SELECT col_list INTO db2.dbo.newtable FROM db1.dbo.oldtable - check
SELECT statement in Books OnLine
- Use Data Transformation Services (you can do it with the DTS Import/Export
Wizard) - check "DTS Import/Export Wizard" topic
- Create scripts, if you need metadata only, and implement scripts in the
new database - check "How to generate a script (Enterprise Manager)"
- ...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:CFDD7D65-54E0-436D-8B40-51DBE7CA6E90@.microsoft.com...
> Hi,
> I have 2 user created databases in MS SQL Server 2000. Now I want to copy
tables from one of this database to the other. The structure, relationships
etc of these tables between these 2 databases are very similar. I searched
high and low in BOL but could not find any info.
> Can some one help me in this please ...
> Thanks,
> Harish Mohanbabu|||Hi,
Since you have the schema in both the databases, Probably you need to copy
the data from database to another in same server, then you can use
1. Copy only data
insert into tablename select * from dbname..tablename
2. If we need to replicate all the tables / users / procedures in one
database to another then you can Backup and Restore
Backup database dbname1 to disk='c:\dbname.bak' with init,stats=10
go
Restore database dbname2 from disk='c:\dbname.bak' with move
'logicaldatafilename' to 'newphysicalfile.mdf',
move 'logicallogfilename' to 'newphysicalfile.ldf'
Thanks
Hari
MCDBA
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:CFDD7D65-54E0-436D-8B40-51DBE7CA6E90@.microsoft.com...
> Hi,
> I have 2 user created databases in MS SQL Server 2000. Now I want to copy
tables from one of this database to the other. The structure, relationships
etc of these tables between these 2 databases are very similar. I searched
high and low in BOL but could not find any info.
> Can some one help me in this please ...
> Thanks,
> Harish Mohanbabu|||Hi Dejan/Hari,
Thank you very much for your kind replies. Since the structure of tables between databases were same, I used DTS to copy data between tables.
Cheers,
Harish Mohanbabu

Copying tables between database

Hi,
I have 2 user created databases in MS SQL Server 2000. Now I want to copy t
ables from one of this database to the other. The structure, relationships e
tc of these tables between these 2 databases are very similar. I searched h
igh and low in BOL but coul
d not find any info.
Can some one help me in this please ...
Thanks,
Harish MohanbabuHarish, you have many different solutions:
- SELECT col_list INTO db2.dbo.newtable FROM db1.dbo.oldtable - check
SELECT statement in Books OnLine
- Use Data Transformation Services (you can do it with the DTS Import/Export
Wizard) - check "DTS Import/Export Wizard" topic
- Create scripts, if you need metadata only, and implement scripts in the
new database - check "How to generate a script (Enterprise Manager)"
- ...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:CFDD7D65-54E0-436D-8B40-51DBE7CA6E90@.microsoft.com...
> Hi,
> I have 2 user created databases in MS SQL Server 2000. Now I want to copy
tables from one of this database to the other. The structure, relationships
etc of these tables between these 2 databases are very similar. I searched
high and low in BOL but could not find any info.
> Can some one help me in this please ...
> Thanks,
> Harish Mohanbabu|||Hi,
Since you have the schema in both the databases, Probably you need to copy
the data from database to another in same server, then you can use
1. Copy only data
insert into tablename select * from dbname..tablename
2. If we need to replicate all the tables / users / procedures in one
database to another then you can Backup and Restore
Backup database dbname1 to disk='c:\dbname.bak' with init,stats=10
go
Restore database dbname2 from disk='c:\dbname.bak' with move
'logicaldatafilename' to 'newphysicalfile.mdf',
move 'logicallogfilename' to 'newphysicalfile.ldf'
Thanks
Hari
MCDBA
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:CFDD7D65-54E0-436D-8B40-51DBE7CA6E90@.microsoft.com...
> Hi,
> I have 2 user created databases in MS SQL Server 2000. Now I want to copy
tables from one of this database to the other. The structure, relationships
etc of these tables between these 2 databases are very similar. I searched
high and low in BOL but could not find any info.
> Can some one help me in this please ...
> Thanks,
> Harish Mohanbabu

Copying table structure using VS 2005

I is possible to copy the structure of an existing SQL 2000 table to make a new table with Visual Studio 2005?Definetely. Just generate the script either in Query Analyzer of using Enterprise Manager and execute it ont he SQL 2005 server.|||

The database is SQL 2000 and I was wondering if Visual Studio 2005 offered any native capability to copy table structure. It offers editing, query, and stored procedure contructors - I thought it might have a copy table or duplicate table feature.

sql

Copying structure of table form one database to another

I am wondering if there is a simple way, like a stored procudure or
using some type of table object, to copy a table form one database to
another. Just the structure , index, triggers, I don't want the data.kevin.jonas@.gmail.com wrote:
> I am wondering if there is a simple way, like a stored procudure or
> using some type of table object, to copy a table form one database to
> another. Just the structure , index, triggers, I don't want the data.
You can generate a script for the whole schema with EM and execute it on
the target db. Maybe there's also a solution involving DTS - I'm not sure.
robert|||Using DMO library also is possible and using DTS
--
Current location: Alicante (ES)
"Robert Klemme" wrote:

> kevin.jonas@.gmail.com wrote:
> You can generate a script for the whole schema with EM and execute it on
> the target db. Maybe there's also a solution involving DTS - I'm not sure
.
> robert
>

Copying database between servers

I need to move a SQL 2005 database between 2 servers.
But the 2 servers have different path structure due to language difference.
One server has the databse installed under "c:\Program Files ..." and the
other under "C:\Programmer..." It is not possible to restore the database,
and present the error Directory lookup for the file" C:\Programmer\Microsoft
SQL Server\MSSQL\Data\xx_Data.MDF" failed with the operating system error
The path was not fould.
How can I manually manage the backup and Restore to accomodate the different
path setup on the 2 servers?
I've used Enterprise Manager to export the original database and import it
to the new server. As I recall, I got an error popup and was given the
opportunity to change the location.
One approach I've used is to create the directory structure I wanted on the
new server. I then detached the database (in Enterprise Manager) and copied
the MDF file to where I want it. Then attach the database and change the
location of the MDF file. Verify it works and delete the old copy. You can
do the same for the LDF (log) file...
Regards,
Hank Arnold
"Bigalexx" <Bigalexx@.community.nospam> wrote in message
news:%23sv3s3HBGHA.2040@.TK2MSFTNGP14.phx.gbl...
>I need to move a SQL 2005 database between 2 servers.
> But the 2 servers have different path structure due to language
> difference. One server has the databse installed under "c:\Program Files
> ..." and the other under "C:\Programmer..." It is not possible to restore
> the database, and present the error Directory lookup for the file"
> C:\Programmer\Microsoft SQL Server\MSSQL\Data\xx_Data.MDF" failed with the
> operating system error The path was not fould.
> How can I manually manage the backup and Restore to accomodate the
> different path setup on the 2 servers?
>
|||You need to use the MOVE option for the backup command. This is documented in Books Online (BACKUP
DATABASE). This is also exposed in Management Studio, options page, "Restore As" column
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bigalexx" <Bigalexx@.community.nospam> wrote in message
news:%23sv3s3HBGHA.2040@.TK2MSFTNGP14.phx.gbl...
>I need to move a SQL 2005 database between 2 servers.
> But the 2 servers have different path structure due to language difference. One server has the
> databse installed under "c:\Program Files ..." and the other under "C:\Programmer..." It is not
> possible to restore the database, and present the error Directory lookup for the file"
> C:\Programmer\Microsoft SQL Server\MSSQL\Data\xx_Data.MDF" failed with the operating system error
> The path was not fould.
> How can I manually manage the backup and Restore to accomodate the different path setup on the 2
> servers?
>

copying data folder to another instance

How do I copy the data from one instance of an MSDE to another? What I need
to do is write the structure, data, etc on a CD so I can copy this back on
my server instance at home and work on it.
Thanks for the information.
Brad
Attach/Detach or Backup/Restore
"Brad" <ballison@.ukcdogs.com> wrote in message
news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> How do I copy the data from one instance of an MSDE to another? What I
need
> to do is write the structure, data, etc on a CD so I can copy this back on
> my server instance at home and work on it.
> Thanks for the information.
> Brad
>
|||Norman,
I primarily progam and I really am not a DB Admin. I understand the
concepts of Attach/Detach but I have never practically used it. How would I
get into the database to do that? Is the Backup/Restore the Backup that is
used through Windows?
I know I can get into it with OSQL, right? When I do try this opening a
command window, typing OSQL -U sa, then the sa password, I get the following
error:
[Shared Memory]SQL Server does not exist or access denied
[Shared Memory]ConnectionOpen (Connect())
Thanks for the information.
Brad
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Attach/Detach or Backup/Restore
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> need
>
|||Norman,
In doing some research I changed OSQL -U to OSQL -E -S <server\instance> and
it worked.
I am now using BACKUP and RESTORE.
Thanks for the information.
Brad
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Attach/Detach or Backup/Restore
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> need
>
|||Now I am getting an error message when I try to RESTORE. It looks as if
RESTORE is using the InstanceName from the Backup and I am trying to retore
to a different MSDE instance name on a different server. It gives me error
messages.
Did I do something wrong with the restore statement?
"Brad" <ballison@.ukcdogs.com> wrote in message
news:OTXJuiEEFHA.464@.TK2MSFTNGP15.phx.gbl...
> Norman,
> In doing some research I changed OSQL -U to OSQL -E -S <server\instance>
> and it worked.
> I am now using BACKUP and RESTORE.
> Thanks for the information.
> Brad
>
> "Norman Yuan" <NotReal@.NotReal.not> wrote in message
> news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
|||Posting the error message would help, but you're probably trying to restore
a database whose physical location does not exists on the destination
computer. Look at the WITH MOVE qualifier of the RESTORE command. This will
allow you to the logical database files to a different physical location
than where they originated from. Another solution is to make the location
for the database files the same on all systems.
Jim
"Brad" <ballison@.ukcdogs.com> wrote in message
news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
> Now I am getting an error message when I try to RESTORE. It looks as if
> RESTORE is using the InstanceName from the Backup and I am trying to
> retore to a different MSDE instance name on a different server. It gives
> me error messages.
> Did I do something wrong with the restore statement?
>
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OTXJuiEEFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||Jim,
Thanks for the information. This is the error message that I get:
File 'CLASSICSQL' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL.mdf'. Use WITH MOVE to identify a valid
location for the file.
The syntax I am using is "RESTORE DATABASE ced FROM DISK =
'D:\MSDEBack\CES.bak' "
This is when I get the error. What is the syntax using the WITH MOVE? or
where would I find this information?
Thanks,
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uOfJD%23FEFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Posting the error message would help, but you're probably trying to
restore
> a database whose physical location does not exists on the destination
> computer. Look at the WITH MOVE qualifier of the RESTORE command. This
will[vbcol=seagreen]
> allow you to the logical database files to a different physical location
> than where they originated from. Another solution is to make the location
> for the database files the same on all systems.
> Jim
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
gives[vbcol=seagreen]
<server\instance>[vbcol=seagreen]
I[vbcol=seagreen]
back
>
|||Jim,
Got it. Using osql for the first time and making sure there are not any
syntax errors is a pain.
Thanks again,
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uOfJD%23FEFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Posting the error message would help, but you're probably trying to
restore
> a database whose physical location does not exists on the destination
> computer. Look at the WITH MOVE qualifier of the RESTORE command. This
will[vbcol=seagreen]
> allow you to the logical database files to a different physical location
> than where they originated from. Another solution is to make the location
> for the database files the same on all systems.
> Jim
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
gives[vbcol=seagreen]
<server\instance>[vbcol=seagreen]
I[vbcol=seagreen]
back
>
|||hi,
AllcompPC wrote:
> Jim,
> Thanks for the information. This is the error message that I get:
> File 'CLASSICSQL' cannot be restored to 'C:\Program Files\Microsoft
> SQL Server\MSSQL$UKCSQL\Data\CLASSICSQL.mdf'. Use WITH MOVE to
> identify a valid location for the file.
> The syntax I am using is "RESTORE DATABASE ced FROM DISK =
> 'D:\MSDEBack\CES.bak' "
> This is when I get the error. What is the syntax using the WITH
> MOVE? or where would I find this information?
>
RESTORE DATABASE ced
FROM DISK = 'D:\MSDEBack\CES.bak'
WITH
MOVE Logical_File_Name_for_Data to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL.Mdf' ,
MOVE Logical_File_Name_for_Log to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL_Log.Ldf'
or the alike, specifying the logical file names and the destination physical
position you want the files to be restored to..
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

copying data and structure from one database to another

Hi all!

I have an application that needs to copy the database structure from
one database to another without using the "Generate SQL Script"
function in Enterprise Manager. I'd like to do this from within a
stored procedure. Can someone recommend the best approach for this?
I've seen references to using SQL-DMO from a stored procedure using the
sp_OA* procs in other postings to this group but was wondering if there
was an easier way? Can I use bcp and then use xp_cmdshell from within
my stored procedure? It's not clear to me from the documentation
whether bcp copies both structure and data or just data? Is there a
better way?

Thanks in advance for any help!
Karen[posted and mailed, posted and mailed]

(kjphipps_377@.hotmail.com) writes:
> I have an application that needs to copy the database structure from
> one database to another without using the "Generate SQL Script"
> function in Enterprise Manager. I'd like to do this from within a
> stored procedure. Can someone recommend the best approach for this?
> I've seen references to using SQL-DMO from a stored procedure using the
> sp_OA* procs in other postings to this group but was wondering if there
> was an easier way? Can I use bcp and then use xp_cmdshell from within
> my stored procedure? It's not clear to me from the documentation
> whether bcp copies both structure and data or just data? Is there a
> better way?

bcp copies only the data.

If you absolutely must copy table definitions and all from a stored
procedure, you are in for a painful exercise. I'd guess that DMO is
the way to go. You could read the system tables and construct SQL
from there, but that would be even more difficult. Particularly if
you need to take in regard that a stored procedure could extend over
more than 4000 characters.

But overall, I would recommend you to review the requirements. T-SQL
is simply not the right tool do this. If you absolutely must fire
a stored procedure, I would recommend writing a program in Perl,
VBscript or whatever, and call that program from xp_cmdshell. But it
goes without saying that it would be better to run this from the
application directly.

Also when running from an application, DMO may be the best pick. I
don't have any experience of DMO myself, so I don't know for sure
whether there is any built-in scripting facilities, but I would
expect there to be.

The general for creating database, is to keep code under source
control, and build the database from the version-controlled scripts.

To copy the data, bcp would still be necessary, but that's the easy
part of it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Copy the table structures of all the tables

CREATE TABLE table_name AS SELECT STATEMENT WHERE rownum = 0;
this query will copy the table structure of one table without copying the data. I need to copy the table structures of all the tables in a particular database without copying the data.
suggest me on this.any body from microsoft|||gee I almost worked for microsoft. is that good enough?

in SQL2k, right click on your database in the Enterprise Mangler and choose Generate SQL script.|||rownum = 0?

What version are you talking about|||SQL Server 2000|||SQL Server 2000
Are you running this statement in sql server 2000? I don't think so...

CREATE TABLE table_name AS SELECT STATEMENT WHERE rownum = 0;

Please check your requirement,
That will not work at all,but this will

SELECT TOP 0 * INTO NewTable FROM OldTable

well Thrasymachus has already given you the solution.|||You can try an app I wrote for this called scriptdb. It will create scripts for ALL objects, not just tables, a separate file for each.

the source is freely available so you can modify it to suit your needs if you know C#.

get it here: http://www.elsasoft.org/tools.htm

EDIT: and I used to be a dev on the ms sql team, but no longer. does that count? :)

Copy the table structure

Hi,
In SQL Server, Is there any DDL available to copy a table structure alone and not the data,
I believe 'SELECT * into new_table from table1' will copy both structure and data as well.
Please advice,
Thanks,
MiraJYou can use SELECT...INTO to create an identical table definition (different table name) with no data by having a FALSE condition in the WHERE clause.

SELECT * INTO new_table FROM table1 WHERE 1 = 0|||Understand though that Indexes and constraints do not get copied over...

Copy Tabular Structure into a temp table

Hello Room,
M stuck with an issue with SQL Query. My requirement is to create a temporary table with the out put of "SELECT" Clause........

Like......
SELECT NC_ID, AUD_PLAN_ID, AUD_CLAUSE_ID FROM AUD_NC

This is my "Select" Query and all I want is to store the out put in a #table. Now one way is to craete #tmp_table with the fields same as the out put column of the "SELECT" Clause....... But I want it in direct fashion... dat means without Creating the #tmp_table directly.........

I tried with "CREATE TBALE #tmp_table AS SELECT NC_ID, AUD_PLAN_ID, AUD_CLAUSE_ID FROM AUD_NC"........ But it dini work

Please help me out in this regard.....SELECT NC_ID,
AUD_PLAN_ID,
AUD_CLAUSE_ID
into
#tmp_table
FROM AUD_NC|||Thankx Mallier........... It works........:)

Copy tables?

Hello!
Pls tell me how to copy a table (want to have another table with another
name, but has the same structure).
Thanks for your help!
select * into NewTable from OldTable where 1 = 0
"Chi Pheo" <chipheo2k@.mail.ru> wrote in message
news:uRXezz1wEHA.3612@.tk2msftngp13.phx.gbl...
> Hello!
> Pls tell me how to copy a table (want to have another table with another
> name, but has the same structure).
> Thanks for your help!
>
|||Problem with this solution is that it won't create any indexes or constraints.
You can use the Enterprise Manager Generate Script, change the name in the
script, and INSERT INTO new table FROM old table. Or use a Copy Object task
in DTS.
Sincerely,
Anthony Thomas
"John Ryan" wrote:

> select * into NewTable from OldTable where 1 = 0
> "Chi Pheo" <chipheo2k@.mail.ru> wrote in message
> news:uRXezz1wEHA.3612@.tk2msftngp13.phx.gbl...
>
>
|||OK, thanks so much!
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> /
:
news:9E63270A-F95C-4605-82FE-ABA22859A7FC@.microsoft.com...
> Problem with this solution is that it won't create any indexes or
constraints.
> You can use the Enterprise Manager Generate Script, change the name in the
> script, and INSERT INTO new table FROM old table. Or use a Copy Object
task[vbcol=seagreen]
> in DTS.
> Sincerely,
>
> Anthony Thomas
> "John Ryan" wrote:
another[vbcol=seagreen]

Copy tables?

Hello!
Pls tell me how to copy a table (want to have another table with another
name, but has the same structure).
Thanks for your help!select * into NewTable from OldTable where 1 = 0
"Chi Pheo" <chipheo2k@.mail.ru> wrote in message
news:uRXezz1wEHA.3612@.tk2msftngp13.phx.gbl...
> Hello!
> Pls tell me how to copy a table (want to have another table with another
> name, but has the same structure).
> Thanks for your help!
>|||Problem with this solution is that it won't create any indexes or constraints.
You can use the Enterprise Manager Generate Script, change the name in the
script, and INSERT INTO new table FROM old table. Or use a Copy Object task
in DTS.
Sincerely,
Anthony Thomas
"John Ryan" wrote:
> select * into NewTable from OldTable where 1 = 0
> "Chi Pheo" <chipheo2k@.mail.ru> wrote in message
> news:uRXezz1wEHA.3612@.tk2msftngp13.phx.gbl...
> > Hello!
> > Pls tell me how to copy a table (want to have another table with another
> > name, but has the same structure).
> >
> > Thanks for your help!
> >
> >
>
>|||OK, thanks so much!
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:9E63270A-F95C-4605-82FE-ABA22859A7FC@.microsoft.com...
> Problem with this solution is that it won't create any indexes or
constraints.
> You can use the Enterprise Manager Generate Script, change the name in the
> script, and INSERT INTO new table FROM old table. Or use a Copy Object
task
> in DTS.
> Sincerely,
>
> Anthony Thomas
> "John Ryan" wrote:
> > select * into NewTable from OldTable where 1 = 0
> >
> > "Chi Pheo" <chipheo2k@.mail.ru> wrote in message
> > news:uRXezz1wEHA.3612@.tk2msftngp13.phx.gbl...
> > > Hello!
> > > Pls tell me how to copy a table (want to have another table with
another
> > > name, but has the same structure).
> > >
> > > Thanks for your help!
> > >
> > >
> >
> >
> >

Copy tables?

Hello!
Pls tell me how to copy a table (want to have another table with another
name, but has the same structure).
Thanks for your help!select * into NewTable from OldTable where 1 = 0
"Chi Pheo" <chipheo2k@.mail.ru> wrote in message
news:uRXezz1wEHA.3612@.tk2msftngp13.phx.gbl...
> Hello!
> Pls tell me how to copy a table (want to have another table with another
> name, but has the same structure).
> Thanks for your help!
>|||Problem with this solution is that it won't create any indexes or constraint
s.
You can use the Enterprise Manager Generate Script, change the name in the
script, and INSERT INTO new table FROM old table. Or use a Copy Object task
in DTS.
Sincerely,
Anthony Thomas
"John Ryan" wrote:

> select * into NewTable from OldTable where 1 = 0
> "Chi Pheo" <chipheo2k@.mail.ru> wrote in message
> news:uRXezz1wEHA.3612@.tk2msftngp13.phx.gbl...
>
>|||OK, thanks so much!
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> /
:
news:9E63270A-F95C-4605-82FE-ABA22859A7FC@.microsoft.com...
> Problem with this solution is that it won't create any indexes or
constraints.
> You can use the Enterprise Manager Generate Script, change the name in the
> script, and INSERT INTO new table FROM old table. Or use a Copy Object
task[vbcol=seagreen]
> in DTS.
> Sincerely,
>
> Anthony Thomas
> "John Ryan" wrote:
>
another[vbcol=seagreen]

copy tables along with constraints in DTS

Hi All,

We are using DTS tool for importing data from one database which is present in one server to another present in another server. the structure and the data is getting copied where as the constraints are not getting copied. pls suggest me as to how should i go about to to complete the job.

waiting for ur reply.

TIA
regards
AdilWhat exactly are your requirements for this ? Are you copying tables/databases ... you need indexes, constraints ...|||In Enterprise Manager, right click on the database and select "All Tasks - Import/Export", ... , select "copy objects and data between SQL Server database", click on "next", you have the option to copy all database objects include constraints into destination database.|||Hi Adil,

Would encourage you to use a free tool for DTS, which copies tables along with constraints. Please try Vaman DataServer from the following link. http://www.vaman.net/vmndataserver.asp
You may need to login for downloading.
Hope this helps...

popeye.

Originally posted by aadil
Hi All,

We are using DTS tool for importing data from one database which is present in one server to another present in another server. the structure and the data is getting copied where as the constraints are not getting copied. pls suggest me as to how should i go about to to complete the job.

waiting for ur reply.

TIA
regards
Adil

Copy table structure to a new table

Hello everyone,

I have a local MSSQL server (I guess it's called MSDE), with some tables that I would like to use as a template for a set of new tables. I would simply like a Stored Procedure that takes these 3 tables, makes a copy of their structure (not data, since they will be empty), and name them by using a parameter given to the SP. I have made something that I thought would work, but after testing it a bit more, it seems to forget default values for the fields, which is of course not good enough :). I hope that someone can tell me how to duplicate these 3 tables, including every detail for the fields!

This is not really a trivial task in T-SQL. You can probablycobble something together using the INFORMATION_SCHEMA views and someheavy dynamic SQL, but it may be easier to simply hardcode the tables'DDL into your stored procedure.
Or, you could use DMO to help you. See if this helps:
http://www.nigelrivett.net/DMO/DMOScripting.html

Copy Table structure only - not Data

We have SQL Enterprise Manager (8.0). Is there any way to Copy a Table from one database to another with only the Structure (design) - not all the Data?

I can't find any option in the Import Data Wizard that only copies the Table structure.

Any help is appreciated. Thanks

You can generate script for the table and compile it on the target server. Right click on the table in EM, All Tasks -> Generate SQL Script...

Copy table Structure including primary keys, index etc.

Hi all,

I was wondering if there is a SQL command to copy the table structure of a table that includes primary keys, foreign keys, indexes, etc.

Thanks and have a nice day to all

Not a SQL command, but you can script this stuff out using the tools by right clicking the table, or programatically using SMO.

|||can you post a sample script or SMO please or send me a link discuss this matter thanks|||

Hi,

The easiest way to create the script is to right click the original table within SQL Server Management Studio and select "Script Table As ...\ Create To\ ..." . This will create a script for the table and its indexes.

Then you need to run the script but with the new tablename. After that, you need to copy the records using a insert/select command. (it is best to set the constraints/indexes afterwards).

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

I was thinking to use that script in my SP, On my SP I add a linked server then i want to copy all the tables exactly the same

and on the linked server the table names and table count change everyday, but i dont have any problem with that.

I was thinking if there is a way to copy exactly the same table inside an SP in that case.

Thanks

|||You can use sys tables/views inside your SP|||

can you post your samples script please.

thanks

|||

SELECT * INTO NewEmployee FROM Employee WHERE 1 = 0

above query will create same structure table called NewEmployee with structure of Employee. But will not have triggers and primary keys etc. you can create them by using follwing scripts

SELECT *

FROM sysobjects

WHERE parent_obj = OBJECT_ID('Employee')

SELECT *

FROM syscomments

WHERE id IN ( SELECT id

FROM sysobjects

WHERE parent_obj = OBJECT_ID('Employee') )

|||thanks for the reply Dinesh. Nice sql Stmt, does this work when you have a MS Access linked Server, is there a sysobjects table on the linked server?

copy table structure

Hi,
In SQL Server, Is there any DDL available to copy a table structure alone and not the data,
I believe 'SELECT * into new_table from table1' will copy both structure and data as well.
Please advice,
Thanks,
SmithaYou can do:

select top 0 * into new_Table from table1;

--or
select top 0 * into new_Table from table1 where 1=0;

-- or in SQL Server 2005
select top(0) * into new_Table from table1;