July 22, 2010 at 2:53 am
INSERT INTO [Tasnim].[dbo].[Master_Application_List]
([AppName]
,[AppVer]
,[AIT_ID]
,[Cert_Status]
,[Cert_Date]
,[App_Status]
,[Remediation])
VALUES
(<AppName, varchar,>
,<AppVer, varchar,>
,<AIT_ID, varchar,>
,<Cert_Status, varchar,>
,<Cert_Date, varchar,>
,<App_Status, varchar,>
,<Remediation, varchar,>)
INSERT INTO [Tasnim].[dbo].[Temp_Event_Import]
([Discovered_App_Name]
,[Version_Number]
,[AIT_Number]
,[Event_ID]
,[Cert_Status]
,[Cert_Date]
,[App_Status]
,[Remediation]
,[App_ID])
VALUES
(<Discovered_App_Name, varchar,>
,<Version_Number, varchar,>
,<AIT_Number, varchar,>
,<Event_ID, varchar,>
,<Cert_Status, varchar,>
,<Cert_Date, varchar,>
,<App_Status, varchar,>
,<Remediation, varchar,>
,<App_ID, int,>)
July 22, 2010 at 3:00 am
Try using the Wizard I mentioned to generate the data (it's the test values I'd like, not just the template INSERT statements). Don't worry too much if you can't.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 5:54 am
Tasnim,
Do you get any rows returned from the following query?
SELECT AppName, AppVer, AIT_ID, COUNT(*)
FROM Master_Application_List
GROUP BY
AppName, AppVer, AIT_ID
HAVING COUNT(*) > 1;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 7:25 pm
Hey yeah I get a number of rows from the query.
July 22, 2010 at 7:30 pm
Hey,
Also can you tell me if there is a way to check how many rows and which one have been either updated or newly entered to the MAL, based on my above codes...
Thanks
July 22, 2010 at 10:28 pm
tasnim.siddiqi (7/22/2010)
Hey yeah I get a number of rows from the query.
Can you work out why that line in your procedure gives an error now? 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 11:21 pm
The sub-query is the bit between the braces, the sub-query in itself is fine and valid, its because we are using an operator that expects comparison against a constant rather than a set. Our sub-query is returning 2 rows and because we are using '=' equals expects a single value (1 row, 1 column).
Hey
But now is there a way to check how many entries have been either updated or modified or added?
July 22, 2010 at 11:42 pm
tasnim.siddiqi (7/22/2010)
The sub-query is the bit between the braces, the sub-query in itself is fine and valid, its because we are using an operator that expects comparison against a constant rather than a set. Our sub-query is returning 2 rows and because we are using '=' equals expects a single value (1 row, 1 column).
Excellent. Yes, that is the reason.
But now is there a way to check how many entries have been either updated or modified or added?
Are you familiar with the @@ROWCOUNT function and the OUTPUT clause?
Using the OUTPUT clause to save inserted or updated record keys to a table variable is a robust method. You might also be able to use @@ROWCOUNT, but it can return incorrect values in some cases.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 25, 2010 at 8:23 pm
Not familiar
July 25, 2010 at 8:46 pm
Hey,
If I want to use the OUTPUT clause, does this mean I will have to create a separate table, where all the updated/inserted/deleted rows will be entered? If so then, whats the point of using OUTPUT clause anyways, because I can easily enter that row in the table whenever I am updating or inserting.
Hmm, though it will be efficient.
July 26, 2010 at 4:19 am
tasnim.siddiqi (7/25/2010)
...If so then, whats the point of using OUTPUT clause anyways, because I can easily enter that row in the table whenever I am updating or inserting.
...
There is a point:
1. Inserting: When you inserting multiple raws into a table with identity PK column and you want to capture all generated values (for example, you want to insert its children into separate table), then OUTPUT clause helps.
2. Updating: Let say you want to update multiple raws and capture the differnece between old and new values.
3. Deleting: You can delete raws from table, and capture them for futher reference.
July 26, 2010 at 4:33 am
Hi,
I am using the Output clause, the thing is I can't put a Output clause here. I just want to see which rows have been updated. Tried before the Where clause but gives error. The following code is the way I am updating, based on the conditions in Where clause.
Update Master_Application_List
SET Cert_Status = Temp_Event_Import.Cert_Status
FROM Master_Application_List, Temp_Event_Import
WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number AND (Master_Application_List.Cert_Status IS NULL OR (Temp_Event_Import.Cert_Status = 'Y' AND Master_Application_List.Cert_Status = 'N')))
Can let me know how I can get around this problem?
Thanks!
July 26, 2010 at 5:05 am
1. Noodles are greate for asian cuisin but not for T-SQL :-D, therefore first step will be transforming your statement into readable form (please note how using table aliases and nice formatting makes code much more readable and therefore more maintainable, also you should start using JOIN clause for joining tables):
UPDATE MAL
SET Cert_Status = TEI.Cert_Status
FROM Master_Application_List AS MAL
JOIN Temp_Event_Import TEI
ON MAL.AppName = TEI.Discovered_App_Name
AND MAL.AppVer = TEI.Version_Number
AND MAL.AIT_ID = TEI.AIT_Number
WHERE MAL.Cert_Status IS NULL
OR (TEI.Cert_Status = 'Y' AND MAL.Cert_Status = 'N')
2. Now, do you want to see which rows have been updated? How you want to see them?
You can output new or old version of them into separate table using OUTPUT:
-- prepare table to store updated rows (you can only cteate columns you really need)
SELECT *
INTO #UpdateResults
FROM Master_Application_List
WHERE 1 = 0
-- Update with OUTPUT will insert all updated rows into the #UpdateResults table
-- Change it to use DELETED.* to get the old version of records...
UPDATE MAL
SET Cert_Status = TEI.Cert_Status
OUTPUT INSERTED.*
INTO #UpdateResults
FROM Master_Application_List AS MAL
JOIN Temp_Event_Import TEI
ON MAL.AppName = TEI.Discovered_App_Name
AND MAL.AppVer = TEI.Version_Number
AND MAL.AIT_ID = TEI.AIT_Number
WHERE MAL.Cert_Status IS NULL
OR (TEI.Cert_Status = 'Y' AND MAL.Cert_Status = 'N')
-- You can see that this table now holds all updated rows
SELECT * FROM #UpdateResults
July 26, 2010 at 5:13 am
Hey,
Thanks for the code. But I knew Join gives you the query...The reason I used my codes is because I want to set something in one table to a value in another table based on some logic conditions.
Could you explain me how Join is working in the code u provided?
Thanks 🙂
July 26, 2010 at 5:19 am
Hi,
I have also run your code, with the Output Clause. I get the following error
Msg 4104, Level 16, State 1, Procedure stored_proc2, Line 50
The multi-part identifier "Temp_Event_Import.Cert_Status" could not be bound.
This is the reason I used my code, as I was getting similar errors previously.
Viewing 15 posts - 46 through 60 (of 66 total)
You must be logged in to reply to this topic. Login to reply