create table dynamically

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  September 5, 2014 at 4:41 am

  #311355

  Hi Guys,

  I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic.

  i.e. some time 5 columns and some time 10 columns.

  Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.

  Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.

  During each load we can drop table, No issue and we can handle this through SSIS Package.

  Please suggest approach.

  Thanks,

  Abhas.

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  September 5, 2014 at 6:29 am

  #1743495

  If the table is dropped each load then a simple select into will do the job, as long as the table name remans the same on each load

• terry999

  SSCertifiable

  Points: 5036

  More actions

  September 5, 2014 at 8:24 am

  #1743534

  select * INTO tblCustomer_new FROM tblCustomer WHERE 1=2

  Will create a new table tblCustomer_new, with no rows in it

  You will have to check if table exists first and drop it.

  Check the new structure, w.r.t to fields being nullable. I know it will not create a PK on the new table.