Transact SQL :: Usage Of OUTPUT Clause Depending On Temporary Table
Jul 14, 2015
Suppose we have the following table in our database;
CREATE TABLE [dbo].[PERMISSION](
[ID] [int] IDENTITY(1,1) NOT NULL,
[USERID] [int] NOT NULL,
[STARTTIME] [smalldatetime] NOT NULL,
[ENDTIME] [smalldatetime] NOT NULL,
[REASON] [nvarchar](250) NULL,
[PERMISSIONTYPEID] [int] NOT NULL,
[code]....
This code works pretty well. But I don't want to do this with "select" since there is OUTPUT clause in T-SQL. So the CommandText property will be changed into this;
command.CommandText = @"insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @outID, @outCREATETIME
values(2, getdate(), getdate(), 'sdfg', 1, DEFAULT);";
well, not only this statement gives an error while executing; but also, no such usage defined in the
documentation of OUTPUT Clause. Actually the documentation tell us to use Temporary Tables for that. So I have to change CommandText into this;
command.CommandText = @"DECLARE @MyTableVar table(ID int, CREATETIME smalldatetime);
insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @MyTableVar
code]....
No temporary tables required; thus, no "type spesific" things required. This way is easier to create dynamic queries though. Only,the RETURNING INTO clause.So, I was wondering; why MS-SQL (T-SQL) forces users to "declare a temporary table (type specific)" and "execute select on the temporary table in order to assign values to output parameters" while using "the OUTPUT Clause". Comparing to the Oracle's sample I'm just using "the RETURNING INTO Clause in order to assign values to output parameters" and that's it; very easy and dynamic. To Summarize, Oracle's RETURNING INTO Clause is better than MS-SQL's OUTPUT Clause, but in fact they're doing the same job.
View 7 Replies
ADVERTISEMENT
Mar 28, 2008
Hi,
I have a report that calls a stored procedure that creates an extract of data for use by various subreports. Now I have this problem:
If I save the extract data in a global temporary table, then it is automatically deleted before the subreports can use it, this means I have to create a normal table with a unique name that need to be deleted - but where do you do this in the report - there is no point where you can say it is now safe to delete a table?
I do not want to resort to external mechanisms, languages, jobs etc. to do this. I want to delete the table once the report is really finished in the report.
My main report uses a list that contains all the subreports as I need to group all sorts of information by vendor. The main report calls the stored procedure. Please do not tell me that I have to duplicate the main extract for every subreport. That will really eat resources.
Thanks in advance.
View 4 Replies
View Related
Mar 28, 2008
Hi,
I have a report that calls a stored procedure that creates an extract of data for use by various subreports. Now I have this problem:
If I save the extract data in a global temporary table, then it is automatically deleted before the subreports can use it, this means I have to create a normal table with a unique name that need to be deleted - but where do you do this in the report - there is no point where you can say it is now safe to delete a table?
I do not want to resort to external mechanisms, languages, jobs etc. to do this. I want to delete the table once the report is really finished in the report.
My main report uses a list that contains all the subreports as I need to group all sorts of information by vendor. The main report calls the stored procedure. Please do not tell me that I have to duplicate the main extract for every subreport. That will really eat resources.
Thanks in advance.
View 6 Replies
View Related
May 28, 2015
I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.
View 4 Replies
View Related
Aug 27, 2015
How to add columns to temporary table in loop with sample code.
View 12 Replies
View Related
Sep 20, 2015
I'm executing a sp name [uspGeneral_Getinfo]. It return a global temporary table's name and I want to get data from that table.
[192.168.2.11] = Linked Server
Declare@LinkedServer varchar(40)='[192.168.2.11].DBPharm.dbo.',@OutPutTableName varchar(50)=' ',@SQL nvarchar(max)Set@LinkedServer=Rtrim(Ltrim(@LinkedServer))Set@SQL=' Declare @OutPutTableName varchar(50),@SQL nvarchar(max) EXEC '+@LinkedServer+'[uspGeneral_GetDomainDataNew] 9,null, @OutPutTableName OutPut '+' Set @SQL='''+'Select * from '''+'+ @OutPutTableName'+' Exec sp_executesql @SQL 'Select@SQL
Above code are executing from another server [192.168.2.10].But getting an error that .I can't able to search where the temp table created in linked serve tempdb or ...?
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 38
Invalid object name '##table1FD1B81Bx4EAFx4FFDx9F6Fx15B77B6445F'.
View 8 Replies
View Related
May 5, 2008
I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.
My simple QueryA
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colD IS NOT NULL
My simple QueryB
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colC IS NOT NULL
I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.
Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?
I know it can be done using dynamic SQL construction. But any other ways ?
Also can someone also give in the solution in PL/SQL.
Thanks a bunch.
Jaffery.
View 7 Replies
View Related
May 28, 2015
I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.
View 5 Replies
View Related
Jan 13, 2015
This is for Microsoft Server SQL 2012.
I'm trying to create a WHERE clause that will have different results depending on a parameter that is entered. For example, if you put in a number, it will only calculate the rows where the column ID matches that number. However, if you put in 0, which doesn't exist in that column ID, it will instead calculate all the data in the table.
So the below would be a very basic idea of what I'm trying to do, but I'm not sure how to do it with proper syntax.
WHERE IF ID=0, THEN do this
ELSE do this AND ID=#
View 2 Replies
View Related
Jun 25, 2013
I need to update the result depending upon the count of distinct entries.
Example
ID Employee Region State
1 ABC AMEA MI
2 DEF AMEA MI
3 XYZ APAC TX
I want the result as below
ID Employee Region State
1 ABC AMEA MI-1
2 DEF AMEA MI-2
3 XYZ APAC TX
since the count of Region is 2
I tried using DECLARE @intFlag INT and stuff but wasn't able to get the solution.
View 2 Replies
View Related
Jul 9, 2015
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
[code]....
View 6 Replies
View Related
Nov 4, 2015
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
View 13 Replies
View Related
Sep 21, 2015
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
View 3 Replies
View Related
Sep 9, 2015
I want to know the best way to use the cte in this context where in I have 2 identical queries except for 2 different filter conditions .So below is how the definition of the cte with 2 different queries looks like at the moment .I will be using this two datasets separately in processing data .
with cte1
AS
(
select from table where a>1
)
with cte2
(
select from table where b>1
)
Is my declaration and definition right in this context or should it be
with cte1
as
(
select from table where a>1
)
,
cte2
as
(
select from table where b>1
)
View 2 Replies
View Related
Apr 26, 2008
need help please on update only if .
i need to update only the field "val_holiday " (in table B from table A)
and olso to check on table B the "ID" + "new_date" only if exist
and update the field "val_holiday " (in table B)
and at the end of update change the field "field_check_update_if _ok" from 0 to 1
only the row that update in table B
select from table A
update table B
WHERE ..........................HOW ?
----------------------------------------------------------------- table A
ID fname new_date val_holiday field_check_update_if _ok
---------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 999 0
111 aaaa 16/03/2008 888 0
111 aaaa 18/03/2008 77 0
111 aaaa 19/03/2008 9 0
111 aaaa 20/03/2008 111 0
111 aaaa 21/03/2008 12 0
222 bbb 02/05/2008 15 0
222 bbb 03/05/2008 16 0
222 bbb 04/05/2008 9 0
222 bbb 05/05/2008 3 0
222 bbb 06/05/2008 90 0
222 bbb 07/05/2008 3 0
222 bbb 08/05/2008 3 0
222 bbb 09/05/2008 3 0
333 ccc 03/04/2008 4 0
333 ccc 04/04/2008 4 0
----------------------------------------------------------------- table B
ID fname new_date val_holiday
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1
222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3
333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
------------------------------------------------------------------------------
TNX for the help and for all
View 1 Replies
View Related
Apr 25, 2008
need help please on update only if .
i need to update only the field "val_holiday " (in table B from table A)
and olso to check on table B the "ID" + "new_date" only if exist
and update the field "val_holiday " (in table B)
and at the end of update change the field "field_check_update_if _ok" from 0 to 1
only the row that update in table B
Code Snippet
select from table A
update table B
WHERE ..........................HOW ?
----------------------------------------------------------------- table A
ID fname new_date val_holiday field_check_update_if _ok
---------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 999 0
111 aaaa 16/03/2008 888 0
111 aaaa 18/03/2008 77 0
111 aaaa 19/03/2008 9 0
111 aaaa 20/03/2008 111 0
111 aaaa 21/03/2008 12 0
222 bbb 02/05/2008 15 0
222 bbb 03/05/2008 16 0
222 bbb 04/05/2008 9 0
222 bbb 05/05/2008 3 0
222 bbb 06/05/2008 90 0
222 bbb 07/05/2008 3 0
222 bbb 08/05/2008 3 0
222 bbb 09/05/2008 3 0
333 ccc 03/04/2008 4 0
333 ccc 04/04/2008 4 0
----------------------------------------------------------------- table B
ID fname new_date val_holiday
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1
222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3
333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
------------------------------------------------------------------------------
TNX for the help and for all
View 5 Replies
View Related
May 27, 2008
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
View 1 Replies
View Related
May 27, 2008
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
View 1 Replies
View Related
Nov 7, 2007
Hi,
I'm copying records from table A to Table B. On "insert" being successful I want to delete the copied records from table A. I want to use one single transaction. Could I use OUTPUT to delete all records in table A which key matches the ones on "inserted" ?. I believe it could be possible but I have tried several ways without any luck.
INSERT INTO Table A (Field 1, Field 2)
OUTPUT (DELETE Table B WHERE Key = (SELECT Key FROM Inserted))
SELECT (Field1, Field 2) FROM TABLE B
Or something like that,
Thank you
View 1 Replies
View Related
Sep 15, 2015
I can use Profiler to see database usage activity. However, in addition to it, is there a good query I can use to see whether user databases are being used (last select, last update, last alter or last delete etc., with date/time stamp)?I am looking for both SQL2000 and SQL2005 as we need to decommission some of the older servers.
View 6 Replies
View Related
Feb 29, 2008
I would like to add more columns on output clause while insert....
Following are the table formats used here ...
Table1 (col1, col2)
Table2 (col1, col2, col3, col4)
MyTable (C1,C2,C3,C4,C5,C6)
Insert into Table1 (col1, col2)
Output Inserted.col1, inserted.col2,MyTable.C3, MyTable.C4 into Table2
select C1,C2 from MyTable
Now the Output statement retrives only the Inserted.col1, inserted.col2 values, But I want to add MyTable.C3, MyTable.C4 values from the table <MyTable>
Is there any way to achieve this....
Thanks...
View 1 Replies
View Related
Nov 14, 2006
In the past when inserting a record into a table with an identity column, to return the new identity value I used one of SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY.
Question: will this sql 2005 approach also provide the newly added identity value?
insert into TestTable -- ID column of testtable is an Identity field
output inserted.id, inserted.col1 into @insertedRecords
values('row 20')
select ID from @insertedRecords
TIA,
Barkingdog
.
View 3 Replies
View Related
Feb 29, 2008
I would like to add more columns on output clause while insert....
Following are the table formats used here ...
Table1 (col1, col2)
Table2 (col1, col2, col3, col4)
MyTable (C1,C2,C3,C4,C5,C6)
Insert into Table1 (col1, col2)
Output Inserted.col1, inserted.col2,MyTable.C3, MyTable.C4 into Table2
select C1,C2 from MyTable
Now the Output statement retrives only the Inserted.col1, inserted.col2 values, But I want to add MyTable.C3, MyTable.C4 values from the table <MyTable>
Is there any way to achieve this....
Thanks...
View 3 Replies
View Related
Jul 23, 2005
Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?
View 7 Replies
View Related
Dec 3, 2007
In a package we have statements pointing to 2(or more) different databases on the server. When moving between environments, is there an easy way to change statements like:
UPDATE t1
SET t1.name = t2.name,
t1.age = t2.age
FROM DB1..Person t1
INNER JOIN DB2..PersonToo t2
ON t1.PersonID = t2.PersonID
I can think only of building the statements replacing the database names with variables, but that's not an easy way. I do not know how to use package variables in this situation.
1 way of doing this may be by using a Lookup or Conditional Split and use the resulting dataflow in a SP or such to update, using parameters...
It all sounds very messy, and I still don't know how
Any ideas?
TIA,
View 7 Replies
View Related
Aug 29, 2007
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies
View Related
Mar 14, 2004
I have a table that includes the html-output of different parts of my pages. This table grows very big very fast, and rows older than 24 hours are useless.
My question is if it is possible to have temporary rows, whose are automatically deleted after these 24 hours? And then how to accomplish that?
View 2 Replies
View Related
Dec 18, 2006
I need to update a table using a stored procedure that "UPSERTS". In this case, @OfficeId is passed with ParameterDirection.OutputSo when I update I assign the value to OfficeId like this (the office id value is being correctly assigned): SqlParameter OfficeIdParam = new SqlParameter("@OfficeId", SqlDbType.Int, 4); if (_officeId > 0) { OfficeIdParam.Value = _officeId; } OfficeIdParam.Direction = ParameterDirection.Output; The following segment of the stored procedure is throwing an error that the OfficeId does not exist: Update --- @OfficeId int output if not exists (select 1 from ORG_Office where OfficeId=@OfficeId) begin RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist. return -1 end May this approach work, and if the operation turns out to be an Insert I may do this:select @error_code = @@ERROR, @OfficeId= scope_identity() Thank you,jspurlin
View 1 Replies
View Related
Sep 26, 2014
I need to update the account_status and show the audit in the ssrs report.
I wrote the following query in the ssrs report but when I click on refresh fields it is throwing error incorrect syntax near output
Declare @username nvarchar(50)
Declare @UserStatus Table
(id bigint,
Username nvarchar(50),
Oldaccount_status varchar(255),
Newaccount_status varchar(255))
[Code] .....
View 2 Replies
View Related
Sep 10, 2007
Hi Guys,
I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.
Let's say I have a Dictionary table, 2 columns:
Word | Definition
And I have a string - "The cat sat on the dog"
If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"
At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"
The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...
I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )
Any ideas?
Thanks in advance
-Craig
View 4 Replies
View Related
Jul 15, 2004
Hi there,
Can anyone help? I currently have this query that imports distinct data into a prices table with a couple of contraints. This table became to large so I have now split this down into yearly tables (dbo.price --> dbo.price2001, dbo.price2002 etc). I get the data each day in another table that may contain data for different years so I need to be able to look at this data and insert into the right yearly tables.
E.g. present query:
Insert Prices
select distinct M.ids, C.zdateT, 1.0,0 from mapid as M,datacorrect as C
where M.asset = 'money'
and C.zDate not in (select zdate from price where sid >=15)
So I need to run this query for each yearly table with a date listed in the datacorrect table (insert prices(yr) yr = 2002,2003,2004 etc)
Any ideas would be appreciated!!
Thanks
S
View 3 Replies
View Related
Apr 21, 2008
H, need help please!
I have two tables on a reports, each having it's own dataset.
I want to set the visibility of the table depending on the filter selection.
So if the user selects: show open then show only the table with the open dataset.
I don't now how to this using the expression in the visibility expression.
Please Assist!
Regards
View 2 Replies
View Related
Jul 5, 2007
Hi, Everyone.
i am wondering if there is some way to hide a table row depending on the page number.
I have tried to find a way to access the global page number to use it in an expression for the visibility property on the table row, but i havent found anything useful.
View 14 Replies
View Related