I don't think the OP has explained very precisely what the problem is. Why do they only want ProductId 101? Why does this take presidency over ProductId 102? Why does...

Is it really necessary to use a merge statement?  Why not something like this?

DROP TABLE IF EXISTS Test_Products;
DROP TABLE IF EXISTS Test_UpdatedProducts;
GO
CREATE TABLE Test_Products(ProductIDint, ProductName varchar(10), Rate...

So this...

if @x=1
begin
return 'xyz';
end
else
begin
return 'abc';
end

...is preferable to?

            
December 13, 2019 at 10:27 pm
            
#3707801

            
            
                                                
        

These are temporal tables?  When you look at the table name in SSMS it says "TableName (System-Versioned)"?

Is id the primary key of jobs?

Is siteid the primary key of SiteAddresses?

To get the columns from both tables onto a single row you could use CROSS JOIN.  It works...

Is this the documentation you're looking for?

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-2017

It's possible to pass a user defined table type to a stored procedure as a readonly parameter.  This code defines a table type called test_type1.  The procedure dbo.test_proc1 declares a...

The entire plural column is 'no' including temporal tables.  If tables are collections of rows then they should be plural, no?  If the table name is 'orders' then one row...

Just...

Just a few...

An alternative to nested sets is nested json arrays.  Here it is using Jeff's (small) Employee table (renamed to dbo.Test_Employee)

drop table if exists dbo.Test_Employee;
go
CREATE TABLE dbo.Test_Employee
 ...

To see if DesNorton's solution worked (it does) and to see if the interval could be created in Sql 2012 I tried with this sql although I don't have any...

By the way, I did have a script published here on SSC that includes an example of nested json arrays.  Please see:

https://www.sqlservercentral.com/scripts/function-and-queries-to-convert-hierarchical-adjacency-to-nested-json-arrays

Json_value() is used to access a single value of up to nvarchar(4000).  Openjson() is used to access arrays and can be nvarchar(max).  In the example json (with array []'s added)...

What you posted is not valid json.  The elements of SecondaryData should be in array brackets.

{
  "odata.id": "0001",
  "MediaId": "0001",
  "SecondaryData": [
  ...

• This reply was modified 6 years, 4 months ago by Steve Collins.