Creating tables from other tables

Hi,

I have a table of data and would like to make an exact copy of the table (structure and data).

Are there any ways to do this without having to first run a create statement (that would have been based on the original table being manually scripted) and then select insert for the data. The reason being that this initial table may change and that would mean I would have to go and change my create statements. It would be nice to have a blind command:-

create table A based on table B

etc

Thanks

Rhys

Rhys,

The SELECT INTO command may work for you.

here is an example

CREATE Table B ( i int primary key )

INSERT into B values(1)

INSERT into B values(2)

SELECT * INTO A from B -- this creates table A

Karl Tarbet

|||Thanks Karl, that was exactly what I was looking for.

Creating New tables by copying existing table

Hi,
Is there any easy way of copying the structure of a table within a single
database and creating a new table with a new name. I have an app that the
structure of several new tables is close enough that it would save time if I
could create one and copy and recreate a new one under a new name. Then I
would only have to make the changes that are different.
thanks,
hughVery Easy
Select * into NewTableName from OldTableName where 1= 0
Keep in mind that this is just the table structure no keys, constraints or
indexes are created
http://sqlservercode.blogspot.com/
"Hugh O" wrote:

> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
I
> could create one and copy and recreate a new one under a new name. Then I
> would only have to make the changes that are different.
> thanks,
> hugh
>
>|||Script the CREATE TABLE statement and then edit it. You can do that in Query
Analyzer.
David Portas
SQL Server MVP
--
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:%23kGLB6DzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
> I could create one and copy and recreate a new one under a new name. Then
> I would only have to make the changes that are different.
> thanks,
> hugh
>|||Hi
You should consider putting your table definitions under version control in
which case you could use the scripting options on Enterpeise Manager or
Query Analyser to create the file to be edited.
John
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:%23kGLB6DzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
> I could create one and copy and recreate a new one under a new name. Then
> I would only have to make the changes that are different.
> thanks,
> hugh
>|||>> I have an app that the structure of several new tables is close enough th
at it would save time if I could create one and copy and recreate a new one
under a new name. Then I would only have to make the changes that are diffe
rent. <<
Be careful about this. While not a certainity, this is a sign that you
might have split a table on an attributes. For example, someone
recently posted a set of tables where an event had been put into a
temporal table and a separate physical location table and then was
trying to tie them back together to answer queries about the location
and status at a site.|||Thanks SQL, David, John, & Celko
These newsgroups are great. So helpful and shows the diversity and
alternatives so clearly.
hugh
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:%23kGLB6DzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
> I could create one and copy and recreate a new one under a new name. Then
> I would only have to make the changes that are different.
> thanks,
> hugh
>

Creating New Table & Linking to Datas

Hi Everybody,
I have a table as "AdjSummary" with 5 columns. I have to create a
table with 2 columns from the above table. The structure of the table
"AdjSummary" is as below
INVNO ADJUST MODE PAYMENT MODE
10 100 OTH 0 NULL
11 150 OTH 0 NULL
11 100 DET 0 NULL
12 0 NULL 50 CSH
12 50 DET 50 VIS
12 12 DET 0 NULL
My new table should be with 2 columns (INVNO & ADJUST) as below.
INVNO ADJUST
10 100
11 250
12 62
Moreover, I want ADJUST column should be not equal to zero.
Pls help me in creating the table & to link the data to new table.
Thanks for your help in advance.
Regards,
Selvarathinam.Selvarathinam wrote:
> Hi Everybody,
> I have a table as "AdjSummary" with 5 columns. I have to create a
> table with 2 columns from the above table. The structure of the table
> "AdjSummary" is as below
> INVNO ADJUST MODE PAYMENT MODE
> 10 100 OTH 0 NULL
> 11 150 OTH 0 NULL
> 11 100 DET 0 NULL
> 12 0 NULL 50 CSH
> 12 50 DET 50 VIS
> 12 12 DET 0 NULL
> My new table should be with 2 columns (INVNO & ADJUST) as below.
> INVNO ADJUST
> 10 100
> 11 250
> 12 62
> Moreover, I want ADJUST column should be not equal to zero.
> Pls help me in creating the table & to link the data to new table.
> Thanks for your help in advance.
> Regards,
> Selvarathinam.
SELECT INVNO, ADJUST
--INTO MyTable
FROM AdjSummary
WHERE ADJUST <> 0
This will return the records to you in Query Analyzer. If you want to
keep a permament copy of the records, uncomming the INTO line above,
and change "MyTable" to a legitimate table name.|||Tracy McKibben wrote:
> Selvarathinam wrote:
> SELECT INVNO, ADJUST
> --INTO MyTable
> FROM AdjSummary
> WHERE ADJUST <> 0
> This will return the records to you in Query Analyzer. If you want to
> keep a permament copy of the records, uncomming the INTO line above,
> and change "MyTable" to a legitimate table name.
Sorry, make that:
SELECT INVNO, SUM(ADJUST)
--INTO MyTable
FROM AdjSummary
GROUP BY INVNO
HAVING SUM(ADJUST) <> 0

Creating index on two columns

I have a SQL Server 2000 table with the following structure:
TABLE Personal (
UserID (PK),
Name,
Surname,
DateOfBirth,
Citizen,
BranchCode,
RegionCode,
Division
)
and I use the following stored procedure to do a SELECT which I use on
an asp page:
CREATE PROCEDURE dbo.SelectLevel1
AS
BEGIN
SET NOCOUNT ON
SELECT UserID, Name, Surname
FROM Personal
WHERE Citizen = 'Yes'
ORDER BY Surname ASC, Name ASC
END
I'd like to add an index to the Personal table to maximize the
performance of the SELECT statement.
What index should I create on this table?
My first thought was to index the Name and Surname columns, but how?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Andy,
Yes Surname and Name columns might be good ones to use. Is this a
commonly executed query? I would also look at placing an index on the
Citizen column - how selective is that column?
Your best bet is to try different indexing and look at how efficient
each of them are. Try three or four different approaches with Surname,
Name, Citizen.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Andy wrote:
> I have a SQL Server 2000 table with the following structure:
> TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
> )
> and I use the following stored procedure to do a SELECT which I use on
> an asp page:
> CREATE PROCEDURE dbo.SelectLevel1
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
> END
> I'd like to add an index to the Personal table to maximize the
> performance of the SELECT statement.
> What index should I create on this table?
> My first thought was to index the Name and Surname columns, but how?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||On Fri, 21 Jan 2005 03:19:54 -0800, Andy wrote:
>I have a SQL Server 2000 table with the following structure:
>TABLE Personal (
> UserID (PK),
> Name,
> Surname,
> DateOfBirth,
> Citizen,
> BranchCode,
> RegionCode,
> Division
>)
>and I use the following stored procedure to do a SELECT which I use on
>an asp page:
>CREATE PROCEDURE dbo.SelectLevel1
>AS
>BEGIN
> SET NOCOUNT ON
> SELECT UserID, Name, Surname
> FROM Personal
> WHERE Citizen = 'Yes'
> ORDER BY Surname ASC, Name ASC
>END
>I'd like to add an index to the Personal table to maximize the
>performance of the SELECT statement.
>What index should I create on this table?
>My first thought was to index the Name and Surname columns, but how?
Hi Andy,
That index would only be useful if you would make it the clustered index
and if you define the columns in the order (Surname, Name) - not the other
way aroound.
If it's a nonclustered index, simply scanning the table (or clustered
index), then sorting the rows with the correct Citizen value would be
faster than using the index on (Surname, Name), so SQL Server should
choose that execution plan.
It would be useful if you'd make it into a covering index. Assuming that
UserID is the clustered index (for the PK), your best index for this
specific query would be (Citizen, Surname, Name) - and (Surname, Name,
Citizen) would also be quite good.
If most of the rows in Personal have Citizen not equal to 'Yes', then you
might also gain by using an index on Citizen only. But only if this index
would actually discard almost all rows from your table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)