Insert into table , Sybase

Table2 will be just the backup of Table1.

Even so, is there an identity column or a numeric PK column in table1?

No, it does not have any identity column or PK column

In that case, you can do it all in one statement including creating the table itself by using

SELECT *
  INTO BackupTable
 FROM SourceTable

Select/Into is a minimally logged operation (only the metadata and space allocation are logged) so you don't have to worry about having enough log space.  It is also very fast since it uses the same mechanism internally as a BCP.

Remember to delete the table you created before you run this as it does not work into an existing table.

Regards,
Bill

What does identity column or PK column has to do with it?

Well, a PK is not really a problem in either case, however, it will add overhead to the process of getting data into the new table.

An identity column is another issue all together.  The system assigns a value to an identity column as the row is inserted into the table.  Normally, you have no control over it and, in fact, if you attempt to insert or update an identity column you will get an error.

Depending on the version of Sybase you are running, there are various methods for dealing with identity columns.  For instance, you can turn the IDENTITY INSERT option on which will allow you to insert values into an identity column.  You can check the doc on ASE and identity columns for the version you are using.

Regards,
Bill

Thanks Bill for the explaination.

Can you also review the code <Table1> contains 1200 rows and running this query gives <Table2> now contains 12000.

I just want the those 1200 rows to be copied to <Table2> but in chunks of 100.

Can you let me know where I am going wrong?

DECLARE @max int
 
SELECT @max = 0
SELECT @max = count(*) from <Table1>
 
SET ROWCOUNT 100
WHILE (@max > 0)
 
BEGIN 
    
    INSERT INTO <Table2> 
    SELECT * FROM <Table1>
    select @max = @max - 1 
  
END
 
SET ROWCOUNT 0

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

What you are doing will not work at all.  In a nut shell, how does you code know which 100 rows to get/insert each time through the loop.

Chances are you are getting the same 100 rows 1200 times for a total of 120,000 (instead of 12,000).  Even if you were going to make this work, SELECT @MAX = @MAX -1 should be @MAX - 100

Unless there is something you haven't told us, drop Table2 and issue a SELECT/INTO and be done with it.

Regards,
Bill

Actually we have  drop Table2 and a SELECT/INTO in the existing code.

But the table 1 contains CC# and table 2 is backup table which is taken every month. Table 2 just sit back containing CC# , no one is using them. So i was being asked to use "TRUNCATE" instead of dropping and then insert into in small batches not one big transaction. so thats why i am looking for solution.