Re: Trucate Command

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 05/07/04 

Date: Fri, 7 May 2004 16:54:13 +0530

Hi ,

The best way to deal this will be:-

1. Create a temp (original) table with the same structure
2. Insert into temp select * from real_table where date_col between
start_date and end_date
3. The second step will be fast if you have an idex on date_col
4. Truncate the original table (Truncate table table_name) -- This step will
be very fast , rather than using DELETE from
5. insert into real_table select * from temp

The above 5 steps will spped up your operation.

Thanks
Hari
MCDBA

"Vishal Parkar" <REMOVE_THIS_vgparkar@yahoo.co.in> wrote in message
news:BE30B741-0C70-4D1B-88F5-150CA4D99C92@microsoft.com...
> hi garry,
>
> >>Can I use the truncate command???
>
> No, Truncate table command removes all rows from a table.
>
> >>Can I use it to bring the temp_table data back to the original table
> with out reprocusions? <<
>
> No, you can not do any kind of recovery using truncate table command. use
"truncate table" command to remove all the rows from the table.
>
> What you need is INSERT INTO....SELECT statement. By which you can
transfer the rows from both the tables.
> The SELECT query in the INSERT statement can be used to add values into a
table from one or more other tables.
>
> A typical select... into syntax will look as follows.
>
> 1)
> INSERT INTO destination_table(col1,col2)
> SELECT col1,col2
> FROM <source_table>
> WHERE .....
>
> 2)
> once you transfer the rows to the temp table. you can delete the rows from
original table using
>
> DELETE FROM table where..... syntax.
>
> 3)
> To transfer rows back to the original table from temp table you can use
same INSERT INTO....SELECT syntax.
>
> --
> Vishal Parkar
> vgparkar@yahoo.co.in | vgparkar@hotmail.com
>
>
>
>
>

Relevant Pages

  • RE: Trucate Command
    ... you can not do any kind of recovery using truncate table command. ... into syntax will look as follows. ... To transfer rows back to the original table from temp table you can use same INSERT INTO....SELECT syntax. ...
    (microsoft.public.sqlserver.mseq)
  • Re: Instead of DBO
    ... Anyone can create temp table. ... When you say TRUNCATE TABLE, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • temp
    ... Create table #tablename is working with MSSQL7.When i tried with MSACCESS the command is ... not accepting.For creating a temp table,what syntax i can use in Access. ...
    (microsoft.public.vb.database)
  • Re: Proper Syntax
    ... I need to use the following code for a command line function, ... does not seem to like the syntax. ... ensure that the folder 'temp' exists. ...
    (microsoft.public.visio.developer)
  • Re: Delete without log ??
    ... and after a lot of test I use the TRUNCATE (with ... temp tables to keep the data that I need) ... > (Create your own groups, Forum, FAQ's and a ton more) ...
    (microsoft.public.sqlserver.server)