Recovery :: UPDATE Table And OUTPUT Clause To Capture Some Of Columns
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.
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.
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;
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.
SQL Server 2008R2: Enabling Change Data Capture on a replicated database or its tables will have any performance impact on existing transactional replication.Is it possible to use both of them con temporarily.
I have an SP which concatenates 2 columns in a where clause - ie WHERE [Column1] + [Column2] = @var. This, as far as I'm aware, is not going to access any seek on an index on a table.
My task is to create an index on this table and get the SP to access said index.
Aside from combining the two columns into one column on the table, how I can get an SP to access the newly created index when queried by the SP?
One thought of mine is to firstly index Column1 and allow the SP to access this index by inserting the rows into a temp table. Then a search on the temp table to retrieve the records on a search on Column2.
I have a temporary table #Temp with, among others, a column CountryCode and a column Lastname.I would like to change the ü that appears in some names to u (u umlaut to a plain u), but only for those that have the nationality 'Ned'
My code so far:
Update #Temp set LastName = replace(Lastname, 'ü', 'ue') WHERE CountryCode = 'Ned'
This code deletes all entries in the column Lastname
I think it must be easy, but I keep staring at the code.
I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.
I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.
What C# code would capture the Scope_Identity value (CoDeptRowID) output by the code below? Do I even need to capture it or is it already available as a C# variable CoDeptRowID ? I can't seem to get my hands on it! SqlDataSource1.Insert();<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [CompanyDepartment] ([User_Name], [FirstName], [LastName]) VALUES (@User_Name, @FirstName, @LastName); SELECT @CoDeptRowID = SCOPE_IDENTITY()" <insertparameters> <asp:sessionparameter Name="User_Name" Type="String" SessionField ="LoginName"/> <asp:controlparameter Name="FirstName" Type="String" ControlID="TextBox1" PropertyName ="text"/> <asp:controlparameter Name="LastName" Type="String" ControlID ="TextBox2" PropertyName ="text"/> <asp:Parameter Direction =Output Name ="CoDeptRowID" Type ="Int32" DefaultValue = "0" /> </insertparameters> </asp:SqlDataSource>
IF Object_id('GoldenSecurity') IS NOT NULL DROP TABLE dbo.GoldenSecurity; IF Object_id('GoldenSecurityRowVersion') IS NOT NULL DROP TABLE dbo.GoldenSecurityRowVersion;
I have a simple question that is more about performance on my code for updating a column with the sum of two columns:
Example
Table = dbo.adding
c1 c2 c3Sum 1 2 3 5 4 6
Now if I just simple write
Update dbo.adding set c3SUM = c1 + c2
This works, but there is a null in c1 or c2 then I know I can write this:
if exists (select * from dbo.adding where c1 is NULL)
Update dbo.adding
set c1 = '0', c3SUM = '0' + c2 where c1 is NULL
if exists (select * from dbo.adding where c2 is NULL)
Update dbo.adding
set c2 = '0', equals = c1 + '0' where c2 is NULL
Update dbo.adding
set c3SUM = c1 + c2
This works as well, but I am not sure how perficient it is being that from my understanding the code will evaluate the table three times and update it based on the criteria. If the table consists of 100,000 rows then this might not be a good solution
Is there a more proficient way to do this... I personally try to as little coding as possible to help increase performance. I know I can make this very complex and declare variables and put everything in a loop, but I personally think that would be less profient..
I have 4 rows below in file tblTEST, and I want to be able to transfer the CODE from the MAIN location to the INT location (replacing all existing "A" codes), preceeded by an "I".
ID LOC CODE -- ----- ------ 11 MAIN B 11 INT A 22 MAIN C 22 INT A
I want the result to be:
ID LOC CODE -- ----- ------ 11 MAIN B 11 INT IB 22 MAIN C 22 INT IC
I am stumped as to how to do this - any help or advice would be appreciated.
The only thing I've come up with is:
UPDATE S SET s.code = B.code FROM tbltest B LEFT OUTER JOIN tbltest S ON B.id = S.id WHERE (S.loc = 'INT')
I'm entering a Selection record for a partiuclar lotID, Once entered, I need to obtain its SelectionID then use it to update a another field within that record. Here's what I've been doing... --insert values into a testchangeorders table INSERT INTO testchangeorders VALUES (2,3,3,3,1,'red',0,5) --Find the SelectionsID of the last record created for that partiuclar LotID SELECT MAX (SelectionsID) FROM testchangeorders WHERE LotID = 2 --Once located, I was trying to update a field called uniqueID with a contantination of '3-' & the record's SelectionsID UPDATE testchangeorders SET UniqueID = ('3-' & SelectionID WHERE SelectionsID = SELECT MAX (SelectionsID) AND LotID = 2)
A column in my table is being updated and I would like to find out what process, host, user, etc. is performing the update.
I have added a trigger to the table so that when an update occurs I am able capture the session details into a table, however the session details that are being returned are those of the actual trigger rather than those of the original query.
The statement that I am using to capture the session details is...
select * from sys.dm_exec_connections cross apply sys.dm_exec_sql_text(most_recent_sql_handle) where session_id = @@spid
There is a parent_session_id returned in the above which I hoped might give me some pointers but this is always NULL.
I have been working my way through the system dynamic views in the hope that one of them will provide the information I am looking for but so far I've had no luck.
Because the system I am troubleshooting is web based the actual server and user details will be those configured as service accounts for the application but if I am able to show the developers the original query that was used for the update I'm sure they will be able to provide valuable information as to which component it is coming from. I imagine SQL will also be able to tell me which host is sending the query. With these two bits of info I'm sure I'll be able to track it down.
Our suspicion is that an old component is running somewhere and I'd like to be able to track it down. Perhaps I've spent too much time looking at this but I'm coming to the conclusion that it may not be possible from the SQL back end, which I am very surprised at.
I have tried capturing every session that was running at the time of the update but there's simply too much info to deal with. I even searched through these results but there's so many different possible combinations that an update statement might take form that it quickly became a worthless exercise.
Perhaps one of you can resolve this for me. Any ideas?
I have a table with 8 columns, I need to update data in multiple columns on this table, this table contains 1 million records, having single update was taking time so I broke the single update into multiple update statements and running multiple update statements in parallel, Each update statement updates different column.
This approach is working fine but I am getting the deadlock error.
Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )
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.
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.
Hi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!
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
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT) INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT) INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H SET CHANNEL1= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=1 -- updates only channel1 UPDATE H SET CHANNEL2= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=2 -- updates only channel2 SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
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>
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
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>
I have located a bug in the functions cdc.fn_cdc_get_net_changes_<capture_instance> generated when you enable cdc on a table. This bug can be triggered if 2 rows are created in the _CT table having the same values for the __$start_lsn, __$seqval and the table's key column(s). From research on the internet I have found such rows can be created by a "deferred update": a single update statement in which a column that is part of a unique constraint is updated.
In order to report the bug with Microsoft I need to create a complete series of steps-to-reproduce. But even though the situation happens several times a day in our production environment, I have not yet been able to reproduce it in my test environment.I need a single update statement (plus maybe some steps in advance) that make that the log reader inserts 2 rows into the _CT table, one with __$operation = 1 (delete) and another with __$operation = 2 (insert) as opposed to the single row with __$operation = 4 that it inserts for a normal update. Below is the script I have so far to create a fresh database, enable cdc, create a test table, insert some data and update this data.
I would have liked the last update statement to be handled as a "deferred update". However in all of my tests the log reader just simply inserts a single row into the cdc.dbo_NETTEST_CT table.how to reproduce the situation where I get the 2 rows with __$operation 1 and 2 from a single update statement instead of the single row with __$operation = 4.
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
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?
In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.
