SQL 2012 :: Should Change SP To Run Select Into Temp Table

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


ADVERTISEMENT

SQL Question: Change Column Names Of Temp Table In SP

Apr 21, 2004

Hi,
I have a SP which returns a select query on a temp table so I get to choose column names
when I create the #table.

Can I determine column names myself based on the results of another query (in the SP)
before, or (preferably) after I create the #table and populate it.

My query is used to bind to a datagrid so I could use....

dataGrid.Columns[index].HeaderText and set it to a particular output parameter, but I want
to keep the code in the SQL.

Cheers

View 4 Replies View Related

SQL Server 2012 :: Stored Procedures Compiles Even When There Is No CREATE TABLE For A Temp Table

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

SQL 2012 :: Way Of Creating Temp Table

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

SELECT INTO TEMP TABLE

Apr 17, 2007

The following batch does not compile. It works for real tables but not temp tables. I need to get this to work. Any ideas? Thanks.

code
-------------------
IF 1 = 1 --IF CONDITION
BEGIN
SELECT * INTO #TEMP FROM TABLE1
END
ELSE
BEGIN
SELECT * INTO #TEMP FROM TABLE2
END
--------------------
Error Msg
Msg 2714, Level 16, State 1, Line 7
There is already an object named '#TEMP' in the database.
--------------------

View 12 Replies View Related

SQL 2012 :: Temp Table Sort Order

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

SQL 2012 :: How Many Records Can Insert Into A Temp Table

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

SQL 2012 :: Createing Temp Table But Got Error

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

SQL 2012 :: 1.5 Million Records Into Temp Table

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

SQL Server 2012 :: Adding New Row In Temp Table

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

Select Data From #temp Table In SQL

Apr 6, 2004

I am building a dynamic query stored procedure. I am first filling a temp table with data:

Declare
@Counter int

drop table #tempmerge
create table #tempmerge(IDIndex int IDENTITY, CitationNum char(9),Exp1 int)

insert into #tempmerge
Select E_Cit_For_Merge, Count(*) as Exp1
from dbo.E_Citation_XML_Data
group by E_Cit_For_Merge
having Count(*)>1
select * from #tempmerge

Results returned from #tempmerge table:

IDIndex CitationNum Exp1
----------- ----------- -----------
1 4AA020621 2
2 4AA022361 2
3 4AA022391 2
4 4AA022423 2
5 4AA022532 3
6 4AA027761 2
7 4AA030513 2

Then, I want to use a while loop, looping thru the #tempmerge table
and retrieving the CitationNum value of each row:

set @RowCount = (Select Count(*) from #tempmerge)
set @Counter = 1
While @Counter <= @RowCount
Begin

Set @WhereStatement2 = ' where E_Cit_For_Merge= (Select CitationNum from #tempmerge
where IDIndex = @Counter)'

E_Cit_For_Merge is a field in a SQL table.
I Declare @Counter as int.

I get the Error message that:

FROM E_Citation_XML_Data where E_Cit_For_Merge= (Select CitationNum from #tempmerge
where IDIndex = @Counter)

Server: Msg 137, Level 15, State 2, Line 24
Must declare the variable '@Counter'.

Any Suggestions?
Thanks
JEB

View 6 Replies View Related

Temp Table Or Nested Select

Jan 30, 2007

hi all,
i have speed issue on displaying 4k line of records using temp table.. before this it works fine and fast.. but maybe when i starts joining group by it loads slower.

SELECT DISTINCT customlotno, itemid, ItemName, Ownership, TotalCTNInPlt, TotalCarton, sum(CartonPcs) AS CartonPcs, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
INTO #ByItemID
FROM (
SELECT * FROM tblItemdetail
)AS L1
GROUP BY customlotno, itemid, ItemName, ownership, TotalCTNInPlt, TotalCarton, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate

SELECT *
FROM #ByItemID
ORDER BY CustomLotNo

DROP TABLE #ByItemID

----------------------------
or maybe just use something like nested SELECT like this, but cannot work:-

select customlotno, itemid, locid(

select * from tblitemdetail
where customlotno='IN28606000'

) AS T
GROUP BY customlotno, itemid, locid


~~~Focus on problem, not solution~~~

View 12 Replies View Related

SQL 2012 :: Import Txt Data File Into Temp Table?

Sep 30, 2014

In Access, I can import a txt file data(e.g. Claims.txt) as below specifications:

Choose the delimiter that separates your fields: Other (|)
First row contains field name: Yes
Text Qualifier:"

I need to create a store procedure to read txt file data (d:cliamcliams.txt) first column (ClaimNumber) into a temp table (#claim)

(This #claim table will use for my .Net program)

There about 5 txt files need to process every day.

View 1 Replies View Related

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 View Related

SQL 2012 :: Inserting Data Into Temp Table Using 2 While Loop

Apr 21, 2015

I want to insert data (month&year) from 2014 till now - into temp table using 2 while loop.

drop table #loop
create table #loop
(
seq int identity(1,1),
[month] smallint,
[Year] smallint

For some reason I cant not get 2015 data .

View 4 Replies View Related

Can I Make A Temp Table With A Union All Select?

Mar 26, 2008

I'm having trouble creating a temp table out of a select statement that uses multipe union alls.

Here's what I have, I'm trying to get the results of this query into a temp table...

select
parent,
(select cst_id from co_customer (nolock) where cst_key = Parent) as cst_id,
(select cst_name_cp from co_customer (nolock) where cst_key = Parent) as cst_name_cp,
(select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent) as Parent_Total_assets,
sum(own_assets) as Total_child_own_assets

from
(
Select parent,
Child,
(select org_own_assets_ext from dbo.co_organization_ext where org_cst_key_ext = child) as Own_assets

from
(Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,1) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,1) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,2) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,2) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,3) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,3) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,4) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,4) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,5) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,5) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,6) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,6) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,7) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,7) is not null )as c
) as d

group by parent

having sum(own_assets) <> (select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent)

View 8 Replies View Related

Transact SQL :: Select Values From Temp Table

Jul 10, 2015

DECLARE @Query varchar(8000)
Create Table
#tempCountRichard (Status varchar(50), Number Varchar(1000)
Set @Query = 'Insert Into #tempCountRichard
Select Count(Status),

[code]...

With the following SQL. When I select from the temp table I return the right count but my second column doesn't return anything.

Count Status
1010 2000
1111 2222

When I run the query that is being inserted into the the temp table I return the correct results

Count Status
1010 Pass
2000 Pass with Obs
1111 Fail
2222 None

how to select the data from the temp table exactly the same way it was inserted. As I need to select the exact same data from the insert.

View 23 Replies View Related

SQL 2012 :: Import Excel XLSX Files Into Temp Table

Feb 18, 2014

I am having with trying to import XLSX files into SQL 2012 64 Bit.

I have installed the Access driver (AccessDatabaseEngine_x64.exe)

I have configured the script to run the following SP

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1

[Code] ....

So I first create my Temp Table

The run the SP above then I run the insert into the Temp table defined

INSERT INTO tempdb.dbo.TempTRBZ (IsNew,CoID, Zip, City, County,StateCode,Rate,Taxable,TaxShip,TaxLab,CountryID,StateID)

SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=C:TempNotInTrbzJan.xlsx;HDR=YES','SELECT * FROM [Data$]')

[Code] ....

The error message I get back is

Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

What I have set wrong on the import? Using SSIS at this point is not a real option.

View 0 Replies View Related

SQL 2012 :: Partially Contained Database And Temp Table Creation

Nov 20, 2014

As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?

View 0 Replies View Related

SQL Server 2012 :: Storing Data From Temp Table Into A Variable

Aug 4, 2015

I have created procedure that creates a temptable with the users from a certain business unit.

I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.

CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)

[Code] ....

View 6 Replies View Related

SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table

Jul 31, 2014

I need to write a select statement that take the upper table and select the lower table.

View 3 Replies View Related

Select From Multiple Tables, Insert In Temp Table

Feb 18, 2004

What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?


Any ideas?

Thanks in advance.

View 6 Replies View Related

SQL Server 2012 :: Update Temp Table Where The Column Names Are Unknown

Dec 26, 2013

In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.

So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:

UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))

However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.

how can I get the column names of a Temp table dynamically while doing an Update?

View 4 Replies View Related

SQL Server 2012 :: Error Creating Temp Table Based On IF Logic

Nov 13, 2014

I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:

sp_executesql creates a table outside of the scope of my session:
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''

[code]....

View 9 Replies View Related

Transact SQL :: Insert Constant Value Along With Results Of Select Into Temp Table?

Dec 4, 2015

I'm trying to fill a temp table whose columns are the same as another table plus it has one more column. The temp table's contents are those rows in the other table that meet a particular condition plus another column that is the name of the table that is the source for the rows being added.

Example: 'permTable' has col1 and col2. The data in these two rows plus the name of the table from which it came ('permTable' in this example) are to be added to #temp.

Data in permTable
col1   col2
11,    12
21,     22

Data in #temp after permTable's filtered contents have been added

TableName, col1   col2
permTable, 11,     12
permTable, 21,     22

What is the syntax for an insert like this?

View 2 Replies View Related

SQL 2005 Select Into Temp Table Then Insert Causes Null Issue

Jul 20, 2007

Here is the scenario that I cannot resolve



CREATE TABLE [dbo].[tEvents](

[EventID] [int] IDENTITY(1,1) NOT NULL,

[EventName] [varchar](1000) NOT NULL,

CONSTRAINT [PK_tEvent] PRIMARY KEY CLUSTERED

(

[EventID] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]



CREATE TABLE [dbo].[tEventSelections](

[EventSelectionID] [int] IDENTITY(1,1) NOT NULL,

[EventID] [int] NOT NULL,

[StatusPID] [int] NOT NULL,

CONSTRAINT [PK_tEventSelections] PRIMARY KEY CLUSTERED

(

[EventSelectionID] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]



then try this



SELECT e.eventName, es.statuspid

INTO #tmpTable

FROM tEventSelections ES

INNER JOIN tEvents E

ON E.EVentID = ES.EventID

INSERT INTO #tmpTable (eventName) values ('Another One')

DROP TABLE #tmpTable



this causes a null insert issue

(0 row(s) affected)

Msg 515, Level 16, State 2, Line 7

Cannot insert the value NULL into column 'statuspid', table 'tempdb.dbo.#tmpTable___________________________________________________________________________________________________________000000000130'; column does not allow nulls. INSERT fails.

The statement has been terminated.



So how do I allow the null, as the not null is coming from the ES table. But I want to allow the insert to happen without having to create the table first, this code works in SQL 2000 but fails in 2005, inserting all fileds into the insert also has it's own issues as some of the fields are delibertly left blank so in some circumstances the data returned to a grid displays correctly.



This method has been used in quite a lot of stored procedures and will be a nightmare to correct if each has to be edited.



One example of the use of is to return a dataset and then add a row at the bottom which is a sum of all the rows.



Regards

View 20 Replies View Related

SQL Server 2012 :: Avoiding Temp Table With String Of IDs Passed Into Stored Procedure

Sep 26, 2014

Using a string of IDs passed into a stored procedure as a VARCHAR parameter ('1,2,100,1020,') in an IN without parsing the list to a temp table or table variable. Here's the situation, I've got a stored procedure that is called all the time. It's working with some larger tables (100+ Million rows). The procedure passes in as one of the variables a list of IDs for the large table. This list can have anywhere from 1 to ~100 IDs passed to it.

Currently, we are using a function to parse the list of IDs into a temp table then joining the temp table to get the query:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] .....

The problem we're running into is that since this proc gets called so often, we sometimes run into tempDB contention that slows this down. In my testing (unfortunately I don't have a good way of generating a production load) swapping the #table for an @table didn't make any difference which makes sense to me given that they are both allocated in the tempDB. One approach that I tried was that since the SELECT query is pretty simple, I moved it to dynamic SQL:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] ....

The problem I had there, is that it creates an Ad Hoc plan for the query and only reuses it if the same list of parameters are passed in, so I get a higher CPU cost because it compiles a plan and it also causes the plan cache to bloat since the parameter list is almost always different. Is there an approach that I haven't considered that may get the best of both worlds, avoiding or minimizing tempDB contention but also not having to compile a new plan every time the proc is run?

View 9 Replies View Related

SQL Server Admin 2014 :: Crystal Report Cannot Select Into Temp Table

Jul 30, 2015

I know select * into ##tmp1 should work but I get a database connection error.

View 9 Replies View Related

Transact SQL :: Select 1000 Rows At A Time From / Into A Large Temp Table?

May 12, 2015

I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.

In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.

For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.

Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?

Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).

View 6 Replies View Related

T-SQL (SS2K8) :: Moving Values From Temp Table To Another Temp Table?

Apr 9, 2014

Below are my temp tables

--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL

[Code] ....

I want to loop through the data from #Base_Resource and do the follwing logic.

1. get the Resourcekey from #Base_Resource and insert into #Resource table

2. Get the SCOPE_IDENTITY(),value and insert into to

#Resource_Trans table's column(StringId,value)

I am able to do this using while loop. Is there any way to avoid the while loop to make this work?

View 2 Replies View Related

SQL 2012 :: Change Properties Of A Table Which Have Filestream When Column Added To It

Dec 6, 2014

I store files in db in sql server 2008 by filestream. But when a column would be added to table which have filestream, properties of table would be changed. by every things change on table, retrieve files will faced to error. but store process work probably.

and filestream filegroup at following address will be empty. why?

Right click on table --> properties --> storage --> filestream filegroup

View 0 Replies View Related

SQL 2012 :: Converting Time String In Temp Table To Military Time Then Cast As Integer?

Dec 26, 2014

I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:

Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')

how this can be done while my temp table is in session?

View 2 Replies View Related

SQL 2012 :: Cannot Truncate Table Because It Is Published For Replication Or Enabled For Change Data Capture

Jul 8, 2015

I get the following error message when a job calls a Stored Procedure that TRUNCATES a Table:

Cannot truncate table 'CombinedSurveyData' because it is published for replication or enabled for Change Data Capture

Is my only option to change the TRUNCATE to DELETE?

[URL]

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved