Multiple Job steps using same variable or one temp table

I need a constant to be available in multiple steps of a job (more
specifically, getdate() from step one). I tried storing it in a variable or
temp table but when I get to step two of the job and try to retrieve the
value, I get "must declare variable..." or "table does not exist" (depending
on which method I am testing).
I did a little test and the SPID in each one of the steps is the same so
shouldn't the temp table be accessible from all steps?
A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>I need a constant to be available in multiple steps of a job (more
> specifically, getdate() from step one). I tried storing it in a variable or
> temp table but when I get to step two of the job and try to retrieve the
> value, I get "must declare variable..." or "table does not exist" (depending
> on which method I am testing).
> I did a little test and the SPID in each one of the steps is the same so
> shouldn't the temp table be accessible from all steps?
|||Initially, my main reason for not using a permanent table is that I will need
to drop it at the end and if step 2 fails and the table gets dropped in step
3 then it won't get dropped.
I could modify step 2 to continue on to step 3 even if it fails but then
the job will show as completed succesfully even if step two failed.
Maybe I will just use a permanent table and keep it so that I don't need to
drop it at the end...
"Tibor Karaszi" wrote:

> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
> table?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>
>
|||> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
That is what I would do.

> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed successfully even if step two failed.
Have two finishing steps. If step 2 fails, goto step 4 which drops the table and returns fails
status whether step 4 is OK or not...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:FBFC6287-C3C4-47C3-A58F-9AF3B373E3D7@.microsoft.com...[vbcol=seagreen]
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed succesfully even if step two failed.
> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
> "Tibor Karaszi" wrote:

Multiple Job steps using same variable or one temp table

I need a constant to be available in multiple steps of a job (more
specifically, getdate() from step one). I tried storing it in a variable or
temp table but when I get to step two of the job and try to retrieve the
value, I get "must declare variable..." or "table does not exist" (depending
on which method I am testing).
I did a little test and the SPID in each one of the steps is the same so
shouldn't the temp table be accessible from all steps?A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
table?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>I need a constant to be available in multiple steps of a job (more
> specifically, getdate() from step one). I tried storing it in a variable or
> temp table but when I get to step two of the job and try to retrieve the
> value, I get "must declare variable..." or "table does not exist" (depending
> on which method I am testing).
> I did a little test and the SPID in each one of the steps is the same so
> shouldn't the temp table be accessible from all steps?|||Initially, my main reason for not using a permanent table is that I will need
to drop it at the end and if step 2 fails and the table gets dropped in step
3 then it won't get dropped.
I could modify step 2 to continue on to step 3 even if it fails but then
the job will show as completed succesfully even if step two failed.
Maybe I will just use a permanent table and keep it so that I don't need to
drop it at the end...
"Tibor Karaszi" wrote:
> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
> table?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
> >I need a constant to be available in multiple steps of a job (more
> > specifically, getdate() from step one). I tried storing it in a variable or
> > temp table but when I get to step two of the job and try to retrieve the
> > value, I get "must declare variable..." or "table does not exist" (depending
> > on which method I am testing).
> >
> > I did a little test and the SPID in each one of the steps is the same so
> > shouldn't the temp table be accessible from all steps?
>
>|||> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
That is what I would do.
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed successfully even if step two failed.
Have two finishing steps. If step 2 fails, goto step 4 which drops the table and returns fails
status whether step 4 is OK or not...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:FBFC6287-C3C4-47C3-A58F-9AF3B373E3D7@.microsoft.com...
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed succesfully even if step two failed.
> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
> "Tibor Karaszi" wrote:
>> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
>> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
>> table?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
>> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>> >I need a constant to be available in multiple steps of a job (more
>> > specifically, getdate() from step one). I tried storing it in a variable or
>> > temp table but when I get to step two of the job and try to retrieve the
>> > value, I get "must declare variable..." or "table does not exist" (depending
>> > on which method I am testing).
>> >
>> > I did a little test and the SPID in each one of the steps is the same so
>> > shouldn't the temp table be accessible from all steps?
>>

Multiple INSERT's INTO temp table w/ Primary Key

Hi all -
I need to create a temp table containing all Items with activity for the
last five years. These records will be coming from a few different tables.
So I have the following . . .
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
INSERT INTO #tt_item
SELECT dbo.item.item
FROM Orders
. . . . which I'll have to repeat for each table in question.
My question is, with the PRIMARY KEY on the #tt_item table, how do I check
for the existence of an Item in #tt_item as I'm doing the INSERT?This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||INSERT INTO #tt_item (item)
SELECT O.item
FROM Orders AS O
LEFT JOIN #tt_item AS I
ON O.item = I.item
WHERE I.item IS NULL
OR:
INSERT INTO #tt_item (item)
SELECT item
FROM Orders
UNION
SELECT item
FROM Foo
UNION
SELECT item
FROM Bar
UNION
..
David Portas
SQL Server MVP
--|||Many thanks David, as well as CBretana, for providing these timely, helpful
examples. Looks like any of these will work.
"David Portas" wrote:

> INSERT INTO #tt_item (item)
> SELECT O.item
> FROM Orders AS O
> LEFT JOIN #tt_item AS I
> ON O.item = I.item
> WHERE I.item IS NULL
> OR:
> INSERT INTO #tt_item (item)
> SELECT item
> FROM Orders
> UNION
> SELECT item
> FROM Foo
> UNION
> SELECT item
> FROM Bar
> UNION
> ...
> --
> David Portas
> SQL Server MVP
> --
>

Multiple inserts against single insert from temp table

Hi,

I have table (AttributeID INT NOT NULL, ValueID INT NOT NULL), both of them are foreign keys. I need replace several rows which have same AttributeID and various ValueID with new values. So I must delete all rows WHERE AttributeID=@.AttributeID and then insert new values. What is better,

for every row make one call to SP which will do single INSERT;

or supplies all ValueIDs as single parameter to SP and then extracts ValueIDs them to temp table and then do INSERT MyTable SELECT * FROM #TempTable.

Which solution will be faster ? Or do you have idea for other solution which will outperform those two ?

Much thanks for your replies.

In general, a set base solution (i.e. single dml that affects more than one row at a time) is faster and better than row-per-row. So, if you have lots of rows to be processed, putting the data into a temp table (i.e. staging table) then invoking a single dml command would give you the most bang for your buck.

Multiple db query call from within different context into #temp table

The first query returns me the results from multiple databases, the
second does the same thing except it puts the result into a #temp
table? Could someone please show me an example of this using the first
query? The first query uses the @.exec_context and I am having a
challenge trying to figure out how to make the call from within a
different context and still insert into a #temp table.

DECLARE @.exec_context varchar(30)
declare @.sql nvarchar(4000)
DECLARE @.DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.exec_context = @.DBNAME + '.dbo.sp_executesql '
set @.sql = N'select top 10 * from products'
exec @.exec_context @.sql
FETCH NEXT FROM companies_cursor INTO @.DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
--------------------------
CREATE TABLE #Test (field list here)
declare @.sql nvarchar(4000)
DECLARE @.DBNAME nvarchar(50)

DECLARE companies_cursor CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
ORDER BY NAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.sql = N'select top 10 * from '+@.DBNAME+'.dbo.products'
INSERT INTO #Test
exec (@.sql)
FETCH NEXT FROM companies_cursor INTO @.DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
SELECT * from #Test
DROP TABLE #TestOn Mar 21, 12:57 pm, santaferub...@.gmail.com wrote:

Quote:

Originally Posted by

The first query returns me the results from multiple databases, the
second does the same thing except it puts the result into a #temp
table? Could someone please show me an example of this using the first
query? The first query uses the @.exec_context and I am having a
challenge trying to figure out how to make the call from within a
different context and still insert into a #temp table.
>
DECLARE @.exec_context varchar(30)
declare @.sql nvarchar(4000)
DECLARE @.DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.exec_context = @.DBNAME + '.dbo.sp_executesql '
set @.sql = N'select top 10 * from products'
exec @.exec_context @.sql
FETCH NEXT FROM companies_cursor INTO @.DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
-----------------------*----
CREATE TABLE #Test (field list here)
declare @.sql nvarchar(4000)
DECLARE @.DBNAME nvarchar(50)
>
DECLARE companies_cursor CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
ORDER BY NAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.sql = N'select top 10 * from '+@.DBNAME+'.dbo.products'
INSERT INTO #Test
exec (@.sql)
FETCH NEXT FROM companies_cursor INTO @.DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
SELECT * from #Test
DROP TABLE #Test

Not sure if this solves the problem:

WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.sql = ' INSERT INTO #Test select top 10 * from '+@.DBNAME
+'.dbo.products'
exec (@.sql)
FETCH NEXT FROM companies_cursor INTO @.DBNAME
END