July 13, 2004 at 3:20 pm
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
July 13, 2004 at 3:37 pm
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.
July 14, 2004 at 1:58 pm
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
July 15, 2004 at 2:14 am
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
July 15, 2004 at 6:29 am
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy