"CTAS" in SQL Server

• orasqldba

  SSC Veteran

  Points: 279

  More actions

  March 12, 2008 at 11:55 am

  #183217

  Hi, There is something called "CTAS" create table as in Oracle and was wondering if there was anything like that in SQL 2000.

  I am a newbie to SQL.

  In Oracle you can do the following..

  create table my.test

  as

  select * from your.test;

  also

  insert into my.test

  select * from your.test;

  Your help in much appreciated...all this excercise is to put some load a DB server, by CTAS'ng a big table.

  Thanks in Advance

  Ramki

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  March 12, 2008 at 12:26 pm

  #788735

  Look at using Select ... INTO

  Example:

  Select *

  INTO MyNewTable

  from MyOldTable

  Uses whatever it derives from the query to make a table.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  March 12, 2008 at 4:53 pm

  #788824

  Matt Miller (3/12/2008)

  ---

  Look at using Select ... INTO

  Example:

  Select *

  INTO MyNewTable

  from MyOldTable

  Uses whatever it derives from the query to make a table.

  And if you want to "Create As" like this but without any data:

  Select *

  INTO MyNewTable

  from MyOldTable

  Where 0=1

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• orasqldba

  SSC Veteran

  Points: 279

  More actions

  March 12, 2008 at 5:59 pm

  #788838

  Thankyou!

• Scalability Doug

  SSCertifiable

  Points: 5351

  More actions

  March 12, 2008 at 8:38 pm

  #788860

  There are 2 differences between SELECT INTO and INSERT INTO...

  SELECT INTO only works for if the inserted table does not exist. INSERT INTO works if the inserted table already exists.

  DAB