Create a table using a Query

Hello,
With Oracle, we can create a table as following :
CREATE TABLE tab_name
AS SELECT
col1, col2...
from tab1, tab2
where ...
With MSSQL, can we do the same thing ?The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list.

I suggest you to refer to books online for more information.|||Thanks a lot|||No mention, keep in touch with BOL:)

Create a table from select Queries.

Hi,

I wanted to know a query which will create a final result table from a combination of select queries.

The select query is like :
1. select col1 , col2 , null from table1
2. select null , col2 , null from table2
3. select null , null , col3 from table 3.

null are inserted as i wanted a single select query which will merge all the columns from all the tables and finally create a result table.

Thanks in advance.select *
into resulttable
from (
select col1 as col1,
col2 as col2,
null as col3
from table1
union all
select null ,
col2 ,
null
from table2
union all
select null ,
null ,
col3
from table3
) as tm

Counter in a select statement

How do I get a counter in a select statement output
Say if I do
Select counter, col1, col2 from table1
The output should be
Counter Col1 Col2
1 val11 val21
2 val12 val22
3 val13 val23
4 val14 val24See this thread from yesterday
http://groups.google.com/group/micr...1eb03a01b9a15af
<balacr@.gmail.com> wrote in message
news:1127282527.952182.230990@.g14g2000cwa.googlegroups.com...
> How do I get a counter in a select statement output
>
> Say if I do
> Select counter, col1, col2 from table1
>
> The output should be
>
> Counter Col1 Col2
> 1 val11 val21
> 2 val12 val22
> 3 val13 val23
> 4 val14 val24
>|||Hi,
this is quite easy, can also be found under:
http://support.microsoft.com/defaul...b;EN-US;q186133
CREATE TABLE cols
(
col1 varchar(20),
col2 varchar(20)
)
INSERt INTO cols (col1,col2)
SELECT 'val11','val21'
INSERt INTO cols (col1,col2)
SELECT 'val12','val22'
INSERt INTO cols (col1,col2)
SELECT 'val13','val23'
INSERt INTO cols (col1,col2)
SELECT 'val14','val24'
select rank=count(*), c1.col1, c1.col2
from cols c1, cols c2
where c1.col1 + c1.col2 >= c2.col1 + c2.col2
group by c1.col1, c1.col2
order by 1
DROP Table cols
HTH, Jens Suessmeyer.|||Jens,
This will not give the expected result if there are 2 rows with the
same values for all columns :(
Thanks for you help|||Hi
I assumed that col1 is a PRIMARY KEY
CREATE TABLE cols
(
col1 varchar(20) NOT NULL PRIMARY KEY,
col2 varchar(20)
)
INSERt INTO cols (col1,col2)
SELECT 'val11','val21'
INSERt INTO cols (col1,col2)
SELECT 'val12','val22'
INSERt INTO cols (col1,col2)
SELECT 'val13','val23'
INSERt INTO cols (col1,col2)
SELECT 'val14','val24'
SELECT *,(SELECT COUNT(*) FROM cols C WHERE C.col1<=cols.col1)rank
FROM cols ORDER BY rank
<balacr@.gmail.com> wrote in message
news:1127285371.485317.274560@.g43g2000cwa.googlegroups.com...
> Jens,
> This will not give the expected result if there are 2 rows with the
> same values for all columns :(
> Thanks for you help
>|||You need a unique combination of columns for that, otherwise you could
pump the data in a temp table with an identity column which will insert
a increasing number on its own.
HTH, Jens Suessmeyer.|||select FirstName,LastName,RowNo=(SELECT count(*) from employeesa1 t2
where t1.empid<=t2.empid)from employeesa1 t1
order by rowno asc|||Create a temp table or declare a table variable with an identity column &
insert ur data into that. Use this table...
This is the only solution for cases where u hv no unique keys
Rakesh
"balacr@.gmail.com" wrote:

> How do I get a counter in a select statement output
>
> Say if I do
> Select counter, col1, col2 from table1
>
> The output should be
>
> Counter Col1 Col2
> 1 val11 val21
> 2 val12 val22
> 3 val13 val23
> 4 val14 val24
>