How To Put The Sp_executesql Result In A Temp Table
Apr 13, 2001I used sp_executesql to execute a dynamically built string containing an SQL statement, is there any way I can insert the results into a temporary table?
View 1 Replies
ADVERTISEMENT
What's Wrong With #result Temp Table?
Dec 27, 2005question: get 10 gram of the gold from Products? i cannot think a good algorithm,just use a clumsy proach to do this job ,but also failed,the last statement occur a Error "#result" have syntax error;
here my code:
--------test condition-------------Create table Qt(id int identity primary key,Q int)Goinsert into Qt(Q) values(8);insert into Qt(Q) values(6);insert into Qt(Q) values(6);insert into Qt(Q) values(5);insert into Qt(Q) values(3);insert into Qt(Q) values(3);insert into Qt(Q) values(1);insert into Qt(Q) values(0);Go-------------------------------
Create procedure Test2( @m int) ASdeclare @i int,@j int,@n int ,@id int ,@q int ,@last intset @i=1;set @j=1;Create table #result(id int, Q int)declare __cursor cursor forselect * from Qt where Qt.Q <=@m order by Qt.q desc for read onlyOpen _cursor;select @n=@@cursor_rows;fetch last from _cursor into @lastif @n > 0 -------------------beginBegin_this:fetch absolute @i from __cursor into @id,@qif @q=@m begininsert into #result(id,Q) values(@id,@q);GoTo End_this;End-------------------else-------------------------------begin------------------------if @q=@lastbeginSet @j=@j+1;------------if @j>@n Goto End_this;elseBeginset @i=@j;Delete from #result;End------------Endelsebegininsert into #result(id,Q) values(@id,@q);set @i=@i+1;end------------------------GoTo Begin_thisEnd-----------------------------
End_this:select id,Q from #result--close __cursor--deallocate __cursor
.....please help me, =A='
Export Sp_columns Result Into A Temp Table?
Mar 1, 2004Can anyone show me the syntax for exporting the result of sp_columns into a new table?
I've tried using "SELECT EXEC sp_columns...INTO 'column_info'" but I keep getting syntax errors that are unclear to me.
Any help would be greatly appreciated.
ab
Storing A Stored-proc's Result Into A Temp Table
Jul 20, 2005I'm trying to write a SQL that stores a result from a stored-procedureinto a temporary table.Is there any way of doing this?
View 3 Replies View RelatedAnalysis :: Temp Table Like Structure In MDX To Process Intermediate Result
Aug 5, 2015I have started working on SSAS since last week, I need to perform some calculations on the data fetched from the cube based on the parameters. SSRS is used to display the output and SSAS is used as a data source.
How i could perform the operations on the data fetched from the cube in SSAS? Does SSAS provides the storage structure like temp table in stored procedure where we can perform the various operations before sending final data back to the client side tool(SSRS)?
OR Is there any alternative way to perform the operations based on the input provided through the parameters
Transact SQL :: How To Store Result Of Exec Command Into Temp Table
Apr 7, 2013I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp
Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
Problem In Executing Stored Procedure With Temp Table Returns Result Sets
Mar 23, 2006Hi,
we are facing problem in executing a stored procedure from Java Session Bean,
coding is below.
pst = con.prepareStatement("EXEC testProcedure ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
pst.setString(1, "IN");
//
rs = pst.executeQuery();
rs.last();
System.out.println(" Row Number "+rs.getRow());
rs.beforeFirst();
while(rs.next())
{
System.out.println(" Procedure is "+rs.getString(1));
}
same sp working perfectly with SQL Server 2000
am getting a error message
com.microsoft.sqlserver.jdbc.SQLServer
Exception: A server cursor cannot be opened on the given statement or statements
. Use a default result set or client cursor.
If a SP doesnt have a temp table, then there is no issue, SP executes perfectly, but if a SP has a temp table, this error occurs.
SP :
create proc testProcedure
@countrCode varchar(3)
as
select countryname INTO #TMPCOU from country where countryCode = @countrCode
SELECT COUNTRYNAME FROM #TMPCOU
Its really very urgent. Please help me...!
Rgds,
Venkatesh.
T-SQL (SS2K8) :: Moving Values From Temp Table To Another Temp Table?
Apr 9, 2014Below 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?
Can You Caputure And Export Out Result Sets In Temp Tables???
Jan 8, 2001I need to export out the data in a result set from temp tables. It is in a rather large Dts Stream. Thanks for your help. Brett
View 1 Replies View RelatedStore The Stored Procedure Result In A Temp. Tabl
Mar 28, 2008All,
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
Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?
Dec 11, 2007Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
Sp_executesql - Using Table Variable
Jan 18, 2008In the following stored procedure, I am doing Right outer join with the table variable. I am getting error 'Must declare the scalar variable "@MonthSales1"'. Can you please let me know how to do this?
ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber1 nvarchar(30),@StoreId1 nvarchar(500),@Year1 int)AS DECLARE @SQL nvarchar(1000)
-- Searching ItemNumber in Inventory_SKU DECLARE @Count int DECLARE @ItemNumSKU varchar(50) SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId) AND ItemNum = @ItemNumber' EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@Count12=@Count OUTPUT,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1 IF (@Count = 0) BEGIN SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId) AND AltSKU = @ItemNumber' EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@ItemNumSKU1=@ItemNumSKU OUTPUT SET @ItemNumber1 = @ItemNumSKU END
-- Creating table variable to have values from 1 to 12 DECLARE @MonthSales1 Table(MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint) DECLARE @Cnt INT SET @Cnt = 1 WHILE(@Cnt <= 12) BEGIN INSERT INTO @MonthSales1 VALUES(@Cnt,0,0,0) SET @Cnt = @Cnt + 1 END
--Joining query result with the table variable to get required result DECLARE @Status1 Char(1) SET @Status1 = 'C'
SET @SQL = N'SELECT' SET @SQL = @SQL + N' MS.MonthNumber,' SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,' SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,' SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan' SET @SQL = @SQL + N' FROM' SET @SQL = @SQL + N' (SELECT ' SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#' SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost' SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice' SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan ' SET @SQL = @SQL + N' FROM ' SET @SQL = @SQL + N' Invoice_Totals ' SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number ' SET @SQL = @SQL + N' WHERE ' SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId) ' SET @SQL = @SQL + N' AND ItemNum = @ItemNumber' SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year' SET @SQL = @SQL + N' GROUP BY' SET @SQL = @SQL + N' DATEPART(mm, DateTime)' SET @SQL = @SQL + N' ) Temp ' SET @SQL = @SQL + N' RIGHT OUTER JOIN ' + @MonthSales1 + ' MS ON MS.MonthNumber = Temp.Month#' SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'
EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId nvarchar(500),@ItemNumber nvarchar(30),@Year int',@Status = @Status1,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@Year=@Year1
Thanks
Using Sp_ExecuteSQL And Passing Table Name
Jan 11, 2001I am creating a dynamic sql statement in a stored procedure.
The stored procedure has table name passed in.
I cannot pass in the table name as a parameter.
How do I pass the table name as a paramter is a dynamic sql statement
Sp_executesql - Using Table Variable
Jan 18, 2008In the following stored procedure, I am doing Right outer join with the table variable. I am getting error 'Must declare the scalar variable "@MonthSales1"'. Can you please let me know how to do this?
ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber1 nvarchar(30),@StoreId1 nvarchar(500),@Year1 int)
AS
DECLARE @SQL nvarchar(1000)
-- Searching ItemNumber in Inventory_SKU
DECLARE @Count int
DECLARE @ItemNumSKU varchar(50)
SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId) AND ItemNum = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@Count12=@Count OUTPUT,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1
IF (@Count = 0)
BEGIN
SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId) AND AltSKU = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@ItemNumSKU1=@ItemNumSKU OUTPUT
SET @ItemNumber1 = @ItemNumSKU
END
-- Creating table variable to have values from 1 to 12
DECLARE @MonthSales1 Table(MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)
DECLARE @Cnt INT
SET @Cnt = 1
WHILE(@Cnt <= 12)
BEGIN
INSERT INTO @MonthSales1 VALUES(@Cnt,0,0,0)
SET @Cnt = @Cnt + 1
END
--Joining query result with the table variable to get required result
DECLARE @Status1 Char(1)
SET @Status1 = 'C'
SET @SQL = N'SELECT'
SET @SQL = @SQL + N' MS.MonthNumber,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'
SET @SQL = @SQL + N' FROM'
SET @SQL = @SQL + N' (SELECT '
SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#'
SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost'
SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice'
SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan '
SET @SQL = @SQL + N' FROM '
SET @SQL = @SQL + N' Invoice_Totals '
SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '
SET @SQL = @SQL + N' WHERE '
SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId) '
SET @SQL = @SQL + N' AND ItemNum = @ItemNumber'
SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year'
SET @SQL = @SQL + N' GROUP BY'
SET @SQL = @SQL + N' DATEPART(mm, DateTime)'
SET @SQL = @SQL + N' ) Temp '
SET @SQL = @SQL + N' RIGHT OUTER JOIN ' + @MonthSales1 + ' MS ON MS.MonthNumber = Temp.Month#'
SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'
EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId nvarchar(500),@ItemNumber nvarchar(30),@Year int',@Status = @Status1,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@Year=@Year1
Thanks
Exec Sp_executesql Vs. Sp_executesql And Performance
Jul 23, 2005This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
View 3 Replies View RelatedTemp Table Vs Global Temp Table
Jun 24, 1999I 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
How To Pass Table Parameter With Sp_executesql
Sep 4, 2007Hello All,
i am using
execute sp_executesql @strQuery statement in my SP.
and My is @strQuery = "Insert into @tmp_tbl select ...". I have created @tmp_tbl using "
DECLARE @tmp_tbl TABLE(rownum ...... ".
its giving me error "
Must declare the table variable "@tmp_tbl". while executing : execute sp_executesql @strQuery statement.
How to pass that table variable with executesql ??
As i understand, i created table @tmp_tbl in my SP and trying to access it inside system SP sp_executesql .
so its not working.
Is there a way to pass table variable as parameter to sp_executesql ??
Thanks in advance. its very urgent for me..
Not Able To Create Hash Table Inside Stored Proc Through Execute Sp_executesql @strQuery
Aug 21, 2007Hello,
i need to create temporary table inside SP.
i having one string variable @strQuery which contain dynamic query inside SP.
i am executing that trhough execute sp_executesql @strQuery once query build.
now instead of select query , i want to creat hash table.
so i wrote :
set @strQuery = "Select * into #tmp_tbl from table_name..."
when i tried to execute it through
execute sp_executesql @strQuery , its giving error 'Invalid object name '#tmp_tbl'
If i removed Hash then it works fine. even for double Hash also its work fine.
but i want hash table only as i want that table local to that user.
Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery through execute sp_executesql @strQuery only as query is dynamic .
please guide me how to do this?
its very urgent for me.
thanks in advance.
SQL Server 2012 :: Stored Procedures Compiles Even When There Is No CREATE TABLE For A Temp Table
Feb 11, 2015i 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
Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?
Jul 19, 2012I 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.
INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table
Jul 20, 2005Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO
View 2 Replies View RelatedSQL Tools :: Adding Column To A Table Causes Copying Data Into Temp Table
Sep 23, 2015If 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.
Transact SQL :: Update Table With Its Value And Data From Row In Temp Table For Matching Record?
Oct 25, 2015I have a temp table like this
CREATE TABLE #Temp
(
ID int,
Source varchar(50),
Date datetime,
CID varchar(50),
Segments int,
Air_Date datetime,
[code]....
Getting Error
Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.
Column Name Or Number Of Supplied Values Does Not Match Table Definition When Trying To Populate Temp Table
Jun 6, 2005Hello,
I am receiving the following error:
Column name or number of supplied values does not match table definition
I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:
SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp
INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'
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.
Thanks,
Greg.
How To Search Multiple Table Which Table Name Is Store In Another Table And Join The Result Together?
Dec 1, 2006I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
Transact SQL :: Table Structure - Inserting Data From Other Temp Table
Aug 14, 2015Below 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.
Update Temp Table With Stored Procedure Joined With Table
Sep 8, 2006Hello
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!
Worf
Is A Temp Table Or A Table Variable Used In UDF's Returning A Table?
Sep 17, 2007In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
Copying Temp Table Data To Permanent Table
Nov 23, 2007Hello guys..
Can u plz help me by giving me an idea how i can copy the temp table data to permanent table
Thanks,
sohails
Difference In Performance Between Temp-table And Local-table?
Jan 23, 2008Hi!
What is the difference in performance if I use a Temp-table or a local-table variable in a storedprocedure?
Why?
//Daniel
Transact SQL :: Insert Data From Temp Table To Other Table
Oct 5, 2015I 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
1000
1-Aug
1000
1-Feb
1000
1-Jul
1000
[code]....
Rename Table After Loading Data Into Temp Table
Dec 19, 2007WE have a job that loads data from an Oralce DB into our SQL Server 2000 DB twice a day. The schedule has just changed so that now there is a possibility of having my west coast users impacted when it runs at 5 PM PST and my east coast users impacted when it runs at 7 AM EST. As a workaround, I have developed a DTS package that loads the data into temp tables instead of the real tables. IE. Oracle -> XTable_temp instead of Oracle -> XTable. The load sometimes takes about an hour to an hour and a half to load, so this solution works great, but I want to then lock the table, delete it and rename the temp table to table X. The pseudo code would be:
Begin Transaction
Lock Table XTable
Drop XTable
Alter Table XTable_temp rename to XTable
Release Lock XTable
End Transaction
Create XTable_temp
I see two issues with this solution. 1) I think if I can lock XTable that the lock would be released when the table is dropped and the XTable_temp was being renamed. 2) I can't find a command to rename a table.
Any ideas on a process that might help?
TIA,
A
Trigger- Dump 'inserted' Table To Temp Table
Jul 11, 2006I want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare).
Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?