Does TSQL limits us on creating temp table using dynamic sql? If so any workaround... Here's the sample code that doesn't let me run second exec because it looks like first exec is not able to create a temp table.
declare @str1 varchar(80),@str2 varchar(80)
set @str1='create table #tmp(col1 int)' set @str2='insert into #tmp values (10)'
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?
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?
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
The dynamic sql is used for link server. Can someone help. Im getting an error CREATE PROCEDURE GSCLink ( @LinkCompany nvarchar(50), @Page int, @RecsPerPage int ) AS SET NOCOUNT ON --Create temp table CREATE TABLE #TempTable ( ID int IDENTITY, Company nvarchar(50), AcctID int, IsActive 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'.
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')
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
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.
hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?
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!!
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
I have a database with serveral tables, for example 'customer', I want to update this table with a SSIS package. However, to ensure we don't have issues if the update fails then I've put in an intermediate stage
Using an Execute SQL Task I create temporary tables, for example 'customer_tmp'. Data is then imported into these tables. When all the data is imported successfully the original tables are dropped and the temporary tables are renamed, removing the '_tmp'
This works fine and I'm happy with it. However, if someone adds a column to one of the tables in SQL server it is lost on the next upload.
Similarly I have to hard code creating the indexes into the package as well.
Does anyone know how I could copy the original table definitions and create the temporary tables dynamically. So that any new columns would be picked up?
And indeed is it possible to copy the indexes from one table to another before the drop and rename trick?
Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found". Reason can be Create and Insert statement gets executed as 2 different sessions. How to get the above requirement work fine? Thank you. HV
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.
I am developing a datagrid which will display a summary "report" of sales revenue grouped by a particular field. There will also be a drill-down on this field that will display in more detail the table of data that was used for the summary.
Because the user is selecting multiple criteria under which to run the search, I have created a dynamic query that gathers all the necessary data I need for both the drill down and the summary based on the variable parameters of the user. I've optimized the query that gathers the detailed data (taken from 7 tables) to execute far under a second.
So, that said, here's my question: It seems the most efficient way to get the summary data is to run it against the datatable (which gathers the detailed data) created by the dynamic query and just pass them both into a dataset. Can this be done? Originally, I was calling 2 separate sprocs in my application - one for the detailed data and another for the summary data using a "group by" on the table that was produced by the dynamic query (using "INTO TABLE" in the SELECT statement of the dynamic query). With this method, I received an access exception by asp.net. for the table created by the dynamic query so have dropped the "INTO TABLE", not to mention it increased the performance considerably NOT using the table.
Any suggestion on the most efficient/optimal method to use the data generated from the dynamic query sproc for the summary data? I am trying desparately to avoid running the dynamic query again just for the summary data.
I am a total newbie, so would appreciate any feedback. Also, would someone please tell me the datetime stamp function to print so that I can see how many milliseconds it takes to execute stored procedures?
I have a dynamic query in the stored procedure, and the code looks something like this:
SET @section_test = 'SELECT sectioncode FROM ' + @tablename + ' WHERE sectioncode = "' + @condition + '"' EXEC (@section_test)
What I need to do is try to check if the query returns any values using EXISTS (possibly), but at the same time I don't want to return the results of that dynamic query's select statement in my stored procedure. Is it possible?
What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.
I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.
What I would like to get is just the service codes and the appropriate trigger code for each account.
SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode FROM TriggerTable FOR XML PATH(''), TYPE ).value('(./text())[1]', 'VARCHAR(MAX)') ,1,2,'')
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
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.
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1] and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [dbo].[tmp_ms_xx_MyTable] ( [MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
The SP UserPersist_GetByCriteria does a "SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture", so why am I receiving this error when both tables have the same structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.