Converting a varchar

Question

Post reply

Converting a varchar

Mitchell Leitch

SSC Rookie

Points: 26
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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 1

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: 26 More actions · Answer 2

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 SSC-Addicted Points: 470 More actions · Answer 3

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: 26 More actions · Answer 4

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