Table2 - Dataset Name : Temp2, Observations - 10K, Number of Variables - 1 Table1 - Dataset Name : Temp, Observations - 1 Million, Number of Variables - 1 To answer this question, let's create two larger datasets (tables) and compare the 4 methods as explained above. The MERGE Statement joins the datasets dataset1 and dataset2 by the variable name. Where not exists (select name from dataset2 b This process is repeated for each rows of variable name.
NOT EXISTS subquery writes the observation to the merged dataset only when there is no matching rows of a.name in dataset2. Method III - Not Exists Correlated SubQuery At the next step, WHERE statement with 'b,name is null' tells SAS to keep only records from table A. At the second step, these columns are matched and then the b.name row will be set NULL or MISSING if a name exists in table A but not in table B. In the first step, it reads common column from the both the tables - a.name and b.name. In this method, we are performing left join and telling SAS to include only rows from table 1 that do not exist in table 2. Quit The output is shown in the image below. Where name not in (select name from dataset2) Testing error logic by trying to delete rows within the last monthĮXEC dbo.The simplest method is to write a subquery and use NOT IN operator, It tells system not to include records from dataset 2. (I wrote this answer on and on that date, the following throws an error. My example stored procedure throws an error if you try to delete any rows within the past month. I also added some error checking logic in the stored procedure to show how you might prevent rogue deletes that are too aggressive due to the date being passed in. Check rows after calling the stored procedure Check rows before calling the stored procedureĮXEC dbo.DeleteTableRowsLessThanSpecificDate = 'TestDelete' That should leave only one row in our example () Now, we're ready to test the stored procedure by deleting all rows Check this blog post about why you should use sp_executesql instead of EXEC()ĮXECUTE sp_executesql = let's test the stored procedure by passing in some different DeleteDates and checking the results.
SET = 'DELETE FROM ' + + '.' + + ' WHERE DateOnTable < ''' + convert(VARCHAR(20), + '''' Build the command by concatenating the input TableName and input DeleteDate Declare a variable to hold the dynamic SQL commandĭECLARE NVARCHAR(4000) = sysname, sysname, date' RAISERROR ('Delete Date Too Recent - Delete cancelled',16,1) Example error checking to prevent deleting rows with a date within the last month WHERE id = object_id(N'.')ĪND OBJECTPROPERTY(id, N'IsProcedure') = 1ĭROP PROCEDURE. Now, let's create the 'delete' stored procedure. Insert into TestDelete(ID, Name, DateOnTable) values (1,'NameOnTable','') IF OBJECT_ID('dbo.TestDelete', 'U') IS NOT NULLĬreate table TestDelete (ID int, Name varchar(100), DateOnTable Date) Create a test demo table and insert a couple of rows Create a stored procedure that builds a dynamic SQL statement based on your input parameters and then executes the commandįirst, let's create a test table with some data to play with.
0 kommentar(er)
