Creating a Table

[Ritchie]

How do you create a new table from a SELECT statement of another table
using MS SQL Server. This is part of a distributed database topic for
university. Unfortunately I can only seem to get the new table created
in Oracle and not MS.

[Simon Hayes]

Re: Creating a Table


"Ritchie" <vartegrich@aol .com> wrote in message
news:634b2f3c.0 402271338.74dac 87e@posting.goo gle.com...[color=blue]
> How do you create a new table from a SELECT statement of another table
> using MS SQL Server. This is part of a distributed database topic for
> university. Unfortunately I can only seem to get the new table created
> in Oracle and not MS.[/color]

I'm not completely sure, but are you looking for this?

select *
into dbo.NewTable
from dbo.ExistingTab le
where ...

If you want to copy only the structure, you can use "where 1=2". But in both
cases, you get only the columns - no keys, no constraints, no triggers etc.
If you need to copy those as well, then you'll have to recreate them after
creating the new table.

Simon

[VARTEGRICH]

Re: Creating a Table

Thanks Simon, if you are not sure, that leaves me high and dry. I'll try to
explain the objective and see if that helps at all. We have to locate certain
records in a particular table (e.g. locate records by city='London') then
relocate all these records into a new table.

so I guess using
SELECT * FROM oldtable WHERE city='London'
works to gather the necessary records.
We tried CREATE TABLE newtable AS, but this of course is for oracle. So what I
need is the SQL to permit this in MS-SQL

[Simon Hayes]

Re: Creating a Table


"VARTEGRICH " <vartegrich@aol .com> wrote in message
news:2004022718 2545.20278.0000 0483@mb-m27.aol.com...[color=blue]
> Thanks Simon, if you are not sure, that leaves me high and dry. I'll try[/color]
to[color=blue]
> explain the objective and see if that helps at all. We have to locate[/color]
certain[color=blue]
> records in a particular table (e.g. locate records by city='London') then
> relocate all these records into a new table.
>
> so I guess using
> SELECT * FROM oldtable WHERE city='London'
> works to gather the necessary records.
> We tried CREATE TABLE newtable AS, but this of course is for oracle. So[/color]
what I[color=blue]
> need is the SQL to permit this in MS-SQL
>[/color]

Perhaps my previous post wasn't very clear - you can do this:

select *
into dbo.NewTable
from dbo.OldTable
where city = 'London'

That will create NewTable and insert the records you require, but as I said,
it will have no keys, constraints, indexes etc. If you need those, then you
can use Enterprise Manager to generate the CREATE TABLE script for OldTable,
modify the script as needed (change the table name, constraint names etc),
run it to create NewTable and then do this:

insert into dbo.NewTable
select *
from dbo.OldTable
where city = 'London'

Simon

[VARTEGRICH]

Re: Creating a Table

Cheers Simon,
works a treat.
Pity the tutors don't know how to do this though.
Would make life a lot easier.
Thanks Again