Newbie Question

[mike dwyer]

How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[Sundar]

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[Ray Miao]

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[Sundar]

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[Martyn Hodgson]

This is actually a very good example of why you should never be afraid of asking what appears to be a simple (even silly) question. There is actually a lot in this. Firstly, a lot of DBMS's support a syntax of 'CREATE TABLE table LIKE someOtherTable'. This does the DDL bit and an 'insert into .. select from ..' statement takes care of the data.

The problem with this is that if indexes and constraints are copied automatically what names do you give them? The system will usually generate something at random. However, unless you are creating a table for 2 minutes, you really should give indexes and constraints meaningful names. It makes tracking errors a lot easier. In fact, get one error caused by a constraint violation and you'll spend more time finding out what the (randomly named) constraint is doing than you would have spent naming it properly in the first place.

The other side of this is that even temporary tables should be placed and sized with care, unless you are the *only* user of a database with lots of space. I've seen big systems severely affected (spectacularly so on one occasion) by temporary tables that was created and then forgotten about.

So the moral of this is: if you're creating objects (even short lived ones), get the ddl, name your constraints and indexes, and set their physical parameters with care!


------------
Sundar at 12/18/2001 11:57:15 AM

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[mike dwyer]

Thanks. Yes, that's exactly the problem. You can copy the structure as a script, but as I see it, you have to go in manually and change the "temporary" names. I was hoping there was some magic thing that would regenerate these unique names on the fly for me.

Here's the real question that I need to handle and maybe you guys could give me some insight. I don't know how SQL will handle this best. Here goes...

I've designed a real estate appraisal program for County appraisers. It's an Access upsizing project I've got here, BTW. I'll keep the details to a minimum, for simplicity. I allow each appraiser to create their own "Study" of selected properties (properties as in Homes, not computer-type properties). They enter selection parameters to define a geographical area they want to study. My system then creates computer generated appraisals for each property in the area based on home sales near these properties. That's the gist of it.

Now, the SQL questions...
Each one of these "studies" is it's own unit, so to speak. Each study creates 6 diferent tables of information. These tables hold the characteristics of the properties to study, the comparable sales (comps), adjustments to the sales price of these sales, etc. In Access, I put these six tables in one MDB file, shareable to everyone on the network. This way, a group of Appraisers can look at the results of this study simultaneously. By having each study in it's own MDB file, this study functions as its own "Unit" and can be "Opened" much like a document or a spreadsheet.

Now, the problem: The studies can be quite large. In orange county, Calif., for instance, there are 600,000 properties to study. This could result in one of the tables having as many as 12 Million rows. Or they can be quite small (like 1,000 properties). And with 100 users creating their own studies, there can be many, many, studies. In any event, I need to upsize this to SQL Server. The question is how to store these studies.
Do I...
A) create a Database for each study, as I do in Access? (many many databases)
B) put them in one database, but with different table names
e.g. Mystudy_table1..Mystudy_table6, AnotherStudy_table1..AnotherStudy_table6...(Result ing in many many tables)

C) Put all the studies in one database, in six tables, distinguishing each study by some identifying column in each of the six tables. (resulting in many many rows)

Well, that's my dilema. What are your thoughts?





------------
Martyn Hodgson at 12/20/2001 8:23:02 AM

This is actually a very good example of why you should never be afraid of asking what appears to be a simple (even silly) question. There is actually a lot in this. Firstly, a lot of DBMS's support a syntax of 'CREATE TABLE table LIKE someOtherTable'. This does the DDL bit and an 'insert into .. select from ..' statement takes care of the data.

The problem with this is that if indexes and constraints are copied automatically what names do you give them? The system will usually generate something at random. However, unless you are creating a table for 2 minutes, you really should give indexes and constraints meaningful names. It makes tracking errors a lot easier. In fact, get one error caused by a constraint violation and you'll spend more time finding out what the (randomly named) constraint is doing than you would have spent naming it properly in the first place.

The other side of this is that even temporary tables should be placed and sized with care, unless you are the *only* user of a database with lots of space. I've seen big systems severely affected (spectacularly so on one occasion) by temporary tables that was created and then forgotten about.

So the moral of this is: if you're creating objects (even short lived ones), get the ddl, name your constraints and indexes, and set their physical parameters with care!


------------
Sundar at 12/18/2001 11:57:15 AM

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[Sivakumar]

Mike,

My thought its all about your designing the tables. I would suggest that you first normalize your tables which you are going to create and dont create a study(table) for each appraiser when they need any information. Instead create the necessary sql queries with bind variables so that once the appraiser selected his information request they can have there information from your tables and display it any frontend you want. That way you avoid unneccesary load on your server. (in my personal opinion i will prefer ORACLE over SQL SERVER since you said that your data will be growing maybe you can decide on it in future too). Your PERFORMANCE will be based on how you are going to design the tables so be careful when designing the system.

Regarding your query about multiple database i dont know how the performace will be affected or improved by that option since i havent work much on SQL SERVER. I am not sure about the architecture of SQL SERVER so i cant speak much on it.

Hope someone answer your query regarding that.

Regards,
Sivakumar

------------
mike dwyer at 12/20/2001 2:52:57 PM


Thanks. Yes, that's exactly the problem. You can copy the structure as a script, but as I see it, you have to go in manually and change the "temporary" names. I was hoping there was some magic thing that would regenerate these unique names on the fly for me.

Here's the real question that I need to handle and maybe you guys could give me some insight. I don't know how SQL will handle this best. Here goes...

I've designed a real estate appraisal program for County appraisers. It's an Access upsizing project I've got here, BTW. I'll keep the details to a minimum, for simplicity. I allow each appraiser to create their own "Study" of selected properties (properties as in Homes, not computer-type properties). They enter selection parameters to define a geographical area they want to study. My system then creates computer generated appraisals for each property in the area based on home sales near these properties. That's the gist of it.

Now, the SQL questions...
Each one of these "studies" is it's own unit, so to speak. Each study creates 6 diferent tables of information. These tables hold the characteristics of the properties to study, the comparable sales (comps), adjustments to the sales price of these sales, etc. In Access, I put these six tables in one MDB file, shareable to everyone on the network. This way, a group of Appraisers can look at the results of this study simultaneously. By having each study in it's own MDB file, this study functions as its own "Unit" and can be "Opened" much like a document or a spreadsheet.

Now, the problem: The studies can be quite large. In orange county, Calif., for instance, there are 600,000 properties to study. This could result in one of the tables having as many as 12 Million rows. Or they can be quite small (like 1,000 properties). And with 100 users creating their own studies, there can be many, many, studies. In any event, I need to upsize this to SQL Server. The question is how to store these studies.
Do I...
A) create a Database for each study, as I do in Access? (many many databases)
B) put them in one database, but with different table names
e.g. Mystudy_table1..Mystudy_table6, AnotherStudy_table1..AnotherStudy_table6...(Result ing in many many tables)

C) Put all the studies in one database, in six tables, distinguishing each study by some identifying column in each of the six tables. (resulting in many many rows)

Well, that's my dilema. What are your thoughts?





------------
Martyn Hodgson at 12/20/2001 8:23:02 AM

This is actually a very good example of why you should never be afraid of asking what appears to be a simple (even silly) question. There is actually a lot in this. Firstly, a lot of DBMS's support a syntax of 'CREATE TABLE table LIKE someOtherTable'. This does the DDL bit and an 'insert into .. select from ..' statement takes care of the data.

The problem with this is that if indexes and constraints are copied automatically what names do you give them? The system will usually generate something at random. However, unless you are creating a table for 2 minutes, you really should give indexes and constraints meaningful names. It makes tracking errors a lot easier. In fact, get one error caused by a constraint violation and you'll spend more time finding out what the (randomly named) constraint is doing than you would have spent naming it properly in the first place.

The other side of this is that even temporary tables should be placed and sized with care, unless you are the *only* user of a database with lots of space. I've seen big systems severely affected (spectacularly so on one occasion) by temporary tables that was created and then forgotten about.

So the moral of this is: if you're creating objects (even short lived ones), get the ddl, name your constraints and indexes, and set their physical parameters with care!


------------
Sundar at 12/18/2001 11:57:15 AM

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[mike dwyer]

Well, being new to the SQL server environment, I'm a little worried about having some massive tables with potentially 30 million or so rows of data. They would also like to have the data archived so that they can go back to a set of data that was 3 or four years old.


------------
Sivakumar at 12/21/2001 3:21:05 AM

Mike,

My thought its all about your designing the tables. I would suggest that you first normalize your tables which you are going to create and dont create a study(table) for each appraiser when they need any information. Instead create the necessary sql queries with bind variables so that once the appraiser selected his information request they can have there information from your tables and display it any frontend you want. That way you avoid unneccesary load on your server. (in my personal opinion i will prefer ORACLE over SQL SERVER since you said that your data will be growing maybe you can decide on it in future too). Your PERFORMANCE will be based on how you are going to design the tables so be careful when designing the system.

Regarding your query about multiple database i dont know how the performace will be affected or improved by that option since i havent work much on SQL SERVER. I am not sure about the architecture of SQL SERVER so i cant speak much on it.

Hope someone answer your query regarding that.

Regards,
Sivakumar

------------
mike dwyer at 12/20/2001 2:52:57 PM


Thanks. Yes, that's exactly the problem. You can copy the structure as a script, but as I see it, you have to go in manually and change the "temporary" names. I was hoping there was some magic thing that would regenerate these unique names on the fly for me.

Here's the real question that I need to handle and maybe you guys could give me some insight. I don't know how SQL will handle this best. Here goes...

I've designed a real estate appraisal program for County appraisers. It's an Access upsizing project I've got here, BTW. I'll keep the details to a minimum, for simplicity. I allow each appraiser to create their own "Study" of selected properties (properties as in Homes, not computer-type properties). They enter selection parameters to define a geographical area they want to study. My system then creates computer generated appraisals for each property in the area based on home sales near these properties. That's the gist of it.

Now, the SQL questions...
Each one of these "studies" is it's own unit, so to speak. Each study creates 6 diferent tables of information. These tables hold the characteristics of the properties to study, the comparable sales (comps), adjustments to the sales price of these sales, etc. In Access, I put these six tables in one MDB file, shareable to everyone on the network. This way, a group of Appraisers can look at the results of this study simultaneously. By having each study in it's own MDB file, this study functions as its own "Unit" and can be "Opened" much like a document or a spreadsheet.

Now, the problem: The studies can be quite large. In orange county, Calif., for instance, there are 600,000 properties to study. This could result in one of the tables having as many as 12 Million rows. Or they can be quite small (like 1,000 properties). And with 100 users creating their own studies, there can be many, many, studies. In any event, I need to upsize this to SQL Server. The question is how to store these studies.
Do I...
A) create a Database for each study, as I do in Access? (many many databases)
B) put them in one database, but with different table names
e.g. Mystudy_table1..Mystudy_table6, AnotherStudy_table1..AnotherStudy_table6...(Result ing in many many tables)

C) Put all the studies in one database, in six tables, distinguishing each study by some identifying column in each of the six tables. (resulting in many many rows)

Well, that's my dilema. What are your thoughts?





------------
Martyn Hodgson at 12/20/2001 8:23:02 AM

This is actually a very good example of why you should never be afraid of asking what appears to be a simple (even silly) question. There is actually a lot in this. Firstly, a lot of DBMS's support a syntax of 'CREATE TABLE table LIKE someOtherTable'. This does the DDL bit and an 'insert into .. select from ..' statement takes care of the data.

The problem with this is that if indexes and constraints are copied automatically what names do you give them? The system will usually generate something at random. However, unless you are creating a table for 2 minutes, you really should give indexes and constraints meaningful names. It makes tracking errors a lot easier. In fact, get one error caused by a constraint violation and you'll spend more time finding out what the (randomly named) constraint is doing than you would have spent naming it properly in the first place.

The other side of this is that even temporary tables should be placed and sized with care, unless you are the *only* user of a database with lots of space. I've seen big systems severely affected (spectacularly so on one occasion) by temporary tables that was created and then forgotten about.

So the moral of this is: if you're creating objects (even short lived ones), get the ddl, name your constraints and indexes, and set their physical parameters with care!


------------
Sundar at 12/18/2001 11:57:15 AM

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[Sivakumar]

Mike,

I think some gurus of SQL Server should answer this question. I havent handled so much data in SQL server I have done it oracle and the performance is good. So you should decide on which server you want to go. I have heard that NT is not good to handle more than 200 GB of data (more errors) and its safer to switch on to Unix if your data is going to be more than 200 GB. Its just my suggestion I hope the guru's here will throw more suggestion. You can go for Partition for this so that your performance is not affected.

Regards,
Sivakumar

------------
mike dwyer at 12/22/2001 12:46:55 AM

Well, being new to the SQL server environment, I'm a little worried about having some massive tables with potentially 30 million or so rows of data. They would also like to have the data archived so that they can go back to a set of data that was 3 or four years old.


------------
Sivakumar at 12/21/2001 3:21:05 AM

Mike,

My thought its all about your designing the tables. I would suggest that you first normalize your tables which you are going to create and dont create a study(table) for each appraiser when they need any information. Instead create the necessary sql queries with bind variables so that once the appraiser selected his information request they can have there information from your tables and display it any frontend you want. That way you avoid unneccesary load on your server. (in my personal opinion i will prefer ORACLE over SQL SERVER since you said that your data will be growing maybe you can decide on it in future too). Your PERFORMANCE will be based on how you are going to design the tables so be careful when designing the system.

Regarding your query about multiple database i dont know how the performace will be affected or improved by that option since i havent work much on SQL SERVER. I am not sure about the architecture of SQL SERVER so i cant speak much on it.

Hope someone answer your query regarding that.

Regards,
Sivakumar

------------
mike dwyer at 12/20/2001 2:52:57 PM


Thanks. Yes, that's exactly the problem. You can copy the structure as a script, but as I see it, you have to go in manually and change the "temporary" names. I was hoping there was some magic thing that would regenerate these unique names on the fly for me.

Here's the real question that I need to handle and maybe you guys could give me some insight. I don't know how SQL will handle this best. Here goes...

I've designed a real estate appraisal program for County appraisers. It's an Access upsizing project I've got here, BTW. I'll keep the details to a minimum, for simplicity. I allow each appraiser to create their own "Study" of selected properties (properties as in Homes, not computer-type properties). They enter selection parameters to define a geographical area they want to study. My system then creates computer generated appraisals for each property in the area based on home sales near these properties. That's the gist of it.

Now, the SQL questions...
Each one of these "studies" is it's own unit, so to speak. Each study creates 6 diferent tables of information. These tables hold the characteristics of the properties to study, the comparable sales (comps), adjustments to the sales price of these sales, etc. In Access, I put these six tables in one MDB file, shareable to everyone on the network. This way, a group of Appraisers can look at the results of this study simultaneously. By having each study in it's own MDB file, this study functions as its own "Unit" and can be "Opened" much like a document or a spreadsheet.

Now, the problem: The studies can be quite large. In orange county, Calif., for instance, there are 600,000 properties to study. This could result in one of the tables having as many as 12 Million rows. Or they can be quite small (like 1,000 properties). And with 100 users creating their own studies, there can be many, many, studies. In any event, I need to upsize this to SQL Server. The question is how to store these studies.
Do I...
A) create a Database for each study, as I do in Access? (many many databases)
B) put them in one database, but with different table names
e.g. Mystudy_table1..Mystudy_table6, AnotherStudy_table1..AnotherStudy_table6...(Result ing in many many tables)

C) Put all the studies in one database, in six tables, distinguishing each study by some identifying column in each of the six tables. (resulting in many many rows)

Well, that's my dilema. What are your thoughts?





------------
Martyn Hodgson at 12/20/2001 8:23:02 AM

This is actually a very good example of why you should never be afraid of asking what appears to be a simple (even silly) question. There is actually a lot in this. Firstly, a lot of DBMS's support a syntax of 'CREATE TABLE table LIKE someOtherTable'. This does the DDL bit and an 'insert into .. select from ..' statement takes care of the data.

The problem with this is that if indexes and constraints are copied automatically what names do you give them? The system will usually generate something at random. However, unless you are creating a table for 2 minutes, you really should give indexes and constraints meaningful names. It makes tracking errors a lot easier. In fact, get one error caused by a constraint violation and you'll spend more time finding out what the (randomly named) constraint is doing than you would have spent naming it properly in the first place.

The other side of this is that even temporary tables should be placed and sized with care, unless you are the *only* user of a database with lots of space. I've seen big systems severely affected (spectacularly so on one occasion) by temporary tables that was created and then forgotten about.

So the moral of this is: if you're creating objects (even short lived ones), get the ddl, name your constraints and indexes, and set their physical parameters with care!


------------
Sundar at 12/18/2001 11:57:15 AM

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[Bill]

Mike --
I can't answer your first question about defining tables quickly and easily with all indexes, constraints, triggers, etc. I agree with Martyn that you should give names to constraints and such that are meaningful - read "manual". With this much data you should consider vertically partitioning into similar tables with check constraints set appropriately. My guess is that these may not even be local partitions, but distributed partitions (multiple SQL servers). Take a look at this article in SQL server magazine: www.sqlmag.com/Articles/Print.cfm?ArticleID=9086

I'm actually kind of surprised that you would be upsizing to this grand scale from a lowly Access db. I know at least in Access 97 there was a limit of 1GB of data or 1 million records, whichever came first. Good luck!!

Bill


------------
Sivakumar at 12/24/2001 12:31:38 AM

Mike,

I think some gurus of SQL Server should answer this question. I havent handled so much data in SQL server I have done it oracle and the performance is good. So you should decide on which server you want to go. I have heard that NT is not good to handle more than 200 GB of data (more errors) and its safer to switch on to Unix if your data is going to be more than 200 GB. Its just my suggestion I hope the guru's here will throw more suggestion. You can go for Partition for this so that your performance is not affected.

Regards,
Sivakumar

------------
mike dwyer at 12/22/2001 12:46:55 AM

Well, being new to the SQL server environment, I'm a little worried about having some massive tables with potentially 30 million or so rows of data. They would also like to have the data archived so that they can go back to a set of data that was 3 or four years old.


------------
Sivakumar at 12/21/2001 3:21:05 AM

Mike,

My thought its all about your designing the tables. I would suggest that you first normalize your tables which you are going to create and dont create a study(table) for each appraiser when they need any information. Instead create the necessary sql queries with bind variables so that once the appraiser selected his information request they can have there information from your tables and display it any frontend you want. That way you avoid unneccesary load on your server. (in my personal opinion i will prefer ORACLE over SQL SERVER since you said that your data will be growing maybe you can decide on it in future too). Your PERFORMANCE will be based on how you are going to design the tables so be careful when designing the system.

Regarding your query about multiple database i dont know how the performace will be affected or improved by that option since i havent work much on SQL SERVER. I am not sure about the architecture of SQL SERVER so i cant speak much on it.

Hope someone answer your query regarding that.

Regards,
Sivakumar

------------
mike dwyer at 12/20/2001 2:52:57 PM


Thanks. Yes, that's exactly the problem. You can copy the structure as a script, but as I see it, you have to go in manually and change the "temporary" names. I was hoping there was some magic thing that would regenerate these unique names on the fly for me.

Here's the real question that I need to handle and maybe you guys could give me some insight. I don't know how SQL will handle this best. Here goes...

I've designed a real estate appraisal program for County appraisers. It's an Access upsizing project I've got here, BTW. I'll keep the details to a minimum, for simplicity. I allow each appraiser to create their own "Study" of selected properties (properties as in Homes, not computer-type properties). They enter selection parameters to define a geographical area they want to study. My system then creates computer generated appraisals for each property in the area based on home sales near these properties. That's the gist of it.

Now, the SQL questions...
Each one of these "studies" is it's own unit, so to speak. Each study creates 6 diferent tables of information. These tables hold the characteristics of the properties to study, the comparable sales (comps), adjustments to the sales price of these sales, etc. In Access, I put these six tables in one MDB file, shareable to everyone on the network. This way, a group of Appraisers can look at the results of this study simultaneously. By having each study in it's own MDB file, this study functions as its own "Unit" and can be "Opened" much like a document or a spreadsheet.

Now, the problem: The studies can be quite large. In orange county, Calif., for instance, there are 600,000 properties to study. This could result in one of the tables having as many as 12 Million rows. Or they can be quite small (like 1,000 properties). And with 100 users creating their own studies, there can be many, many, studies. In any event, I need to upsize this to SQL Server. The question is how to store these studies.
Do I...
A) create a Database for each study, as I do in Access? (many many databases)
B) put them in one database, but with different table names
e.g. Mystudy_table1..Mystudy_table6, AnotherStudy_table1..AnotherStudy_table6...(Result ing in many many tables)

C) Put all the studies in one database, in six tables, distinguishing each study by some identifying column in each of the six tables. (resulting in many many rows)

Well, that's my dilema. What are your thoughts?





------------
Martyn Hodgson at 12/20/2001 8:23:02 AM

This is actually a very good example of why you should never be afraid of asking what appears to be a simple (even silly) question. There is actually a lot in this. Firstly, a lot of DBMS's support a syntax of 'CREATE TABLE table LIKE someOtherTable'. This does the DDL bit and an 'insert into .. select from ..' statement takes care of the data.

The problem with this is that if indexes and constraints are copied automatically what names do you give them? The system will usually generate something at random. However, unless you are creating a table for 2 minutes, you really should give indexes and constraints meaningful names. It makes tracking errors a lot easier. In fact, get one error caused by a constraint violation and you'll spend more time finding out what the (randomly named) constraint is doing than you would have spent naming it properly in the first place.

The other side of this is that even temporary tables should be placed and sized with care, unless you are the *only* user of a database with lots of space. I've seen big systems severely affected (spectacularly so on one occasion) by temporary tables that was created and then forgotten about.

So the moral of this is: if you're creating objects (even short lived ones), get the ddl, name your constraints and indexes, and set their physical parameters with care!


------------
Sundar at 12/18/2001 11:57:15 AM

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[Bill Russell]

Mike,

Rather than suggest a solution I'll just offer some observations.

1. If access can handle it SQL Server will do a much better job when it comes to large amounts of data.
2. Seperate databases is both a blessing and a curse. Its a blessing in that the security issues of having the data seperated into "logical" units is simplified and there is no danger of one appraiser stepping on another. Its a curse(but only a small one) in that you have to administer more than one database.
3. Getting summary data from multiple databases is not really a big deal in SQL Server as you can join tables accross databases(indeed across servers) or use Native SQL server tools such as DTS to get your data from many sources.
4. VLDB(Very Large databases) present some unique problems in terms of resources and maintenance that are not necessarily true for smaller databases.

Good Luck

Bill


------------
mike dwyer at 12/22/2001 12:46:55 AM

Well, being new to the SQL server environment, I'm a little worried about having some massive tables with potentially 30 million or so rows of data. They would also like to have the data archived so that they can go back to a set of data that was 3 or four years old.


------------
Sivakumar at 12/21/2001 3:21:05 AM

Mike,

My thought its all about your designing the tables. I would suggest that you first normalize your tables which you are going to create and dont create a study(table) for each appraiser when they need any information. Instead create the necessary sql queries with bind variables so that once the appraiser selected his information request they can have there information from your tables and display it any frontend you want. That way you avoid unneccesary load on your server. (in my personal opinion i will prefer ORACLE over SQL SERVER since you said that your data will be growing maybe you can decide on it in future too). Your PERFORMANCE will be based on how you are going to design the tables so be careful when designing the system.

Regarding your query about multiple database i dont know how the performace will be affected or improved by that option since i havent work much on SQL SERVER. I am not sure about the architecture of SQL SERVER so i cant speak much on it.

Hope someone answer your query regarding that.

Regards,
Sivakumar

------------
mike dwyer at 12/20/2001 2:52:57 PM


Thanks. Yes, that's exactly the problem. You can copy the structure as a script, but as I see it, you have to go in manually and change the "temporary" names. I was hoping there was some magic thing that would regenerate these unique names on the fly for me.

Here's the real question that I need to handle and maybe you guys could give me some insight. I don't know how SQL will handle this best. Here goes...

I've designed a real estate appraisal program for County appraisers. It's an Access upsizing project I've got here, BTW. I'll keep the details to a minimum, for simplicity. I allow each appraiser to create their own "Study" of selected properties (properties as in Homes, not computer-type properties). They enter selection parameters to define a geographical area they want to study. My system then creates computer generated appraisals for each property in the area based on home sales near these properties. That's the gist of it.

Now, the SQL questions...
Each one of these "studies" is it's own unit, so to speak. Each study creates 6 diferent tables of information. These tables hold the characteristics of the properties to study, the comparable sales (comps), adjustments to the sales price of these sales, etc. In Access, I put these six tables in one MDB file, shareable to everyone on the network. This way, a group of Appraisers can look at the results of this study simultaneously. By having each study in it's own MDB file, this study functions as its own "Unit" and can be "Opened" much like a document or a spreadsheet.

Now, the problem: The studies can be quite large. In orange county, Calif., for instance, there are 600,000 properties to study. This could result in one of the tables having as many as 12 Million rows. Or they can be quite small (like 1,000 properties). And with 100 users creating their own studies, there can be many, many, studies. In any event, I need to upsize this to SQL Server. The question is how to store these studies.
Do I...
A) create a Database for each study, as I do in Access? (many many databases)
B) put them in one database, but with different table names
e.g. Mystudy_table1..Mystudy_table6, AnotherStudy_table1..AnotherStudy_table6...(Result ing in many many tables)

C) Put all the studies in one database, in six tables, distinguishing each study by some identifying column in each of the six tables. (resulting in many many rows)

Well, that's my dilema. What are your thoughts?





------------
Martyn Hodgson at 12/20/2001 8:23:02 AM

This is actually a very good example of why you should never be afraid of asking what appears to be a simple (even silly) question. There is actually a lot in this. Firstly, a lot of DBMS's support a syntax of 'CREATE TABLE table LIKE someOtherTable'. This does the DDL bit and an 'insert into .. select from ..' statement takes care of the data.

The problem with this is that if indexes and constraints are copied automatically what names do you give them? The system will usually generate something at random. However, unless you are creating a table for 2 minutes, you really should give indexes and constraints meaningful names. It makes tracking errors a lot easier. In fact, get one error caused by a constraint violation and you'll spend more time finding out what the (randomly named) constraint is doing than you would have spent naming it properly in the first place.

The other side of this is that even temporary tables should be placed and sized with care, unless you are the *only* user of a database with lots of space. I've seen big systems severely affected (spectacularly so on one occasion) by temporary tables that was created and then forgotten about.

So the moral of this is: if you're creating objects (even short lived ones), get the ddl, name your constraints and indexes, and set their physical parameters with care!


------------
Sundar at 12/18/2001 11:57:15 AM

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[Dano]

Hey, I'm wondering if you might want to use the "Model" database.
Let me give a very brief intro on it's purpose. (Read BOL for details)

"A repository of objects that you want included in every new database you create!"

You may want a "model" of your tables with appropriate constraints, etc.
When you created a new dB, the tables, constraints would exists as you wish..

Drawback: same as advantage, every object would exist in every new DB.
Maintenance of table changes would be a bit trickier, but not if you script them well...

Thoughts?

Dano Smith





------------
Bill at 12/26/2001 5:47:00 PM

Mike --
I can't answer your first question about defining tables quickly and easily with all indexes, constraints, triggers, etc. I agree with Martyn that you should give names to constraints and such that are meaningful - read "manual". With this much data you should consider vertically partitioning into similar tables with check constraints set appropriately. My guess is that these may not even be local partitions, but distributed partitions (multiple SQL servers). Take a look at this article in SQL server magazine: www.sqlmag.com/Articles/Print.cfm?ArticleID=9086

I'm actually kind of surprised that you would be upsizing to this grand scale from a lowly Access db. I know at least in Access 97 there was a limit of 1GB of data or 1 million records, whichever came first. Good luck!!

Bill


------------
Sivakumar at 12/24/2001 12:31:38 AM

Mike,

I think some gurus of SQL Server should answer this question. I havent handled so much data in SQL server I have done it oracle and the performance is good. So you should decide on which server you want to go. I have heard that NT is not good to handle more than 200 GB of data (more errors) and its safer to switch on to Unix if your data is going to be more than 200 GB. Its just my suggestion I hope the guru's here will throw more suggestion. You can go for Partition for this so that your performance is not affected.

Regards,
Sivakumar

------------
mike dwyer at 12/22/2001 12:46:55 AM

Well, being new to the SQL server environment, I'm a little worried about having some massive tables with potentially 30 million or so rows of data. They would also like to have the data archived so that they can go back to a set of data that was 3 or four years old.


------------
Sivakumar at 12/21/2001 3:21:05 AM

Mike,

My thought its all about your designing the tables. I would suggest that you first normalize your tables which you are going to create and dont create a study(table) for each appraiser when they need any information. Instead create the necessary sql queries with bind variables so that once the appraiser selected his information request they can have there information from your tables and display it any frontend you want. That way you avoid unneccesary load on your server. (in my personal opinion i will prefer ORACLE over SQL SERVER since you said that your data will be growing maybe you can decide on it in future too). Your PERFORMANCE will be based on how you are going to design the tables so be careful when designing the system.

Regarding your query about multiple database i dont know how the performace will be affected or improved by that option since i havent work much on SQL SERVER. I am not sure about the architecture of SQL SERVER so i cant speak much on it.

Hope someone answer your query regarding that.

Regards,
Sivakumar

------------
mike dwyer at 12/20/2001 2:52:57 PM


Thanks. Yes, that's exactly the problem. You can copy the structure as a script, but as I see it, you have to go in manually and change the "temporary" names. I was hoping there was some magic thing that would regenerate these unique names on the fly for me.

Here's the real question that I need to handle and maybe you guys could give me some insight. I don't know how SQL will handle this best. Here goes...

I've designed a real estate appraisal program for County appraisers. It's an Access upsizing project I've got here, BTW. I'll keep the details to a minimum, for simplicity. I allow each appraiser to create their own "Study" of selected properties (properties as in Homes, not computer-type properties). They enter selection parameters to define a geographical area they want to study. My system then creates computer generated appraisals for each property in the area based on home sales near these properties. That's the gist of it.

Now, the SQL questions...
Each one of these "studies" is it's own unit, so to speak. Each study creates 6 diferent tables of information. These tables hold the characteristics of the properties to study, the comparable sales (comps), adjustments to the sales price of these sales, etc. In Access, I put these six tables in one MDB file, shareable to everyone on the network. This way, a group of Appraisers can look at the results of this study simultaneously. By having each study in it's own MDB file, this study functions as its own "Unit" and can be "Opened" much like a document or a spreadsheet.

Now, the problem: The studies can be quite large. In orange county, Calif., for instance, there are 600,000 properties to study. This could result in one of the tables having as many as 12 Million rows. Or they can be quite small (like 1,000 properties). And with 100 users creating their own studies, there can be many, many, studies. In any event, I need to upsize this to SQL Server. The question is how to store these studies.
Do I...
A) create a Database for each study, as I do in Access? (many many databases)
B) put them in one database, but with different table names
e.g. Mystudy_table1..Mystudy_table6, AnotherStudy_table1..AnotherStudy_table6...(Result ing in many many tables)

C) Put all the studies in one database, in six tables, distinguishing each study by some identifying column in each of the six tables. (resulting in many many rows)

Well, that's my dilema. What are your thoughts?





------------
Martyn Hodgson at 12/20/2001 8:23:02 AM

This is actually a very good example of why you should never be afraid of asking what appears to be a simple (even silly) question. There is actually a lot in this. Firstly, a lot of DBMS's support a syntax of 'CREATE TABLE table LIKE someOtherTable'. This does the DDL bit and an 'insert into .. select from ..' statement takes care of the data.

The problem with this is that if indexes and constraints are copied automatically what names do you give them? The system will usually generate something at random. However, unless you are creating a table for 2 minutes, you really should give indexes and constraints meaningful names. It makes tracking errors a lot easier. In fact, get one error caused by a constraint violation and you'll spend more time finding out what the (randomly named) constraint is doing than you would have spent naming it properly in the first place.

The other side of this is that even temporary tables should be placed and sized with care, unless you are the *only* user of a database with lots of space. I've seen big systems severely affected (spectacularly so on one occasion) by temporary tables that was created and then forgotten about.

So the moral of this is: if you're creating objects (even short lived ones), get the ddl, name your constraints and indexes, and set their physical parameters with care!


------------
Sundar at 12/18/2001 11:57:15 AM

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[mike dwyer]

Well you'd be surprised how much you can get out of a lowly MDB file. 1Gb per MDB. I have all major tables in their own MDB file. This gives me more space but at a cost of R.I., etc. BTW, can you explain what you mean by Vertical Partition? Remember, I AM a newbie, afterall.


------------
Bill at 12/26/2001 5:47:00 PM

Mike --
I can't answer your first question about defining tables quickly and easily with all indexes, constraints, triggers, etc. I agree with Martyn that you should give names to constraints and such that are meaningful - read "manual". With this much data you should consider vertically partitioning into similar tables with check constraints set appropriately. My guess is that these may not even be local partitions, but distributed partitions (multiple SQL servers). Take a look at this article in SQL server magazine: www.sqlmag.com/Articles/Print.cfm?ArticleID=9086

I'm actually kind of surprised that you would be upsizing to this grand scale from a lowly Access db. I know at least in Access 97 there was a limit of 1GB of data or 1 million records, whichever came first. Good luck!!

Bill


------------
Sivakumar at 12/24/2001 12:31:38 AM

Mike,

I think some gurus of SQL Server should answer this question. I havent handled so much data in SQL server I have done it oracle and the performance is good. So you should decide on which server you want to go. I have heard that NT is not good to handle more than 200 GB of data (more errors) and its safer to switch on to Unix if your data is going to be more than 200 GB. Its just my suggestion I hope the guru's here will throw more suggestion. You can go for Partition for this so that your performance is not affected.

Regards,
Sivakumar

------------
mike dwyer at 12/22/2001 12:46:55 AM

Well, being new to the SQL server environment, I'm a little worried about having some massive tables with potentially 30 million or so rows of data. They would also like to have the data archived so that they can go back to a set of data that was 3 or four years old.


------------
Sivakumar at 12/21/2001 3:21:05 AM

Mike,

My thought its all about your designing the tables. I would suggest that you first normalize your tables which you are going to create and dont create a study(table) for each appraiser when they need any information. Instead create the necessary sql queries with bind variables so that once the appraiser selected his information request they can have there information from your tables and display it any frontend you want. That way you avoid unneccesary load on your server. (in my personal opinion i will prefer ORACLE over SQL SERVER since you said that your data will be growing maybe you can decide on it in future too). Your PERFORMANCE will be based on how you are going to design the tables so be careful when designing the system.

Regarding your query about multiple database i dont know how the performace will be affected or improved by that option since i havent work much on SQL SERVER. I am not sure about the architecture of SQL SERVER so i cant speak much on it.

Hope someone answer your query regarding that.

Regards,
Sivakumar

------------
mike dwyer at 12/20/2001 2:52:57 PM


Thanks. Yes, that's exactly the problem. You can copy the structure as a script, but as I see it, you have to go in manually and change the "temporary" names. I was hoping there was some magic thing that would regenerate these unique names on the fly for me.

Here's the real question that I need to handle and maybe you guys could give me some insight. I don't know how SQL will handle this best. Here goes...

I've designed a real estate appraisal program for County appraisers. It's an Access upsizing project I've got here, BTW. I'll keep the details to a minimum, for simplicity. I allow each appraiser to create their own "Study" of selected properties (properties as in Homes, not computer-type properties). They enter selection parameters to define a geographical area they want to study. My system then creates computer generated appraisals for each property in the area based on home sales near these properties. That's the gist of it.

Now, the SQL questions...
Each one of these "studies" is it's own unit, so to speak. Each study creates 6 diferent tables of information. These tables hold the characteristics of the properties to study, the comparable sales (comps), adjustments to the sales price of these sales, etc. In Access, I put these six tables in one MDB file, shareable to everyone on the network. This way, a group of Appraisers can look at the results of this study simultaneously. By having each study in it's own MDB file, this study functions as its own "Unit" and can be "Opened" much like a document or a spreadsheet.

Now, the problem: The studies can be quite large. In orange county, Calif., for instance, there are 600,000 properties to study. This could result in one of the tables having as many as 12 Million rows. Or they can be quite small (like 1,000 properties). And with 100 users creating their own studies, there can be many, many, studies. In any event, I need to upsize this to SQL Server. The question is how to store these studies.
Do I...
A) create a Database for each study, as I do in Access? (many many databases)
B) put them in one database, but with different table names
e.g. Mystudy_table1..Mystudy_table6, AnotherStudy_table1..AnotherStudy_table6...(Result ing in many many tables)

C) Put all the studies in one database, in six tables, distinguishing each study by some identifying column in each of the six tables. (resulting in many many rows)

Well, that's my dilema. What are your thoughts?





------------
Martyn Hodgson at 12/20/2001 8:23:02 AM

This is actually a very good example of why you should never be afraid of asking what appears to be a simple (even silly) question. There is actually a lot in this. Firstly, a lot of DBMS's support a syntax of 'CREATE TABLE table LIKE someOtherTable'. This does the DDL bit and an 'insert into .. select from ..' statement takes care of the data.

The problem with this is that if indexes and constraints are copied automatically what names do you give them? The system will usually generate something at random. However, unless you are creating a table for 2 minutes, you really should give indexes and constraints meaningful names. It makes tracking errors a lot easier. In fact, get one error caused by a constraint violation and you'll spend more time finding out what the (randomly named) constraint is doing than you would have spent naming it properly in the first place.

The other side of this is that even temporary tables should be placed and sized with care, unless you are the *only* user of a database with lots of space. I've seen big systems severely affected (spectacularly so on one occasion) by temporary tables that was created and then forgotten about.

So the moral of this is: if you're creating objects (even short lived ones), get the ddl, name your constraints and indexes, and set their physical parameters with care!


------------
Sundar at 12/18/2001 11:57:15 AM

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?

[mike dwyer]

Thanks for the info, Bill.
Actually, there will need to be no cross-referencing between databases anyway. They really are separate, individual units. It might be a hassle if I alter the table structures in the future to provide more capabilities. In which case I would have to alter all pre-existing Databases to bring them up to "code", so to speak.


------------
Bill Russell at 12/26/2001 6:18:34 PM

Mike,

Rather than suggest a solution I'll just offer some observations.

1. If access can handle it SQL Server will do a much better job when it comes to large amounts of data.
2. Seperate databases is both a blessing and a curse. Its a blessing in that the security issues of having the data seperated into "logical" units is simplified and there is no danger of one appraiser stepping on another. Its a curse(but only a small one) in that you have to administer more than one database.
3. Getting summary data from multiple databases is not really a big deal in SQL Server as you can join tables accross databases(indeed across servers) or use Native SQL server tools such as DTS to get your data from many sources.
4. VLDB(Very Large databases) present some unique problems in terms of resources and maintenance that are not necessarily true for smaller databases.

Good Luck

Bill


------------
mike dwyer at 12/22/2001 12:46:55 AM

Well, being new to the SQL server environment, I'm a little worried about having some massive tables with potentially 30 million or so rows of data. They would also like to have the data archived so that they can go back to a set of data that was 3 or four years old.


------------
Sivakumar at 12/21/2001 3:21:05 AM

Mike,

My thought its all about your designing the tables. I would suggest that you first normalize your tables which you are going to create and dont create a study(table) for each appraiser when they need any information. Instead create the necessary sql queries with bind variables so that once the appraiser selected his information request they can have there information from your tables and display it any frontend you want. That way you avoid unneccesary load on your server. (in my personal opinion i will prefer ORACLE over SQL SERVER since you said that your data will be growing maybe you can decide on it in future too). Your PERFORMANCE will be based on how you are going to design the tables so be careful when designing the system.

Regarding your query about multiple database i dont know how the performace will be affected or improved by that option since i havent work much on SQL SERVER. I am not sure about the architecture of SQL SERVER so i cant speak much on it.

Hope someone answer your query regarding that.

Regards,
Sivakumar

------------
mike dwyer at 12/20/2001 2:52:57 PM


Thanks. Yes, that's exactly the problem. You can copy the structure as a script, but as I see it, you have to go in manually and change the "temporary" names. I was hoping there was some magic thing that would regenerate these unique names on the fly for me.

Here's the real question that I need to handle and maybe you guys could give me some insight. I don't know how SQL will handle this best. Here goes...

I've designed a real estate appraisal program for County appraisers. It's an Access upsizing project I've got here, BTW. I'll keep the details to a minimum, for simplicity. I allow each appraiser to create their own "Study" of selected properties (properties as in Homes, not computer-type properties). They enter selection parameters to define a geographical area they want to study. My system then creates computer generated appraisals for each property in the area based on home sales near these properties. That's the gist of it.

Now, the SQL questions...
Each one of these "studies" is it's own unit, so to speak. Each study creates 6 diferent tables of information. These tables hold the characteristics of the properties to study, the comparable sales (comps), adjustments to the sales price of these sales, etc. In Access, I put these six tables in one MDB file, shareable to everyone on the network. This way, a group of Appraisers can look at the results of this study simultaneously. By having each study in it's own MDB file, this study functions as its own "Unit" and can be "Opened" much like a document or a spreadsheet.

Now, the problem: The studies can be quite large. In orange county, Calif., for instance, there are 600,000 properties to study. This could result in one of the tables having as many as 12 Million rows. Or they can be quite small (like 1,000 properties). And with 100 users creating their own studies, there can be many, many, studies. In any event, I need to upsize this to SQL Server. The question is how to store these studies.
Do I...
A) create a Database for each study, as I do in Access? (many many databases)
B) put them in one database, but with different table names
e.g. Mystudy_table1..Mystudy_table6, AnotherStudy_table1..AnotherStudy_table6...(Result ing in many many tables)

C) Put all the studies in one database, in six tables, distinguishing each study by some identifying column in each of the six tables. (resulting in many many rows)

Well, that's my dilema. What are your thoughts?





------------
Martyn Hodgson at 12/20/2001 8:23:02 AM

This is actually a very good example of why you should never be afraid of asking what appears to be a simple (even silly) question. There is actually a lot in this. Firstly, a lot of DBMS's support a syntax of 'CREATE TABLE table LIKE someOtherTable'. This does the DDL bit and an 'insert into .. select from ..' statement takes care of the data.

The problem with this is that if indexes and constraints are copied automatically what names do you give them? The system will usually generate something at random. However, unless you are creating a table for 2 minutes, you really should give indexes and constraints meaningful names. It makes tracking errors a lot easier. In fact, get one error caused by a constraint violation and you'll spend more time finding out what the (randomly named) constraint is doing than you would have spent naming it properly in the first place.

The other side of this is that even temporary tables should be placed and sized with care, unless you are the *only* user of a database with lots of space. I've seen big systems severely affected (spectacularly so on one occasion) by temporary tables that was created and then forgotten about.

So the moral of this is: if you're creating objects (even short lived ones), get the ddl, name your constraints and indexes, and set their physical parameters with care!


------------
Sundar at 12/18/2001 11:57:15 AM

Doh! Didn't read the question very well!


------------
Ray Miao at 12/18/2001 9:38:25 AM

But 'select into' doesn't create any constraints nor indexes.


------------
Sundar at 12/18/2001 7:37:10 AM

Do a SELECT INTO

This will create a new table using the same outline as the original table and will copy all the data as well.


------------
mike dwyer at 12/18/2001 1:47:39 AM


How does one create a new table from an existing table in the same database quickly? I know I can copy the script to create a table and change the name, but often there are other things like constraint names which need to be renamed as well. Is there a quick 'n easy way to do this?