Moving tables between filegroups

• nabaj

  SSC-Addicted

  Points: 410

  More actions

  November 5, 2007 at 9:12 am

  #126429

  I am working on a ssis package. Tables created in the SSIS are named in the MMYY format using the datepart function. I need to determine the year of the table and then put it in a filegroup accordingly. Like tables with '07' in the name will go in a 07 filegroup.

  I can query the database and get a list of tables for 07

  I am thinking placing them in yearly filegroups can be done in 2 ways

  1.I can place the table in filegroup using the ‘ON’ clause using a variable (containing the current year) when I create the table. This is not working when I try using the variable below it creates a filegroup by the name ‘@fileg’.

  declare @fileg varchar(60)

  set @fileg = substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)

  create table test (id int) ON [@fileg]

  2.I can create a bunch of filegroups and have a step in the package where a query can be used to identify tables with ‘06’, ‘07’ and so on and then place them in the required filegroups. I found one way was to use the 'drop constraint move with' option but that is working with primary key but not with check constraint

  That is all the info i have been able to get so far. Does Please help. Are there any other ways of doing in besides an execute SQL Task in SSIS?

• Dinakar Nethi-176633

  SSCarpal Tunnel

  Points: 4689

  More actions

  November 5, 2007 at 10:49 am

  #748341

  You need to use dynamic SQL. Something like this:

  declare @fileg varchar(60), @sql varchar(500)

  set @fileg = substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)

  Set @sql = 'create table dbo.test (id int) ON [' + @fileg + ']'

  Exec(@Sql)

  ******************
  Dinakar Nethi
  Life is short. Enjoy it.
  ******************

• nabaj

  SSC-Addicted

  Points: 410

  More actions

  November 6, 2007 at 7:07 am

  #748639

  Thankyou for your help.. i tried using dynamic sql for creating a filegroup as a task in the beginning of the package but that is giving me an error now..could you plz give me some kind of direction on how i can solve this:

  declare @newfilegroup varchar(60)

  set @newfilegroup = substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)

  EXEC ('ALTER DATABASE test ADD FILEGROUP ' + @newfilegroup)

  this gives me a synatx error where as

  declare @newfilegroup varchar(60)

  set @newfilegroup = 'a' + substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)

  EXEC ('ALTER DATABASE test ADD FILEGROUP ' + @newfilegroup)

  works fine