Inserting Results From Query Into New Temp Table

Sep 17, 2013

I am try to insert the results from the query into new temp table but keep geeting an error for Incorrect syntax near ')'.

select * into tempCosting
from
(
select top 10* from itemCode itm
where itm.type= 1
)

View 3 Replies


ADVERTISEMENT

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
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,

[code]....

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.

CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,

[code]....

SQL Server 2008 R2.

View 3 Replies View Related

Inserting Into Temp Table..

Mar 10, 2006

Hi,
I have a proc, if i run that proc from query analyzer it completes in 12 sec.
I want those results to be inserted into temp table. When i try to insert the results from Stored proc to temp table it is taking 5 min.

What could be the cause of the problem?
Any help would be greately appreciated.
sskris

View 4 Replies View Related

Inserting Into Temp Table In Dynamic Sql

Mar 3, 2004

I have a temp table in my stored procedure when I try to insert into the temp table thro dynamic sql, it says that table has to be defined. What could be the problem. i have added the code below

CREATE PROCEDURE USP_RULE
AS
declare @TABLE1 table
(
SlNo int identity(1,1), EqNum varchar(25),Pointnum varchar(25)
)
declare @EqNum varchar(25),@Pointnum varchar(25)
DECLARE @STRDBNAME VARCHAR(50)
SET @STRDBNAME = 'DB1'
EXEC('insert into '+@TABLE1+' select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')

GO

View 4 Replies View Related

Inserting Nulls Into A Temp Table

Mar 3, 2008

I'm trying to insert any null values into a temp table. I'm storing the ixo_rlt_code as OldRole, and 'Other' as NewRole. I need both the ixo_rlt_code as OldRole and 'Other' as NewRole in the temp talbe in order to run an update I'm writing.

I know a case statement isn't necessary here, but this section is only one small part of a larger script, so I just used it to be consistent, and it will be easier to make any future changes this way. Below is my code and the error message I'm getting...


select
ixo_key,
ixo_title as Title,
ixo_rlt_code as OldRole,
CASEWHEN ixo_title is null
or ixo_title = ''
or ixo_title = ' '
or ixo_title = ' ' THEN 'Other'

END as NewRole


from
co_customer (nolock)
join co_individual_x_organization (nolock) on ixo_key = cst_ixo_key --and ixo_delete_flag = 0
where
cst_delete_flag = 0
and cst_type = 'Individual'
and ixo_rlt_code is null


This is the Error Message I get:
Server: Msg 515, Level 16, State 2, Line 1095
Cannot insert the value NULL into column 'NewRole', table 'tempdb.dbo.#temp_______________________________________________________________________________________________________________00000001698C'; column does not allow nulls. INSERT fails.
The statement has been terminated.


Thanks ahead of time for any help/suggestions

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

CREATE TABLE #revf (
[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

DELETE
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

Intermittent Timeout When Inserting Into Temp Table

Sep 27, 2001

I have an ASP page that opens a recordset on a stored procedure. I've had it
in production for over a year, and it usually works fine - subsecond
response. However, in the past couple weeks, the recordset open command has
intermittently started to return timeout errors. It's very strange: when the
timeout occurs, it can be dependably reproduced by opening the ASP page with
the same parameter that produced the initial timeout. However, if you pass a
different parameter to the procedure, it runs fine! It only times out when
using the specific parameter that produced the initial timeout.

Even stranger: If, when attempting to debug it, I use QA to run the sp with
the "defective" parameter, the sp invariably runs fine! And subsequent runs
of the ASP page start working as well!

I have repeated this process many times in the past couple weeks: receive a
report of the timeout error; open the asp page with the parameter that
produced the timeout to verify; repeat; open the asp page with a different
parameter - works fine; repeat with the initial parameter - timeout; Open QA
and run the sp - no problem; refresh the asp page - no more symptom.

The connection to the SQL Server (7.0) uses a specific SQL login for all
communications with the server. So all asp pages are logged in under the
same name. I thought this might be a connection pooliing issue, so this
morning, when the problem occurred, I logged into QA as the special name,
but again, there was no problem running the sp from QA.

So - I've run SQL Profiler to track what's going on when the problem occurs
and I've discovered the last statement to successfully run. Here is the sp:

/*
* File Name: np_GetAvailablePreviews.sql

Returns a list of "reports" available to be previewed, defined as:

date/shift combination with "P" mode and "C" status (if there
are Shift records, there must be an All record)

If none exist, check for the existence of accepted (mode = "A")
Shift records without a corresponding All record - if so, return
an All item for that date

Concatenate <OPTION> tags around each item
*/

CREATE PROCEDURE np_GetAvailablePreviews
(
@DivisionNumber smallint)
AS
BEGIN
set nocount on
declare @count int,
@datefrom datetime
select @datefrom = dateadd(day,-900,getdate())
CREATE TABLE #PreviewReports
(
ProductionDate datetime NULL,
Shift char(3) NULL
)

Insert #PreviewReports
SELECT DISTINCT ProductionDate,Shift
FROM dbo.LineItems
WHERE DivisionNumber = @DivisionNumber
AND Mode = 'P' AND Status = 'C'
AND productiondate >= @datefrom

-->>>The above command completes successfully according to Profiler

Insert #PreviewReports
SELECT distinct t1.ProductionDate, 'All'
FROM
(SELECT distinct ProductionDate, Shift, Mode, Status
FROM LineItems
WHERE DivisionNumber = @DivisionNumber
AND Shift<>'All' AND Mode='A' AND Status='C'
AND productiondate >= @datefrom AND timeperiod IN
('Day','Shift')
) t1
LEFT JOIN
(SELECT distinct ProductionDate, Shift, Mode, Status
FROM LineItems
WHERE DivisionNumber = @DivisionNumber
AND Shift='All' AND Mode='A' AND Status='C'
AND productiondate >= @datefrom AND timeperiod IN
('Day','Shift')
) t2
ON t1.ProductionDate = t2.ProductionDate
WHERE t2.ProductionDate Is Null
AND NOT EXISTS (Select * from #PreviewReports
where productiondate = t1.ProductionDate
and Shift = 'All')

-->>>This one (the one before this comment) starts, but does
not -->>>complete

set @count=(Select Count(*) From #PreviewReports)

-->>This one never begins - the ADO command times out

<snipped>

I did not provide a repro script because the problem is so intermittent that
I doubted it would do anyone any good.

However if someone feels they want to try to reproduce this, I will attempt
to create a repro script for them.

I suspect there is an issue with the temp table, but it's such a moving
target that I haven't been able to pin it down.

Does anyone have any suggestions?

TIA,
Bob Barrows

View 1 Replies View Related

T-SQL (SS2K8) :: Inserting FK Information Into Temp Table

Jan 7, 2015

I need to insert FK information into a temp table using database name and table name as a parameter.

I've been trying different ways, even with global temp table, but still doesn't work. Below is the code showing what I am trying to achieve. I want to avoid global temp table if possible.

IF OBJECT_ID('tempdb..##FKs') IS NOT NULL DROP TABLE ##FKs
CREATE TABLE ##FKs (
[ForeginKeyName] [nvarchar](128) NULL,
[TableSchema] [sysname] NULL,
[TableName] [nvarchar](128) NULL,
[RelatedTableSchema] [sysname] NULL,

[Code] .....

View 9 Replies View Related

Insert Into Temp Results Of A Union Query

Nov 20, 2007

Hi,
I have follwing union query. I want to put this all in a temp table.

select Store_Id,batchnumber
From
Adjustments where updatedDt between '10/30/2007' and '11/20/2007' and Store_id in(8637 ,8641)
group by Store_Id, batchnumber
Union
select DestinationId,b.batchNumber
from
batch b
inner join Carton C on C.Carton_Id = b.General_ID
inner join Document d on d.Document_Id = c.Document_Id
where b.BatchType = 'Warehouse' and b.TranTable = 'Carton'
and (d.DestinationId in (8637 ,8641) ) and c.UpdatedDt Between '10/30/2007' and '11/20/2007'
Union
select d.DestinationId,b.Batchnumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferIn' and b.TranTable = 'Document'
and (d.DestinationId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
Union
select d.SourceId,b.batchNumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferOut' and b.TranTable = 'Document'
and (d.SourceId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
order by batchnumber

Kindly advice.

Thanks
Renu

View 2 Replies View Related

How Do I Store Results In TEMP TABLE?

Jul 7, 2005

I'm writing a search engine and I want to make a search and then, after
I've recieved the results, put them into some sort of temporary(in
mememory) table so that I can do another query on that temporary table.

I saw something about  temp tables (in T-SQL)  in the
help guide, but I still want to know how to do a query on a regular
table and then store those results in a temporary table to perform a
query on that. Could anyone show me some example or something?

View 3 Replies View Related

Do QA Results Into A Temp Table Automatically?

Apr 3, 2008

Does anyone know if the results from a query run in QA automatically go into a tempoary table? For instance if ive just run a query that took 5 minutes but it was a normal select query and after it ran i realised i wanted to query the results could i run a simple command to mke a copy of the result set rather than run the original query into a temp table that would then take a few minutes again?

View 1 Replies View Related

Results Of Union Into Temp Table

Apr 3, 2008

This may be a dumb question, but I can't seem to get the syntax right. I have two temp tables that have the same columns, I want to do a union on them and store the results in a temp table. Any ideas?

Ie.

select * from #tmpTable1
union
select * from #tmpTable2
into #tmpTable3

Thanks!!!

View 4 Replies View Related

Help On Dynamic Sql Results On Temp Table

Nov 21, 2006

The dynamic sql is used for link server. What I need is that the result of the dynamic sql put on a temp table. Can someone help. Im getting an error

CREATE PROCEDURE GSCLink

( @LinkCompany nvarchar(50), @Page int, @RecsPerPage int )

AS

SET NOCOUNT ON

--Create temp table

CREATETABLE #TempTable

(  ID int IDENTITY, Name nvarchar(50), AccountID int, Active bit )

INSERT INTO #TempTable (Name, AccountID, Active)

--dynamic sql

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT a.Name, a.AccountID, a.Active

                   FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a

                   LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b

                   ON a.AccountID = b.oaAccountID

                   WHERE oaAccountID IS NULL

                   ORDER BY Name ASC'

EXEC sp_executesql @sql

--Find out the first and last record

DECLARE @FirstRec int

DECLARE @LastRec int

SELECT @FirstRec = (@Page - 1) * @RecsPerPage

SELECT @LastRec = (@Page * @RecsPerPage + 1)

--Return the set of paged records, plus an indication of more records or not

SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @LastRec) AS MoreRecords

FROM #TempTable

WHERE ID > @FirstRec AND ID < @LastRec

 

Error:

Msg 156, Level 15, State 1, Procedure GSCLink, Line 22

Incorrect syntax near the keyword 'DECLARE'.

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

Inserting Few Recordsets Into Temp Table Containing NULL Values

Jul 20, 2005

HiI need some help on achieving the following:I wrote a querie which collects Data out of three Tables, the Resultlooks like this:SET NOCOUNT ONDECLARE @ROWINTDECLARE @CURPTNO CURSORSET @CURPTNO = CURSORFORSELECT * FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGINSELECT ONE.CYNO, ONE.ROLE, ONE.ROL_BEZ, ONE.PTNO, ONE.NOCY, TWO.TEXTAS NOCY_TEXTFROM(SELECT CY.CYNO,RE.CYNO AS RECYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW)AND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW))) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOThe Result looks like this:RS1:6313,1300,Architekt,99737505,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLRS2:2693265,100,Bauherr,99756900,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberRS3:2691919,100,Bauherr,99755058,NULL,NULL2691962,6000,Kontakt,99755058,NULL,NULLMy Problem is, that I need to have all the Resultsets in one Table atthe end.So my further undertaking was to create a Temp Table with theexpectation to receive all the resultsets in one Step.The TSQL for this looks like that:SET NOCOUNT ONCREATE TABLE #CYRE_TEMP(CYNOINT NULL,ROLEINT NULL,ROL_BEZVARCHAR (60) NULL,PTNOINT NULL ,NOCYINT NULL,TEXTVARCHAR (60) NULL)GODECLARE @CYNOINT,@ROLEINT,@ROL_BEZVARCHAR (60),@PTNOINT,@NOCYINT,@TEXTVARCHAR (60),@ROWINT,@CURPTNOCURSORSET @CURPTNO = CURSOR FORSELECT PTNO FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGININSERT INTO #CYRE_TEMP (CYNO, ROLE, ROL_BEZ, PTNO, NOCY, TEXT)VALUES(@CYNO,@ROLE ,@ROL_BEZ ,@PTNO ,@NOCY ,@TEXT)SELECT @CYNO = ONE.CYNO,@ROLE = ONE.ROLE,@ROL_BEZ = ONE.ROL_BEZ,@PTNO = ONE.PTNO,@NOCY = ONE.NOCY,@TEXT = TWO.TEXTFROM(SELECT CY.CYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROWAND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROW)) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOSELECT * FROM #CYRE_TEMPDROP TABLE #CYRE_TEMPGOAnd the Output looks like this now:Q1:NULL,NULL,NULL,NULL,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberCan someone help me getting all the 6 Rows into one Table as Output?I appreciate any available Help on this..Ssscha

View 1 Replies View Related

Inserting Rows Into A Temp Table Created By An Execute Statement

Dec 16, 2007

Below is a simplified table & dataset to illustrate a problem I'm experiencing with a more complex one.





Code Block

create table #test(
recno smallint PRIMARY KEY,
value decimal (18,2))

insert into #test values (1, 3.57)
insert into #test values (2, 5.32)
insert into #test values (3,6.29)
insert into #test values (4, 9.25)
insert into #test values (5, 0.84)

Method 1: I tried inserting rows from #test into a temp table (#table) as follows




Code Block

declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
However, this yields an error message:




Code Block

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name '##table'.
Note - you can comment out the insert into ##table line above to view the results that I'm trying to put into ##table.


Method 2: next I tried explicitly creating ##table & rerunning the loop containing the insert




Code Block

create table ##table (
recno smallint,
value decimal (18,2),
originalrecno smallint)

declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
This worked - it inserted the data from the select statements in the loop into ##table.

Question - why won't method 1 work?

View 6 Replies View Related

Trigger Error When Inserting Stored Proc Output Into Temp Table.

Feb 18, 2008





I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?

--Table being called.


ALTER PROCEDURE dbo.wbGetxxxxxUserReturns

@nxxxxtyId smallint,

@sxxxxxxxxUser varchar(32),

@sxxxxName varchar(32)

AS

SET NOCOUNT ON




CREATE TABLE #Scorecard_Returns

(
NAME_COL varchar(64),
ACCT_ID int,

ACCT_NUMBER varchar(10),

ENTITY_ID smallint,

NAME varchar(100),

ID int,

NUM_ACCOUNT int,

A_OFFICER varchar(30),

I_OFFICER varchar(30),

B_CODE varchar(30),

I_OBJ varchar(03),

LAST_MONTH real,

LAST_3MONTHS real,

IS int

)




ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest



AS

SET NOCOUNT ON




CREATE TABLE #Scorecard_Returns

(
NAME_COL varchar(64),
ACCT_ID int,

ACCT_NUMBER varchar(10),

ENTITY_ID smallint,

NAME varchar(100),

ID int,

NUM_ACCOUNT int,

A_OFFICER varchar(30),

I_OFFICER varchar(30),

B_CODE varchar(30),

I_OBJ varchar(03),

LAST_MONTH real,

LAST_3MONTHS real,

IS int

)

INSERT #Scorecard_Returns(

NAME_COL ,

ACCT_ID

ACCT_NUMBER ,

ENTITY_ID,

NAME,

ID,

NUM_ACCOUNT,

A_OFFICER,

I_OFFICER,

B_CODE,

I_OBJ ,

LAST_MONTH

LAST_3MONTHS,

IS

)

EXEC ISI_WEB_DATA.dbo.wbGetxxxxxcardUserReturns

@nId = 1,

@sSUser = 'SELECTED USER',

@sUName = 'VALID USER'

View 4 Replies View Related

Storing The Stored Procedure Results In A Temp. Table

Mar 28, 2008

All,

I'm trying to store the results of my store procedure in a temp table. when I try it, I got the error saying...

"Insert exec cannot be nested"

I suspsect this is because I have a Insert Exec statement inside my stored procedure...

Is there any way to come over this issue ?

syntax of my code...

create table #Temp1 (ID int)

insert into #Temp1
EXEC SP1

when I try to run the above code I get the error "Insert exec cannot be nested"

SP syntax :

Create Procedure SP1
as
Begin


create table #Temp2
statements.....


Insert into #temp2
exec SP<Name>


staments.. cont...

END

View 5 Replies View Related

T-SQL (SS2K8) :: Returning Stored Procedure Results Into CTE Or Temp Table?

Aug 20, 2013

Is it possible to return the results of a stored procedure into either a CTE or temp table?

In other words, is it possible to do this:

with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???

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

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

How To Use More Than One Query To Fill Temp Table

Jan 21, 2013

I have three queries that I use to determine what the party type of a person.

They will be either a child, family member or an associated party and I need to go to three different tables to find this out. I set a field "relationship" to child, family member or associated party depending on the query results.

I would like to fill a temp table with the results so I can use it in reporting.

Here is my code:

Code:
SELECTvap.partyID, relationship
INTO#Relationship
SELECTvap.partyID, Relationship = 'Child'
FROMVolunteerActivityParty vap JOIN
VolunteerActivity va ON va.VolunteerActivityID = vap.VolunteerActivityID JOIN

[code]....

View 5 Replies View Related

Easy Q.. Query Without Temp Table

Aug 7, 2007

How do I rewrite this query so that I do not use a temp table?


SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

INTO #TEMP1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID

SELECT * FROM #TEMP1 WHERE MINR1 = MAXR1

View 3 Replies View Related

Temp Table For Repeatedly Used Sub Query

Nov 3, 2007

Hi,

Here is my query which lists all orders for products supplied by Supplier-3.
A typical Query on the Northwind database i wrote is like this..

Select * FROM [Order Details] WHERE ProductID in
(Select ProductID From Products where SupplierID = 3)

The subquery in Red was used in multiple places in one of my Stored Procedures..

So what i thought was - use a temp table to store the resultset from this subquery, and then use the temp table instead of querying the Products table everywhere..

My Query looked something like this..

Declare @ProductIDs TABLE
(ProductID int)

INSERT INTO @ProductIDs
Select ProductID From Products where SupplierID = 3

Select * FROM [Order Details] WHERE ProductID in
(Select ProductID FROM @ProductIDs)



Well, I expected an increase in performance with the latter approach, but seems my Stored Procedure is taking more time with the second solution..

Would be glad to see ne explanation on this behavior..

Thanks in Advance..

View 2 Replies View Related

Query Oracle Table In SQL &&amp; Put In Temp Table

Nov 6, 2006

Hi all,

I am querying a table in oracle, the server connection to the Oracle database is determined by a criteria. Though how can I put the results from the oracle query into a temp table ?

This is the code i'm using for the query:

DECLARE @cmd VARCHAR(500)
declare @Year varchar(25)
set @Year = '2006'

DECLARE @Link VARCHAR(100)
DECLARE @Table VARCHAR(100)

select @Link = Server from tbl_Conn where Area='Floor'
select @Table = Target_Table from tbl_Conn where Area='Floor'

SET @cmd =
'
select * from OPENQUERY
(
' + @Link + ',
''
UPDATE '+ @Table +'
SET TARGET_VALUE = '+@Value+'
WHERE Date = '+@Year'
''
)
'
EXEC (@cmd)

How do I put the executed results into a TEMP table ?

Rgds,

View 2 Replies View Related

Query Output As Text Rather Than A Temp Table..

Dec 3, 2006

Hi Guys,

I'm trying to figure out how to output a query as text instead of a tempory table...

I thought perhaps i could use this:

SELECT PRINT CustomerID FROM ORDER_TABLE

But that doesn't work

PRINT CustomerID FROM ORDER_TABLE

Doesn't work either...


I need this so i can print a customised invoice in SQL. That is, unless their is a better way of tackling this problem?

thx for reading :)

--Philkills

View 5 Replies View Related

Multiple Db Query Call From Within Different Context Into #temp Table

Mar 21, 2007

The first query returns me the results from multiple databases, thesecond does the same thing except it puts the result into a #temptable? Could someone please show me an example of this using the firstquery? The first query uses the @exec_context and I am having achallenge trying to figure out how to make the call from within adifferent 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 FORSELECT DBNAMEFROM DBINFOWHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')ORDER BY DBNAMEOPEN companies_cursorFETCH NEXT FROM companies_cursor INTO @DBNAMEWHILE @@FETCH_STATUS = 0BEGINset @exec_context = @DBNAME + '.dbo.sp_executesql 'set @sql = N'select top 10 * from products'exec @exec_context @sqlFETCH NEXT FROM companies_cursor INTO @DBNAMEENDCLOSE companies_cursorDEALLOCATE companies_cursor-------------------------------------------------------------------------------------CREATE TABLE #Test (field list here)declare @sql nvarchar(4000)DECLARE @DBNAME nvarchar(50)DECLARE companies_cursor CURSOR FORSELECT NAMEFROM sysdatabasesWHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULLORDER BY NAMEOPEN companies_cursorFETCH NEXT FROM companies_cursor INTO @DBNAMEWHILE @@FETCH_STATUS = 0BEGINset @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'INSERT INTO #Testexec (@sql)FETCH NEXT FROM companies_cursor INTO @DBNAMEENDCLOSE companies_cursorDEALLOCATE companies_cursorSELECT * from #TestDROP TABLE #Test

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

Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?

Jun 25, 2007

Hi, all experts here,



I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.



With best regards,



Yours sincerely,



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

SQL Server 2008 :: Storing Dynamic Query Output In Temp Table

Apr 6, 2015

I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.

Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!

View 3 Replies View Related

Inserting Into A Tmp Table Using A View --- Please Help Using SQL Query Analyzer

Nov 30, 2006

Hi there,I struggle to get this going i would like to insert data into 2 tmptables in a view.If i run the code on it's own it works perfectly until i want to createa view it complains about the INSERTthis is my codeCreate view dbo.vew_SwitchesAsINSERT INTO tmpInsSelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.INAME,MB.NOfrom Cars BIVLEFT JOIN MountainBikes MBON MB.ID = BIV.IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'IN'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC------ Step 2 -------Into tmpOutsInsert Into tmpOuts ---- All Switches In ----SelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.NAME,MB.NOfrom Cars BIVLEFT JOIN Mountainbikes MBON MB.ID = BIV._IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'OUT'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC----------------------Step 3 ----------------SelectDistinctins.DATE,ins.ID,ins.NAME ,insIND,ins.AMOUNT/100 as AmountIn,outs.IND,outs.AMOUNT/100 as AmountOut,outs.NAME


Quote:

View 4 Replies View Related

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.

CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC, 

[Code] ....

View 7 Replies View Related







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