I am using below two statements to create table and insert some data from an existing table.
Is there a way in which stats will be automatically collected after insert statement completes or a third statement of manually collecting stats is the only way ?
Can something be included with Insert statement to collect the stats ?
1) CT Database2.Table_name as Database1.Table_name with no data and stats;
2) Insert into Database2.Table_name sel * from Database1.Table_name where condition;
STATS are not automatically refreshed, you need a third statement.
When you create a table with no data and stats, the stats will be defined as per the original table with zero stats.
May be you can copy stats from source table if updated stats are already present in that table and you are loading entire records with out a where clause.
COLLECT STATS ON Database2.Table_name FROM Database1.Table_name;