SQL .select into statement

[mcclunyboy]

Hi,

Random question.

I want to take an exact copy of a table - then every now and then overwrite this copy with a more up to date copy.

Question -

select * into [I]tablename [/I]from [I]originaltable[/I]

Does that create a new table from scratch?

Or is it better to use to create the table and then populate it:

Create table [I]tablename  [/I]from (select * from [I]originaltable)[/I]

thoughts?

 

[namliam]

Create table statement doesnt exist for Access.

Select ... into tablename from ... is the create table
Insert into ... select ... from is the Append you mean.

It depends on your situation which is better. If you want 100% exact copy even if columns are added then the Select into would be your choice.

 

[WayneRyan]

m,

Hope this gets you started, but remember that when you do a "Select * Into ..."
the "new" table doesn't inherit Primary Keys and Indexes ...

If Exists (Select *
              From  Sysobjects
              Where Name = 'SomeTable' And
                         Type = 'U')
   Begin
      Truncate Table SomeTable
      --
      Insert Into SomeTable 
      Select * from originaltable
   End
Else
   Begin
      Select * 
      Into SomeTable
      From originaltable
   End

Wayne