SQL 2012 :: Global Temp Table - Invalid Object Name
Feb 13, 2015
I have created a global temp table in Step1 of SQL Job.
I have used that in remaining steps of same job...i ran the job
But i got error message like invalid object name ##xxxxxxxx later i have included as tempdb..##xxxxxxxx also. the i got invalid reference for...
From my SSMS:-
But i was able to do select query for the same from my SSMS...
i have incorporated all steps in single step and completed job...
My question is why ##temp table created in step1 is not able to use in other steps of same job ?
SQL Server 2012 Enterprise Edition
View 2 Replies
ADVERTISEMENT
Feb 24, 2005
Hi:
A regular permanent table is not an option:
Need to exec a procA which at the end also exec procB.
procA will insert to table A and also generate a intermidate result set to a ##A global table, it works fine at this point. However, when it exec the procB at the end of exec procA, the ##A global table is empty when entering procB.
The key is I want to pass a records set to the procB without using a tableTempB. Does a ## global table should be still alive until procB execution is done?
I also tried use table variable, but it does not look like to accept @tableA
as part of the procB's parameters.
Also tried function, but it could not support a #tempTable within it which will do a dynamic query insertion.
thanks
David
View 4 Replies
View Related
Jul 23, 2005
I have a pivot table implementation, part of which is posted below. Itreturns no errors in query analyzer, but when profiler is run, it showsthat "Error 208" is happening. I looked that up in BOL and it meansthat an object doesn't exist. This block of code below works fine on mylocal development machine, but not on our shared development serveruntil I go into the tempdb and make the user have the role db_owner.Even wierder is that when I do a select * from ##pivot there is noerror, but if I specify the single column name (pivot) i.e. selectpivot from ##pivot, it takes the error...Obviously this is a rights issue, but is there any way around thisother than making the user owner of tempdb??declare @select varchar(8000), @PackageId intset @PackageId = 10set @select = 'selectCompany = COALESCE(Users.Company, Contact.Company, ''''),SubContractPackageVendor.Id, SubContractPackageVendor.isActive,SubContractPackageVendor.isAwarded,SubContractPackageVendor.UserOrContactType,SubContractPackageVendor.UserOrContactIdFROMSubContractPackageVendorLEFT JOIN SubContractPackage ON SubContractPackageVendor.PackageId =SubContractPackage.IdLEFT JOIN Users ON UserOrContactType = ''User'' AND UserOrContactId =Users.UserIdLEFT JOIN UserRoles ON UserOrContactType = ''User'' ANDUserRoles.UserId = Users.UserId AND UserRoles.ProjectId =SubContractPackage.ProjectIdLEFT JOIN Role ON Role.RoleId = UserRoles.RoleIdLEFT JOIN Contact ON UserOrContactType = ''Contact'' ANDUserOrContactId = Contact.IdLEFT JOIN SubContractLineItem ONSubContractLineItem.RefType = ''Package'' ANDSubContractLineItem.RefId = SubContractPackageVendor.PackageIdLEFT JOIN SubContractLineItem as SubContractPackageVendorItem ONSubContractPackageVendorItem.RefType = ''PackageVendor'' ANDSubContractPackageVendorItem.RefId = SubContractPackageVendor.Id ANDSubContractPackageVendorItem.RefSubId = SubContractLineItem.IdWhereSubContractPackageVendor.PackageId = ' + CAST(@PackageId as varchar)+ 'GROUP BYSubContractPackageVendor.Id, SubContractPackageVendor.isActive,SubContractPackageVendor.isAwarded, Users.Company, Contact.Company,SubContractPackageVendor.UserOrContactType,SubContractPackageVendor.UserOrContactId'--print @sqldeclare @sumfunc varchar(100),@pivot varchar(100),@table varchar(100),@FieldPrefix varchar(5),@TotalFieldName varchar(50),@PivotFieldFilter varchar(1000)select@sumfunc ='Sum(isnull(SubContractPackageVendorItem.Total,0) )' ,@pivot ='SubContractLineItem.Category' ,@table ='SubContractLineItem' ,@FieldPrefix='~' ,@TotalFieldName = 'Total' ,@PivotFieldFilter = ' AND RefType=''Package'' AND RefId=' +CAST(@PackageId as varchar)set nocount onDECLARE @sql varchar(8000), @delim varchar(1), @TotalSql varchar(8000)SET NOCOUNT ONSET ANSI_WARNINGS OFFEXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + 'WHERE 1=2')EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' +@table + ' WHERE '+ @pivot + ' Is Not Null ' + @PivotFieldFilter)SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )SELECT @delim=CASE Sign( CharIndex('char',data_type)+CharIndex('date', data_type) )WHEN 0 THEN '' ELSE '''' ENDFROM tempdb.information_schema.columnsWHERE table_name='##pivot' AND column_name='pivot'select * from ##pivotDROP TABLE ##pivot
View 6 Replies
View Related
Oct 25, 2007
Greetings guru's,
Is there a benefit to using global temp tables? I've read a few articles this morning and there doesn't seem to be a real need for them.
Am I missing something?
Adam
View 4 Replies
View Related
Mar 1, 2006
Senerio:
I have to extract data from a read only table using a globel temp table then export it to another OLE DB connection or to a flat file. But in the new SSIS packages it does not allow you to do thisusing the global ## symbols in front of a table name. How do I get around this?
Thanks
View 6 Replies
View Related
Sep 7, 2006
hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?
View 16 Replies
View Related
Dec 17, 2004
I need to decide what is better to use: global temp table ( I can't use local one) or permanent table in SQL 2000 stored procedures. I extract data from linked server table and update several tables on our server.
Those procedures scheduled to run every 3 hours.
Another question: for some reasons when I used global temp table, I wasn't able to schedule multi steps with every step executing one of the stored procedures.I think global temp tables should be visible to other stored procedures, right?
Your suggestions?
View 1 Replies
View Related
Jun 24, 1999
I think this is a very simple question, however, I don't know the
answer. What is the difference between a regular Temp table
and a Global Temp table? I need to create a temp table within
an sp that all users will use. I want the table recreated each
time someone accesses the sp, though, because some of the
same info may need to be inserted and I don't want any PK errors.
thanks!!
Toni Eibner
View 2 Replies
View Related
Oct 21, 2015
I have a query I'm using in a report. It runs fine until I add the code with a declared variable called @Total. I'm thinking it has something to do with how Im trying to use3 the variable in the query before the UNION ALL.
thinking it has to do with using the variable in the query at the bottom before the UNION ALL
[highlight=#ffff11]Declare @Total as int[/highlight]
;with LastSevenDays as (
SELECT [GCM_CLIENT_CD],
HP_CD,
sfh.date_time_Started
From [dbo].[G_MASTER_CODING_RESULTS] (NOLOCK) mcr
[code]....
View 9 Replies
View Related
Nov 17, 2004
Hi all,
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
View 2 Replies
View Related
Nov 9, 2000
I have a database owned by the sa. In the database are some tables owned by id1. When I login via SQL Analyzer as id1 and try to select from
the tables owned by id1, I get an error message 208 that the object does not exist. If I query 'select * from id1.table', I get my data. I thought that
if I owned the table I could always access it. Additional facts, id1 is defined as a login, id1 is defined as a user for the database with db_owner role.
Any ideas?
Thanks,
Jen
View 2 Replies
View Related
Nov 17, 2006
After Upsizing a table to sql I linked to that table using access db
Now when i use one of my forms i get a [Invalid Object name "tablename''], not sure why but i am clearly link and the table is in sql!
Can you help!
View 1 Replies
View Related
Jul 6, 2006
Hello,I am getting a SqlException with title "SqlException was unhandled by user code" and then it says "Invalid object name 'Access Table'.here is my code (this is from my login page:)<script runat="server">
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT [Password] FROM [AccessTable] WHERE" & _
" (([Username] = @Username) AND ([Password] = @Password))"
conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance")
cmd = New SqlCommand(cmdString, conn)
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50)
cmd.Parameters("@Username").Value = Me.Login1.UserName
cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50)
cmd.Parameters("@Password").Value = Me.Login1.Password
conn.Open()
Dim myReader As SqlDataReader
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If myReader.Read() Then
FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False)
Else
Response.Write("Invalid credentials")
End If
myReader.Close()
End Sub
</script> The error is comming from the myReader = cmd.Execute(CommandBehavior.CloseConnection)Thanks for any suggestions or ideas.
View 2 Replies
View Related
Apr 8, 2008
Hello,
Can anyone shed some light on why the following:
declare @str varchar(2000)
set @str = 'SELECT * INTO #TmpTable FROM FormHistory'
exec (@str)
SELECT * FROM #TmpTable
gives the following error:
Invalid object name '#TmpTable'.
This is a very cutdown version of what I am trying to achieve so it might not seem obvious why I am writing it into a string and using exec but in the real code I do need to do this. I have cut it right back to try to get to the bottom of why this doesn't work. I suspect the # in the string is causing the problems.
Thanks for any help
View 16 Replies
View Related
Jan 21, 2015
I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.
BEGIN
SET NOCOUNT ON;
WITH Darin_Import_With_Key
AS
(
SELECT [pra_id]
,[pra_ClientPracID]
[code]....
View 2 Replies
View Related
Jan 4, 2008
Hi all,
I copied the following code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):
--CET.sql--
USE AdventureWorks
GO
--Use column value from a table pointed at by a foreign key
WITH ProductItemPrices AS
(
SELECT ProductID, AVG(LineTotal) 'AvgPrice'
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT p.Name, pp.AvgPrice
FROM ProductItemPrices pp
JOIN
Production.Product p
ON
pp.ProductID = p.ProductID
ORDER BY p.Name
SELECT * FROM ProductItemPrices
GO
--Display rows from SalesOrderDetail table with a LineTotal
--value greater than the average for all Linetotal values with
--the same ProductID value
WITH ProductItemPrices AS
(
SELECT ProductID, AVG(LineTotal) 'AvgPrice'
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT TOP 29 sd.SalesOrderID, sd.ProductID, sd.LineTotal, pp.AvgPrice
FROM Sales.SalesOrderDetail sd
JOIN
ProductItemPrices pp
ON pp.ProductID = sd.ProductID
WHERE sd.LineTotal > pp.AvgPrice
ORDER BY sd.SalesOrderID, sd.ProductID
--Return EmployeeID along with first and last name of employees
--not reporting to any other employee
SELECT e.EmployeeID, c.FirstName, c.LastName
JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
JOIN HumanResources.EmployeeDepartmentHistory d
ON d.EmployeeID = e.EmployeeID
JOIN HumanResources.Department dn
ON dn.DepartmentID = d.DepartmentID)
JOIN Empcte a
ON e.ManagerID = a.empid)
--Order and display result set from CTE
SELECT * Hi all,
I copied the following T-SQL code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):
--CTE.sql--
USE AdventureWorks
GO
--Use column value from a table pointed at by a foreign key
WITH ProductItemPrices AS
(
SELECT ProductID, AVG(LineTotal) 'AvgPrice'
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT p.Name, pp.AvgPrice
FROM ProductItemPrices pp
JOIN
Production.Product p
ON
pp.ProductID = p.ProductID
ORDER BY p.Name
SELECT * FROM ProductItemPrices
GO
--Display rows from SalesOrderDetail table with a LineTotal
--value greater than the average for all Linetotal values with
--the same ProductID value
WITH ProductItemPrices AS
(
SELECT ProductID, AVG(LineTotal) 'AvgPrice'
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT TOP 29 sd.SalesOrderID, sd.ProductID, sd.LineTotal, pp.AvgPrice
FROM Sales.SalesOrderDetail sd
JOIN
ProductItemPrices pp
ON pp.ProductID = sd.ProductID
WHERE sd.LineTotal > pp.AvgPrice
ORDER BY sd.SalesOrderID, sd.ProductID
--Return EmployeeID along with first and last name of employees
--not reporting to any other employee
SELECT e.EmployeeID, c.FirstName, c.LastName
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.ContactID
where ManagerID IS NULL
--Specify top level EmployeeID for direct reports
DECLARE @TopEmp as int
SET @TopEmp = 109;
--Names and departments for direct reports to
--EmployeeID = @TopEmp; calculate employee name
WITH Empcte(empid, empname, mgrid, dName, lvl)
AS
(
-- Anchor row
SELECT e.EmployeeID,
REPLACE(c.FirstName + ' ' + ISNULL(c.MiddleName, '') +
' ' + c.LastName, ' ', ' ') 'Employee name',
e.ManagerID, dn.Name, 0
FROM Person.Contact c
JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
JOIN HumanResources.EmployeeDepartmentHistory d
ON d.EmployeeID = e.EmployeeID
JOIN HumanResources.Department dn
ON dn.DepartmentID = d.DepartmentID
WHERE e.EmployeeID = @TopEmp
UNION ALL
-- Recursive rows
SELECT e.EmployeeID,
REPLACE(c.FirstName + ' ' + ISNULL(c.MiddleName, '') +
' ' + c.LastName, ' ', ' ') 'Employee name',
e.ManagerID, dn.Name, a.lvl+1
FROM (Person.Contact c
JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
JOIN HumanResources.EmployeeDepartmentHistory d
ON d.EmployeeID = e.EmployeeID
JOIN HumanResources.Department dn
ON dn.DepartmentID = d.DepartmentID)
JOIN Empcte a
ON e.ManagerID = a.empid)
--Order and display result set from CTE
SELECT *
FROM Empcte
WHERE lvl <= 1
ORDER BY lvl, mgrid, empid
--Alternate statement using MAXRECURSION;
--must position immediately after Empcte to work
SELECT *
FROM Empcte
OPTION (MAXRECURSION 1)
====================================
This is Part 1 (due to the length of input > 50000 characters).
Scott Chang
View 5 Replies
View Related
Apr 3, 2015
I want to create and drop the global temporary table in same statement.
I am using below command but I am getting below error
Msg 2714, Level 16, State 6, Line 11
There is already an object named '##Staging_Temp' in the database.
if object_id('Datastaging..##Staging_Temp') is not null
begin
drop table ##Staging_Temp
end
CREATE TABLE ##Staging_Temp(
[COL001] [varchar](4000) NULL,
[Code] ....
View 1 Replies
View Related
Sep 27, 2007
There are certain errors that does not come during compilation and it shows during execution because of late-binding concept implemented in SQL Server from 7.0 version.
The problem is when Tables are not there, SPs are created. And there is no option where we can set like 'Validate object resolution during compilation"
There are lot of SPs that are in invalid status because the tables are really not there and SP needs to be modified to reflect the correct table name. In Oracle, if I have to find the list of objects that are in invalid state (because of object resolution problems), it was possible. How do I do it in SQL?
I need a listing of all objects in my database that is in invalid state. Searched in NET but there seems to be no supporting tool also that lists invalid objects.
Pl let me know whether there exists a way by which I can get to know the invalid object lists in my SQL 2005 database
View 2 Replies
View Related
Aug 11, 1998
Hi everyone:
I am creating an sp, in which I check for the existence of a global temp table (using the exists)
statement. If the Exists returns a false, I move on to processing without the temp table. If it
returns a true, I utilize the temp table to do some inserts. I create the temp table when my
application first starts up. The problem that I am facing is that the check for the temp table`s
existence seems to be failing. Is there any other way to check for the existence of a global
temp table??
Any info really appreciated
Thanks
Nisha
View 1 Replies
View Related
May 29, 2006
Hi group,
I want to create several global temp tables. I've created a script:
if not object_id('tempdb..##tbl_ProductTypes') is null
begin
drop table ##tbl_ProductTypes
end
select * into ##tbl_ProductTypes from dbo.tbl_ProductTypes
This script creates a global temp table. When I run it in QA the table is created. When I close QA the table is dropped. This is correct since I found the following in BoL:
[Global temporary tables are automatically dropped when the session that created
the table ends and all other tasks have stopped referencing them. The
association between a task and a table is maintained only for the life of a
single Transact-SQL statement. This means that a global temporary table is
dropped at the completion of the last Transact-SQL statement that was actively
referencing the table when the creating session ended.]
I created a job that executes the statement above. The job exists with success, however the table is not created!
Why wasn't the table created??
TIA
Regards,
SDerix
View 4 Replies
View Related
Feb 11, 2015
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
View 3 Replies
View Related
Oct 5, 2006
Hi!
I have a stored proc that creates a global temp table. How can I have multiple users select records from and insert records in that table without overwriting and/or deleting data?
Thanks so much for your help!
-Parul
View 14 Replies
View Related
Jul 20, 2005
Can anyone tell me or post a link that says how many global temptables can exist SQL Server 2000? Also, is there a limit to thenumber of local temp tables that can exist?Thanks,Billy
View 1 Replies
View Related
Jun 3, 2015
I have two global temp tables in my stored procedure.Is it possible for me to make the execution of a stored procedure dynamic. Based on a flag parameter value I should get the result set from first temp table and viceversa ex: If my flag is 0.I should be able to get the result set from ##temp1 (select * from ##temp1).If my flag is 1.I should be able to get the result set from ##temp2 (select * from ##temp2).
View 4 Replies
View Related
May 22, 2014
I am working with the SP tuning. I want to know clearly about the temp tables.Instead of Select * into #table, it is always better to create temp table structure and insert the data later.Is it true in the case, if the data is small? insert into #table (select colmn1, column 2 from TableA join Table B on JoinC on joinD on..If they use several joins, in that situation, which way of creating temp table would be better?
View 9 Replies
View Related
Oct 4, 2006
Hi,
I want to drop a Global temp table within a stored procedure.
But first, I want to check it's existence.
So, what I would like to do is something like this
if exists
(select * from sysobjects where name like '##globaltemptable')
drop table ##globaltemptable
But I don't think the global temp tables get stored in sysobjects.
Any suggestions?
Thanks in advance
View 2 Replies
View Related
Apr 3, 2013
Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.
View 5 Replies
View Related
Mar 5, 2014
it runs over night, pulls 10.5m rows. Inserts into a table, from a select (so "insert...select", rather than "select into"), from many tables, grouping on a max. It's complex. During the day, it runs fine - maybe 25 minutes. At night it *sometimes* runs fine, but then sometimes takes 4hours.Checking this morning there were 230 threads open for this one query. Checking sys.dm_os_tasks and sys.dm_os_waiting_tasks there were no other wait types on that session_id. None at all. Checking activity monitor, most of the existing threads were suspended on the insert.
There are 24 cores, but NUMA'd. We have maxdop on the server of 8. The maxdop option on the query is 12, just to speed up the select. Index and Stats refreshed daily. We've eight identical tempdb data files, on a separate spindle. Checking those, they are filling up using the round robin correctly
Would the delay be due to SQL trying to combine so many 'select' threads into one 'insert' thread (as it can't insert in parallel; 2014 can, apparently. Upgrades not available!)Should i change the SP to run the select into a temp table (table variable?) with a maxdop of 12, then do the insert into the actual table using a maxdop of 1. Checking the execution plans for a table variable implies the subtree cost comes down from 2.5m to 357k. What's best - temp table or table variable?
View 9 Replies
View Related
Mar 25, 2014
I use code below to insert data into a temp table.
How many records can insert into a temp table?
Select * into #temp from ORDER
View 3 Replies
View Related
May 27, 2014
I try to modify a code to create a temp table but got an error.
Declare @headerid Int
Declare @Providerid Int
BELOW CODE IS WORKING:
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123
[code]...
Error:
Incorrect syntax near '='.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
View 5 Replies
View Related
Sep 23, 2014
I come from a web based world were loading 1.5 million records into a temp table is suicide. I’m doing more data warehouse stuff now and I was looking into optimizing a buddies proc and noticed he was loading 1.5 million records into a temp table. We had a discussion about it because being from a web world I was drastically against it. He on the other hand didn’t feel it was an issue being it gets called once maybe twice a day. The tempdb is set to autogrow and it is on a different drive than all the other databases on the box. It has one ldf and mdf. He’s creating an index on the table after load. Why we shouldn’t be loading 1.5 million recs into temp table?
View 5 Replies
View Related
May 12, 2015
I am trying to insert a single row in Temp table #InventoryItems . The temp table is mentioned in a curosor childcur_inventory. While looping through the cursor I have mentioned nested if else condition . In the Else condition where I have mentioned PRINT "Hello World" I want to insert a single row in the temp #InventoryItems. I trying to use Select Top 1 but the cursor is looping indefinitely trying to insert multiple record
I want to insert only one record with null values.
DECLARE childcur_inventory CURSOR FOR SELECT Structure_Number, State, Neighbor_State, Border_Bridge_Structure_Number FROM #InventoryItems
OPEN childcur_inventory
FETCH childcur_inventory INTO @Structure_Number, @State, @Neighbour_State, @Border_Bridge_Structure_Number
[Code] .....
View 5 Replies
View Related
Mar 2, 1999
I have an sql file that contains several queries that are generating numbers to populate a sql table. The sql file is too large for a single sp so I am nesting them. I have 4 nested stored procedures. Each of the queries in each stored procedure dumps into its own global temp table. The final stored procedure needs to insert into a sql table all the information gathered in the global temp tables. So the final stored proc. looks something like:
"Create procedure usp_myProc_4 AS EXEC usp_myProc_3
INSERT INTO mySQLTable (a,b,c)
SELECT a, b, c FROM ##myTempTable (which was created in usp_myProc_1)
INSERT INTO mySQLTable (a,b,c)
SELECT a, b, c FROM ##myOtherTempTable
INSERT INTO......etc;"
I have done this befor and it worked fine. The only difference is that when I did this before these insert statements were being called from within an sp_makewebtask procedure.
Now when I try to save this final stored procedure it tells me "Invalid Object Name: ##myTempTable"
How do I call on these global temp tables from my final nested stored procedure?
Thanks for any help.
View 1 Replies
View Related
