Converting a varchar

• Mitchell Leitch

  SSC Rookie

  Points: 36

  More actions

  July 13, 2004 at 3:20 pm

  #162919

  Hi,

  Im trying to create a varchar based on quarter and year putting them together then, using that string as an identifier for a database for an insert in a trigger,

   

  for example:

   

  DECLARE @quarter

  SET @quarter = 'Q1' + '2004'

  INSERT INTO [dbo].[@quarter] -- whatever you want to insert

   

  i cant figure out how to convert that name because the error keeps popping up Invalid Object name [dbo].[@quarter], it wont recognize that is a variable or atleast convert whatever is stored in that variables memory location 

   

  i know it maybe a simple question but hey i just picked this up today, and the microsoft help files are "unhelpful" 

   

  thanks from a new newb,

  Mitch

• Jim P.

  SSCrazy Eights

  Points: 8725

  More actions

  July 13, 2004 at 3:37 pm

  #514469

  DECLARE @quarter

  declare @sql Varchar(8000)

  SET @quarter = 'Q1' + '2004'

  SELECT @sql = ('INSERT INTO [dbo].[' + @quarter + '] ' + 'SQL Statement')

  EXEC @sql

  Note that if the table doesn't exist it would be 'select * into [dbo].[' + @quarter + '] SQL Statement'

  
  
  ----------------
  Jim P.

  A little bit of this and a little byte of that can cause bloatware.

• Mitchell Leitch

  SSC Rookie

  Points: 36

  More actions

  July 14, 2004 at 1:58 pm

  #514548

  Thanks a bunch.

  Im having a problem now where im trying to access the deleted table and because im calling it from an EXEC statement. It says the deleted is not a valid object, it seems to be out of scope is there anyway to bypass this issue

   

  I have it set up like this

  SET @quarter = 'Q1' + '2004'

  SET @sql = ('INSERT INTO [dbo].[' + @quarter + ']  (Column names, ...)

  SELECT * FROM DELETED where (Archived = 1) ' )

  EXEC (@SQL)

  is there anyway to put the deleted table into scope or a way to work around this.

  Any direction would help

   

  Thanks,

  Mitch

   

   

• Alex Wilson-148544

  Mr or Mrs. 500

  Points: 500

  More actions

  July 15, 2004 at 2:14 am

  #514602

  Mitch,

  (two things?)

  (1)

  Do you need to perform the operation using dynamic SQL?

  Can you not do something similar to:

  SET @quarter = 'Q1' + '2004'

  insert into TableMitch (key, field1, field2)

  values (@quarter, 'dummy', 'dummy')

  SELECT * FROM DELETED where (Archived = 1)

  (2)

  You are trying to access the deleted table, without actually deleting anything, this table will not be available until you until you issue a delete or an update. (someone correct me if i'm wrong)

  hth,

  Alex

   

• Mitchell Leitch

  SSC Rookie

  Points: 36

  More actions

  July 15, 2004 at 6:29 am

  #514630

  Hi,

  well, it is a delete trigger i might have failed to mention it before sorry

  so yes there will be a deleted table

  first off, unfortunately i do have to use dynamic memory because i dont want to have to write the same thing of four quarters for the next 10 years.. i think that takes a bit of memory...

   

  but i found a way around it creating a temp table of deleted so when i use the exec statement the temp table is in scope, unfortunately the deleted table is not.. 

   

  thanks for everyones help...

   

  if there is a more efficient way to do this, could you let me know? i was hoping this would be a quick fix, but if i dont have to deal with it im ok with that too

   

  thanks,

  Mitch