Transact SQL :: Create Index On Temp Table To Reduce Run Time Of Update Query

Apr 29, 2015

I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!

The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.

Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index. 

And also I am not sure, whether creating index in more columns will create issue or not.

Attached the update query and index query.


[Code] ....

View 7 Replies


Transact SQL :: Create A Temp Table On Results Of A Pivot Query?

Jun 17, 2015

I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.

IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,


The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.".   The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT".  I will post the definitions from another effort below.

--ivo_int_idINTEGERNOT NULL,


SQL Server 2008 R2.

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

Transact SQL :: Update Table With Its Value And Data From Row In Temp Table For Matching Record?

Oct 25, 2015

I have a temp table like this

  ID int,
  Source varchar(50),
  Date datetime,
  CID varchar(50),
  Segments int,
  Air_Date datetime,


Getting Error

Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.

View 4 Replies View Related

Transact SQL :: Create Temp Table That Persists While A Front End App Is Open

Sep 30, 2015

I have an Access app. that I am migrating the DB portion (queries, tables) to SQL server. I need to create a temp table that lasts as long as the user has the Access FE app. open. Idea is that the temp table stores the user's parameters (used for filtering data entry forms and report). The parameters allow the app. to only show the user his data (cannot view other users data). The SP shown below works OK, it creates a ##Temp table and updates it with the parameters sent by Access FE app. The issue I am having is that as soon as the SP finishes the ##Temp table is removed. I thought of using a regular table, but, I am currently testing this migration in my local SQL server instance, as soon as I move the database to production environment, then users will not be able to create tables as permissions are only read/write.

USE [Work_Allocation]
/****** Object: StoredProcedure [dbo].[spUser_Parameters_update] Script Date: 9/30/2015 12:27:42 PM ******/
ALTER PROCEDURE [dbo].[spUser_Parameters_update]


View 10 Replies View Related

Transact SQL :: Confirmation Of UNION ALL Query For INSERT INTO Temp Table

Jul 21, 2015

I have the following UNION ALL query with SELECT INTO @tblData temp table. I would like to confirm if my query is correct.

In my first SELECT statement, I have INSERT INTO @tblData.

Do I need another INSERT INTO @tblData again in my second SELECT statement after UNION ALL?

DECLARE @BeginDate as Datetime
DECLARE @EndDate as Datetime
SET @BeginDate = '7/1/2015'
SET @EndDate = '7/13/2015'
DECLARE @tblData table

[Code] ....

View 3 Replies View Related

Transact SQL :: Create Table Named With Ending Date-time Format

Jun 25, 2015

what would be the TSQL in trying to create a new table with date-time format ending via a select into like:

into tblResults_
from qryResult

View 3 Replies View Related

Reduce Time For Search Query

Feb 26, 2008

Hi,I have a task at hand to reduce the time taken for search query toexecute. The query fetches records which will have to sorted bydegrees away from the logged in user. I have a function whichcalculates the degrees, but using this in the search query slows theexecution and takes about 10 secs to complete which is unacceptable.Please advice. Your help is much appreciatedFor more details plz see:

View 6 Replies View Related

Transact SQL :: Create Hierarchies Table Or Query From Multi Parent Table?

May 21, 2015

convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in application directly.

ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent

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

Transact SQL :: Update Time Portion Of DateTime Field With Time Parameter

Oct 3, 2015

I hope to update a DateTime column value with a Time input parameter.  Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000

@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime


View 14 Replies View Related

Transact SQL :: Query To Update A Table With More Than 150 Million Rows Of Data?

Sep 17, 2015

I have been tasked with writing an update query to update a table with more than 150 million rows of data. Here are the table structures:

Source Tables :

[OC] [nvarchar](255) NULL,
[DATE FIN] [date] NULL,
[Code Article] [nvarchar](255) NULL,
[INSERTION] [nvarchar](255) NULL,

[Code] ....

The update requirement is as follows:

DECLARE @Counter INT=0 --This causes the @@rowcount to be > 0
while @@rowcount>0
    SET rowcount 10000
    update r
    set Comp=t.Comp

[Code] ....

The update took more than 48h and didn't terminate , how to accelerate it ?

View 6 Replies View Related

Temp Table Index Question

Dec 19, 2001

I have a proc which creates a rather large temp table, and then i create an index on this. the problem arises when multiple users call this proc at that same time. the second user gets errors as they cannot create the index because it already exists. i know i can't just name the index #index_name, althought this would be ideal. does anyone know of a way to let multiple users create an index besides using dynamic sql? thanks in advance

View 5 Replies View Related

Existence Of Index For Temp Table

Oct 4, 2007


create table #tmp(a int)

create index idxt1 on #tmp(a)

insert into #tmp values (42)

select * from sys.indexes

where name like '%idx%'

order by name

But I can't see any rows about idxt1 :-(

If I say

create index idxt1 on #tmp(a)

againg I got error, because it exists.

How to check for existence using query?

B. D. Jensen

View 3 Replies View Related

T-SQL (SS2K8) :: Non-clustered Index On Temp Table?

Nov 2, 2010

I am trying to create a temp table with a non-clustered index.

Originally I tried to create the index after I created the table.

This seemed to work fine, so I added my stored procedure to our Production environment.

However, when two users called the stored procedure at once I got the following error:

There is already an object named 'IX_tmpTableName' in the database. Could not create constraint. See previous errors.

I then found that SQL Server does generate unique names for the temp table but not all the objects associated with the temp table if they are explicitly named.

This is easy enough to solve for a PRIMAY KEY or UNIQUE constraint because the do not have to be named.

Is there a way to create an non-clustered index on a temp table without naming it?

View 9 Replies View Related

T-SQL (SS2K8) :: Creating Index On Temp Table

Nov 12, 2014

In a Stored Proc I am creating the following temp table and index:

CREATE TABLE [dbo].[#ShipTo](
[Ship_to_Num] [int] NOT NULL,
[Country_key] [nvarchar](3) NULL,
[ship_to_Num] ASC

The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.

View 9 Replies View Related

Transact SQL :: How To Determine Index Create Date

Nov 7, 2012

Is it possible to determine an index create date in SQL Server 2008 R2?  The index in question is not a primary key.

View 8 Replies View Related

Transact SQL :: Table Structure - Inserting Data From Other Temp Table

Aug 14, 2015

Below is my table structure. And I am inserting data from other temp table.

[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,

[Code] ....

Now in a next step I am deleting the records from #revf table. Please see the delete code below

FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2

[Code] ...

I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:

SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],

[Code] ....

If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.

View 5 Replies View Related

Transact SQL :: Converting 24hrs Time To 12 Hours Time Query Format?

Apr 21, 2015

    CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
    CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance

indate   04/18/2015
time part :17:45:00

I need to convert this 17:45:00 to 12 hours date format...

View 8 Replies View Related

Transact SQL :: Insert Data From Temp Table To Other Table

Oct 5, 2015

I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary  (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.

Actual Data
Expected Result



View 3 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.
INSERT INTO #tmp(dt)

only on calling the proc does this give an execution error

View 3 Replies View Related

Dynamic Create Table, Create Index Based Upon A Given Database

Jul 20, 2005

Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View 1 Replies View Related

I Can't Create A Temp Table

Jul 20, 2005

Hi all!I have a problem with a temp table.I start creating my table:bdsqlado.execute ("CREATE TABLE #MyTable ...")There is no error. The sql string has been tested and when it'sexecuted in the sql query analyzer it really creates the table.After creating the table, I execute an insert statement:bdsqlado.execute ("INSERT INTO #MyTable VALUES(...) "It returns an error like this: "Invalid Object Name #MyTable"I don't understand what's wrong. If I execute both sql sentences inthe SQL Query Analyzer it works perfectly.I use the same connection to execute both statements and I don't closeit before the INSERT is executed.I think it may be something related to dynamic properties of theconnection, but I'm not sure. It's just an idea.Please I need help.Thanks a lot,

View 1 Replies View Related

Transact SQL :: Temp Table Scope On Error

Oct 12, 2015

In my sp I'm have an Insert statement. In the event that after the insert @@rowcount = 0 I'm throwing an error with RAISEERROR.

This is fine, however, in my CATCH block I'm referencing a local temp table but getting an error that it no longer exists.

My question is, does throwing an error drop any #temp tables? I thought they were in scope for the session?

View 4 Replies View Related

Transact SQL :: Drop A Temp Table If It Exists?

Jan 21, 2010

What is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end

View 5 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),


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

Using A Variable To Create Temp Table

Mar 3, 2003

Can someone send me an example of creating a variable to use instead of a temp table? I cannot find an example on books on line, but know it is possible in SQL2000.


View 2 Replies View Related

CREATE A Temp Table Via EXEC (@SQL)

Jan 23, 2006

I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:--------------------------------------------------------------------------------------- Get all SubquestionIDs for this concept-------------------------------------------------------------------------------------DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR SELECT QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesFROM RotationMaster AS RM INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumberWHERE RM.Study = @StudyGROUP BY QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesHAVING QGDM.SubquestionID <> 0--------------------------------------------------------------------------------------- Dynamically create a Temp Table to store the data, simulating a pivot table-------------------------------------------------------------------------------------SET @Count = 2SET @SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'OPEN curStudySubquestionsFETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValuesWHILE @@FETCH_STATUS = 0BEGIN SET @SQL = @SQL + ', Col' + CAST(@Count AS VARCHAR(5)) + ' VARCHAR(10)' SET @Count = @Count + 1 FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues ENDSET @SQL = @SQL + ', ShowOrder SMALLINT)'CLOSE curStudySubquestionsPRINT 'Create Table SQL:'PRINT @SQLEXEC (@SQL)SET @ErrNum = @@ERROR IF (@ErrNum <> 0) BEGIN PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!' RETURN ENDPRINT '*** Table Created ***'-- Test that the table was createdSELECT *, 'TEST' AS AnyField FROM #AllSubquestions The line PRINT @SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):CREATE TABLE #AllSubquestions (Col1 VARCHAR(100), Col2 VARCHAR(10), Col3 VARCHAR(10), Col4 VARCHAR(10), Col5 VARCHAR(10), Col6 VARCHAR(10), Col7 VARCHAR(10), ShowOrder SMALLINT) However, the SELECT statement to test the creation of the table produces this error:*** Table Created ***Server: Msg 208, Level 16, State 1, Procedure sp_SLIDE_CONCEPT_AllSubquestions, Line 73Invalid object name '#AllSubquestions'. It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?

View 4 Replies View Related

Create Temp Table Instead Of View?

Aug 23, 2013

I have a view which works fine but I cannot display the data in the Report tool because its

CCSID is HEX. If I could create it to temp table

I think then there would be an easy way to get around this problem.

This is the code:

CREATE VIEW astlib.acbalmpk AS (
'' as IEPRT#, '.00' as IEQOH#, '' as IELOC1, '' as IELOC2, '' as
IELOC3, '' as IERIDC, '' as IEWHS#

[Code] ....

View 2 Replies View Related

Update Temp Table With Stored Procedure Joined With Table

Sep 8, 2006


Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?

insert #MyTempTable

exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3

JOIN dbo.OtherTable...

I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.

I just can't figure it out

Many Thanks!


View 2 Replies View Related

Update From Temp Table

Jun 9, 2008

I'm trying to update a table from a temporary table but I'm struggling a little with the syntax. Here's what I have so far:

SET @WeekNo = 1

WHILE @WeekNo <= 53
SET @SQL = 'UPDATE [Targets]
SET [Target] = SELECT ISNULL(Week' + Cast(@WeekNo as VarChar(2)) + ', 0) FROM #targets
WHERE [CampaignID] = @CampaignID'
SET @WeekNo = @WeekNo + 1

My temp table gets created OK, it's just setting the 'Target' field to the value of the same field in my temp table.

I get the following errors:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ' + Cast(@WeekNo as VarChar(2)) + '.

Can anyone help?

Thanks in advance,

View 7 Replies View Related

Temp Table And Update

Mar 27, 2008

below is the complete code of two temp talbe , the first temp talbe i created so for others it is easy to see what fields I am copying into the temptable.

So what I am doing is that, copying the values from #RealTable into the #Rpt table. Later I want to check the values into the some column and than on certain condtions I am going to update the values. But the way I am doing is I am using the update statment to change the values, where as I think there must be a better way of doing what I am doing in these codes. So instead of using the updates like 6 times or more I can just use them maybe twice. Or probably not at all.
Please give your idea. Thanks.
Code is running and you can try.
I just depict a litttle code here but in real i have 30 columns and the data is coming from the complex join statments. And I am doin the updates for 8 times.

Create table #RealTable
LastName varchar (20)
,Phone varchar (20)
,MissingInfo varchar (100)
,ErrMessage varchar (255)

Insert INTO #RealTable Values('Abraham','917 250 9999', '1', 'Need More Paper')
Insert INTO #RealTable Values('Sar', '917 999 9999', '2', 'Need ID')
Insert INTO #RealTable Values('John', '732 888 8888', '3', 'Need More Paper')
Insert INTO #RealTable Values ('Sal', '555 555 5555', '4', 'Legal Documentation')

Create table #Rpt
LastName varchar(20)
,Phone varchar(20)
,MissingInfo varchar(20)
,ErrMessage varchar(255)
,TextMessage varchar(255)
,CheckMark varchar(1)

Insert INTO #Rpt
, MissingInfo
,'' AS TextMessage
,'' AS CheckMark
From #RealTable

Select * from #Rpt

--Now i saw the result which is what I have inserted.
--but I want to fill the values of the TextMessage and the CheckMark.
--So there could be a way to do what I am doing following.

Update #Rpt
Set TextMessage='Please send the signed copy'
Where MissingInfo='Need More Paper'

Update #Rpt
Set TextMessage='Send us photo copy of your ID'
Where MissingInfo='Need ID'

Update #Rpt
Set TextMessage='Send the legal copy of the document'
Where MissingInfo='Legal Documentation'

Update #Rpt
set CheckMark='a'
where MissingInfo in ('1','3','4') AND ErrMessage='Need More Paper'

Select * From #Rpt


View 10 Replies View Related

Newbie How To Create Temp Table And Populate

May 8, 2006

Sorry guys I know this is easy but I've been looking for about an hour for a straight forward explanation.
I want to store a user's wish list while they browse the site, then they can send me an enquiry populated with their choices.
Basically, a shopping cart!
I thought of using session variables and string manipulations but I am more comfortable with DB queries.
a simple 4 column table would cover everything.
SQL server and VBScript

View 4 Replies View Related

Copyrights 2005-15, All rights reserved