SQL Server Performance

creating new statging table for everyday with date extension

Discussion in 'SQL Server 2005 Integration Services' started by robin712, Jun 11, 2008.

  1. robin712 New Member

    Hi All,

    I am extracting source data which is in txt fille to OLE DB destination. But data of each day I want to save in different staging table. For Eg; tblProduct20081206, tblProduct20081207. How can it be done. I have seen lots of posting and script when destination is Txt. I want to use same table for staging but want to create different table for each day with adding date extension.

    Please Help

  2. MohammedU New Member

    You have to use the dynamic sql to create the table...
    declare @sql Varchar(1000), @tablename Varchar(100)
    select @tablename = 'tblProduct'+Convert(char(8), getdate(),112)
    select @tablename
    select @sql = 'create table '+@tablename+' (col1 int, col2 int...)'
    exec (@sql)
    You can have a regular table without date stamp and rename it once populated with date stamp...
  3. knowledgy New Member

    Without knowing your design constraints another option that may work for you would be to dump all your source data into the same destination table and use a view to partition or seperate the data by day.
  4. Madhivanan Moderator

    Why dont you have single staging table with datetime column to identify each day's data?
  5. Madhivanan Moderator

    Also read www.sommarskog.se/dynamic_sql.html

Share This Page