create table from query results

Hi everybody need help on the possibility creating a new table from the results of a view or query? below is my table named table1

ID col1 col2

1 a a
2 b d
3 c f

this would be my new table named table2

ID col1 col2 col3

1 a a aa
2 b d bd
3 c f cf

this new table has an additional column by concatenating col1+col2
tried this procedure but is not working

CREATE TABLE AS (SELECT ID, COL1, COL2, COL1+COL2) TABLE2

thanksare you sure you need to store the concatenation in a separate table?SELECT ID, COL1, COL2, COL1+COL2 AS COL3
INTO TABLE2
FROM TABLE1|||thanks Rudy

yes I'll be storing the concatenated field in a new table

will that procedure create automatically the table2 even if i don't use CREATE TABLE?

alex

Create table from dynamic SP

Hi Everybody
I have a dynamic Sp that return data ( crossta)
I need create a table from SP result.
How can I do that.
Thanks
Pablo Salazar.Pablo,
possible ways:
1. use (global) temp table
2. use an inline table type variable
3. use insert into your temptable
execute (your proc)
Quentin
"Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
news:e$sH3o4NEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi Everybody
> I have a dynamic Sp that return data ( crossta)
> I need create a table from SP result.
>
> How can I do that.
>
> Thanks
> Pablo Salazar.
>
>|||http://www.sommarskog.se/share_data.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
news:e$sH3o4NEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi Everybody
> I have a dynamic Sp that return data ( crossta)
> I need create a table from SP result.
>
> How can I do that.
>
> Thanks
> Pablo Salazar.
>
>sql

create table => system table

Hallo everybody,
when I create a table or a stored procedure it always becomes a system
object instead of a user object, who can I avoid this? I just want to create
user objects.
I'm using SQL Server 2000 and I have all the service packs installed, the
user I'm using to create the table is DBOwner of the database and is part of
the role "system administrators"
thanks for the help
CristianSomebody has been playing with the sp_MS_upd_sysobj_category procedure. Exec
ute it with the value 2
as parameter and verify that objects create from thereon will not be system
objects.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Cristian" <cristiansuazo@.hotmail.com> wrote in message news:O0iCVmKWGHA.752@.TK2MSFTNGP02.p
hx.gbl...
> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to crea
te
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
of
> the role "system administrators"
> thanks for the help
> Cristian
>|||what do you mean by system object. How did you find that it was a system
object?
"Cristian" wrote:

> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to crea
te
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
of
> the role "system administrators"
> thanks for the help
> Cristian
>
>|||What makes you think they're system objects?
*mike hodgson*
http://sqlnerd.blogspot.com
Cristian wrote:

>Hallo everybody,
>when I create a table or a stored procedure it always becomes a system
>object instead of a user object, who can I avoid this? I just want to creat
e
>user objects.
>I'm using SQL Server 2000 and I have all the service packs installed, the
>user I'm using to create the table is DBOwner of the database and is part o
f
>the role "system administrators"
>thanks for the help
>Cristian
>
>|||thanks man! that resolved everything... strange stored procedure, an
undocumented one, anyway thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8cZjzKWGHA.3972@.TK2MSFTNGP02.phx.gbl...
> Somebody has been playing with the sp_MS_upd_sysobj_category procedure.
Execute it with the value 2
> as parameter and verify that objects create from thereon will not be
system objects.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Cristian" <cristiansuazo@.hotmail.com> wrote in message
news:O0iCVmKWGHA.752@.TK2MSFTNGP02.phx.gbl...
create
the
part of
>|||Perhaps trace flag 1717 is on. You can interrogate with DBCC TRACESTATUS
(1717) and turn off with DBCC TRACEOFF (1717, -1) . Remove it from startup
parameters, if present.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cristian" <cristiansuazo@.hotmail.com> wrote in message
news:O0iCVmKWGHA.752@.TK2MSFTNGP02.phx.gbl...
> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to
> create
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
> of
> the role "system administrators"
> thanks for the help
> Cristian
>

Create Schema in Sql Server 2000

Hi everybody, I like to asked anyone who is very good at T-Sql to help me with Sql Server 2000. Thanks in advance. In Sql Server 2000 I have the statement use DBTEST CREATE SCHEMA AUTHORIZATION Ross CREATE VIEW v1(c1) AS SELECT c1 from t1 CREATE TABLE t1(c1 int) GO It work correctly, but Ross don't belong to DBTEST; also use DBTEST CREATE SCHEMA AUTHORIZATION atrandom CREATE VIEW v1(c1) AS SELECT c1 from t1 CREATE TABLE t1(c1 int) GO work with correctness. Why ? The owner can be every words ?

SQL Server 2000 does not have schema concept like SQL Server 2005 so the authorization syntax doesn't really do anything. User-schema separation is available completely in SQL Server 2005 where the authorization clause makes sense.

create data source, data source view from XML?

hi everybody,
i want to create data source and data source view for data mining, with using C Sharp.
i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully,
and have i build data source and data source view, how to do it.?

thanks for you helps

Basically, you need to double check that you can the following: 1) You can connect to the server successfully; 2) The database for containing the target data source and data source view has been created, and you can access it; 3) You specify the connection appropriately.

Please double check your script against the following sample XMLA script for creating Data Source:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ParentObject>

<DatabaseID>Your Database ID</DatabaseID>

</ParentObject>

<ObjectDefinition>

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlnsdl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlnsdl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xsi:type="RelationalDataSource">

<ID>Your Data source ID</ID>

<Name>Your Data source name</Name>

<ConnectionString>Provider=SQLOLEDB.1;Data Source=Server Name;Integrated Security=SSPI;Initial Catalog= Your Database ID </ConnectionString>

<ImpersonationInfo>

<ImpersonationMode>Default</ImpersonationMode>

</ImpersonationInfo>

<Timeout>PT0S</Timeout>

</DataSource>

</ObjectDefinition>

</Create>

I have marked out the xml elements that you need to pay extra attention. As to the script for data source view, you also need to make sure the related data source exist on the server.

Good luck,