Multiple Db Query Call From Within Different Context Into #temp Table
Mar 21, 2007
The first query returns me the results from multiple databases, the
second does the same thing except it puts the result into a #temp
table? Could someone please show me an example of this using the first
query? The first query uses the @exec_context and I am having a
challenge trying to figure out how to make the call from within a
different context and still insert into a #temp table.
DECLARE @exec_context varchar(30)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @exec_context = @DBNAME + '.dbo.sp_executesql '
set @sql = N'select top 10 * from products'
exec @exec_context @sql
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
-------------------------------------------------------------------------------------
CREATE TABLE #Test (field list here)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
ORDER BY NAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'
INSERT INTO #Test
exec (@sql)
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
SELECT * from #Test
DROP TABLE #Test
View 1 Replies
ADVERTISEMENT
Mar 5, 2015
I'm trying to do something like this:
Loop through #Temp_1
-Execute Sproc_ABC passing in #Temp_1.Field_TUV as parameter
-Store result set of Sproc_ABC into #Temp_2
-Update #Temp_1 SET #Temp_1.Field_XYZ= #Temp_2.Field_XYZ
End Loop
It appears scary from a performance standpoint, but I'm not sure there's a way around it. I have little experience with loops and cursors in SQL. What would such code look like? And is there a preferable way (assuming I have to call Sproc_ABC using Field_TUV to get the new value for Field_XYZ?
View 2 Replies
View Related
May 8, 2008
If an application makes a connection under a given user, performs the following:
- calls a stored procedure which performs an insert,
- the insert causes a trigger to be fired
- the trigger causes a sql statement to execute against a different
database
Questions:
* Does the user who called the procedure, also need permissions to this 2nd database ?
* What security context does this trigger get executed ?
I have a stored procedure that is being called by my .Net application. The user account that makes the connection has execute permission to the procedure. However, the procedure does an insert which causes a trigger to fire. This trigger does a sql statement against a table in a different database, which the user who is calling the procedure, does not have permissions to access. Im getting an exception when I call the procedure, and it basically says there was a permission error with the user accessing this other database.
View 2 Replies
View Related
Aug 29, 2013
I have the following query and I would like to combine it into one query instead of using several temp tables.
IF OBJECT_ID('Tempdb..#a') IS NOT NULL DROP TABLE #a
SELECT *
INTO #a
FROM #Web_table w WITH(NOLOCK)
WHERE w.generated_date IS NOT NULL
IF OBJECT_ID('Tempdb..#b') IS NOT NULL DROP TABLE #b
[code]....
View 2 Replies
View Related
Apr 4, 2006
Hello. I'm having some difficulty trying to output the results of two seperate queries into the same temporary table.
Does anyone know if it is possible to use the UNION operator to output the results into a temporary table?
Select * From TableA
UNION
Select * From TableB
<---output result to temporary table here--->
Alternatively, is it possible to output the results of two queries in to the same temporary table without the UNION clause?
The following statement fails on the second SELECT INTO due to the fact that #MyTempTable already exists.
Select * INTO #MyTempTable FROM TableA
Select * INTO #MyTempTable FROM TableB
Thanks in advance.
View 2 Replies
View Related
Feb 22, 2007
I have to select several field values from a table and need to assign them to different variables in my SP.Here's what I do now:
declare @ReceiverEmail nvarchar(50)
SET @ReceiverEmail=(SELECT Email FROM Users WHERE UserCode=@UserCodeOwner)
declare @UsernameSender nvarchar(50)
SET @UsernameSender=(SELECT Username FROM Users WHERE UserCode=@UserCodeOwner)As you can see I have to search the Users table twice: once for the Email and a second time for the Username...and all that based on the SAME usercode...:SSo, is there an option where I only have to search the table once and return the Email and UserName fields and assign them to my variables (without using a temp table....)?
View 4 Replies
View Related
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
Jun 30, 2015
I have a temp table with the following columns and data
drop table #temp
create table #temp (id int,DLR_ID int,KPI_ID int,Brnd_ID int)
insert into #temp values (1,2343,34,2)
insert into #temp values (2,2343,34,2)
insert into #temp values (3,2343,34,2)
[Code]....
I use the rank function on that table and get the following results
select rank() over (order by DLR_ID,KPI_ID,BRND_ID ) Rown,* from #temp
I am interested only in Rown and Id columns. For each Rown number, I need to get the min(ID) in the second column and the duplicate ID should be in 3rd column as shown below.If i have 3 duplicate IDs , I should have 3 rows with 2nd column being the min(id) and 3rd column having one of the duplicate ids in ascending order(as shown in Rown=6)
View 7 Replies
View Related
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
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
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
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
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
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
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
View Related
Aug 2, 2007
We are seeing a regression bug with the Microsoft JDBC driver 1.2 CTP.
Using this driver, we don't seem to be able to call stored procedures which return a result set, if those stored procedures use temporary tables internally.
The 1.2 CTP driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases.
The previous 1.1 driver, suceeds in both cases.
Here is a test case which demonstrates the problem (with IP addresses and logins omitted). The prDummy stored procedure being called is quite simple, and I've copied it below:
Code Snippet
public class MicrosoftJDBCDriverCallingStoredProceduresTest extends TestCase {
// CREATE PROCEDURE [dbo].[prDummy]
// AS
//
// CREATE TABLE #MyTempTable (
// someid BIGINT NOT NULL PRIMARY KEY,
// userid BIGINT,
// )
//
// SELECT 1 as TEST2, 2 as TEST2
// GO
public void testStoredProcedureViaDirectJDBC() {
Connection conn = null;
String driverInfo = "<unknown>";
String dbInfo = "<unknown>";
try {
// Set up driver & DB login...
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://xxx.xxx.xxx.xxx:1433";
Properties dbProps = new Properties();
dbProps.put("databaseName", "xxxxxx");
dbProps.put("user", "xxxxxx");
dbProps.put("password", "xxxxxx");
// Get a connection...
conn = DriverManager.getConnection(connectionUrl, dbProps);
driverInfo = conn.getMetaData().getDriverName() + " v" + conn.getMetaData().getDriverVersion();
dbInfo = conn.getMetaData().getDatabaseProductName() + " v" + conn.getMetaData().getDatabaseProductVersion();
// Perform the test...
CallableStatement cs = conn.prepareCall("{CALL prDummy()}");
cs.executeQuery();
// If the previous line executes okay, the test is passed...
System.out.println("Accessing "" + dbInfo + "" with driver "" + driverInfo + "" calls the stored procedure successfully.");
}
catch (Exception e) {
// Fail the unit test...
fail("Accessing "" + dbInfo + "" with driver "" + driverInfo + "" fails to call the stored procedure: " + e.getMessage());
}
finally {
// Close the connection...
try { if (conn != null) conn.close(); } catch (Exception ignore) { }
}
}
}
The output of this test under both drivers and accessing both databases is as follows:
Code Snippet
Accessing "Microsoft SQL Server v8.00.2039" with driver "Microsoft SQL Server 2005 JDBC Driver v1.1.1501.101" calls the stored procedure successfully.
Accessing "Microsoft SQL Server v9.00.3042" with driver "Microsoft SQL Server 2005 JDBC Driver v1.1.1501.101" calls the stored procedure successfully.
Accessing "Microsoft SQL Server v8.00.2039" with driver "Microsoft SQL Server 2005 JDBC Driver v1.2.2323.101" fails to call the stored procedure: The statement did not return a result set.
Accessing "Microsoft SQL Server v9.00.3042" with driver "Microsoft SQL Server 2005 JDBC Driver v1.2.2323.101" fails to call the stored procedure: The statement did not return a result set.
View 17 Replies
View Related
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
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
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
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
Mar 1, 2004
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
View 10 Replies
View Related
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
Jun 7, 2012
I have a database that has 370 tables that match %_REF_% naming. All of these tables have a column call ds_name.
I need to get a list of all values of ds_name for each table.
View 3 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
May 30, 2008
I was wondered what the most efficient way to do the following in MS SQL (2000, 2005, and above):
Psudo Code:
DECLARE @var1, @var2, @var3 VARCHAR(100)
SET @var1, @var2, @var3 = (SELECT var1, var2, var3 FROM Table WHERE [ID] = @someID) <-- Returns only one row
Right now I'm making a call for every variable. I should play around with temp tables but seems like allot of overhead code just to get 3 values. I'm thinking there's a simple way to do this.
Thanks for any ideas
View 3 Replies
View Related
Apr 5, 2008
Hi,
I'm a beginner with query sql and i'm french sorry for my english !
i have 3 table in sql server 2005:
PERSONNE: person_id (clef), nom (varchar), prenom (varchar)
FORMATION : formation_id (clef), person (foreign key), titre (varchar), actif (bit)
EXPERIENCE: experience_id(pk), personne (foreign key), description (varchar), actif(bit)
I would like to display the person_ID, the total row for formation actif = 1 by person and formation actif = 0 by person and experience actif = 1 by person and experience actif = 0 by person
the result must be:
person_ID nbFormationActif NbFormationInactif NbExpActfi
1 2 5 4
2 1 2 5
...
NbExpInactif
0
3
I have doing a simple query who display total experience actif and inactif for all people but now i'm must to do the SAME THING in the SAME QUERY FOR FORMATION and i'm still block!!!
someone can help me ?
SELECT PERSONNE.Person_ID ,COUNT(PERSONNE.Person_ID) AS totExpAct, totExpInactif
FROM
(
SELECT PERSONNE.Person_ID ,COUNT(PERSONNE.Person_ID) AS totExpInactif
FROM PERSONNE INNER JOIN EXPERIENCE
ON PERSONNE.Person_ID = EXPERIENCE.Personne
AND EXPERIENCE.Actif = 0
GROUP BY PERSONNE.Person_ID
)
PERSONNE INNER JOIN EXPERIENCE
ON PERSONNE.Person_ID = EXPERIENCE.Personne
AND EXPERIENCE.Actif = 1
GROUP BY PERSONNE.Person_ID, totExpInactif
...
Thanks for all
Christophe
Im' null in query :-)
View 1 Replies
View Related
Apr 15, 2008
Hi friends,
I have three table named as Eventsmgmt,blogmgmt,forummgmt..
Each table contain the common column named as CreatedDateTime..
I want to get the most recent CreationDateTime from these three table in single query..
Plzz help me its urgent
Thanks
View 20 Replies
View Related
May 6, 2008
Hi Guys,
I have to work with a poorly designed table :(, that has columns
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
Description varchar(8000) null,
ProductTypevarchar(255) null,
The reason it is poorly designed is the table is used to hold questions and answers, all with a 1:1 relationship. Instead of having ID, ProductType, Question, Answer they have unfortunately adopted the approach of the above i.e:
id 1
thisID 3
parentid nuLL
DESCRIPTION: this is a question
id 20
thisID 3_1
parentID 3
DESCRIPTION: this is the answer to the question above
So I am writing a sproc that does this using a temp table. I got this far:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Spencer
-- =============================================
ALTER PROCEDURE [dbo].[GetFAQs]
-- Add the parameters for the stored procedure here
@ProductType varchar(255)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE TEMP
(
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
DescriptionQvarchar(8000) null,
DescriptionAvarchar(8000) null,
ProductTypevarchar(255) null,
)
SELECT
ID,
thisID,
parentID,
Title,
DescriptionQ,
DescriptionA,
ProductType
FROM
A2Z
WHERE
ProductType = @ProductType AND parentID IS NULL
END
GO
This gets all my questions for that product type.
What I need to do is load the questions into my temp table and then run through the a2z table again gaining the answers to the questions (the parentid holds the question ID). The answers then will also get loaded into the temp table.
Any bright sparks out there that can help me?
Cheers
View 9 Replies
View Related
Dec 16, 2004
What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:
SELECT [table1].[field1], [table2].[field1], [table3].[field1], [table4].[field1], [table5].[field1], [table6].[field1], [table7].[field1]
FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7]
WHERE
[table4].[field2]=[table1.field2]{this is an inner join}
[table4].[field2]=[table2.field2]{this is an inner join}
[table4].[field2]=[table3.field2]{this is an inner join}
[table4].[field2]=[table5.field2]{this is an inner join}
[table5].[field3]=[table6.field2]{this is an inner join}
[table5].[field4]=[table7.field2]{this is needs to be a left join}
As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.
View 2 Replies
View Related
Aug 1, 2014
I have a table named LEDGER
LEDGER has two columns named PATID and CODE
Example data:
PATID CODE
1 Z1110
1 D3330
1 Z0330
2 Z1298
2 Z0987
2 Z0330
2 D1092
I need a query that returns PATID if they have CODE Z0330 but not Z1110. I only want one PATID to return if this condition exists.
View 5 Replies
View Related
Jul 23, 2005
Hey,I am having some confusion about how to formulate this particularquery.I have 2 tables. Table A has 4 columns say a1,a2,a3,a4 with thecolumns a1,a2,a4 forming the primary key. Table B again has 3 columnswith b1,b2,b3,b4 and like before, b1,b2 and b4 form the primary key.All columns are of the same datatype in both tables. Now I want to getrows from table A which are not present in table B. Whats the best wayof doing this?Thanks--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict235166.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815725
View 6 Replies
View Related
Jul 20, 2005
I want to get a column count several times in one query using differentfilters but can't work out how to do it - can anyone point me in the rightdirection?For example, how would combine these two selects into one query that willlist the total and filtered actions:SELECT COUNT(actions) as actioncount, locationFROM mytableGROUP BY locationSELECT COUNT(actions) as actioncount, locationFROM mytableWHERE mycondition IS NULLGROUP BY location
View 6 Replies
View Related
Mar 19, 2008
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?
Thanks, Gary
View 9 Replies
View Related