Inserting into Parents and Child tables

Transact-SQL
1

Hi Guys,
Hope you are looking forward for the weekend. I need your help on this queries please.

IF OBJECT_ID('tempdb..#Land') IS NOT NULL
DROP TABLE #Land

CREATE TABLE #land
(
LandId int not null identity(1,1) primary key,
[ReferenceNumber] NVARCHAR(255),
[Description] NVARCHAR(255),
[LandTitle] NVARCHAR(255),
[AddressID] NVARCHAR(255)

)

INSERT INTO #land ([ReferenceNumber], [Description], [LandTitle],[AddressID])
SELECT '2474' ,'Axis One, Hurricane Way, Langley, Slough','419,541,194,174','null'
UNION ALL
SELECT '2460' ,'Land on the west side of Sutton Lane', '41,874,181,417,541,700,1000,000,2000,3000,4000,5000,6000,7000,8000','null'
UNION ALL
SELECT '3540' ,'land at M4, Slough','419,441,964,141,419','null'
UNION ALL
SELECT '2452' ,'Axis Two, Hurricane Way, Langley, Slough','419,441,964,141,419','null'
UNION ALL
SELECT '2465' ,'Common Road Estate, Langley','417','null'

Select * from #land

IF OBJECT_ID('tempdb..#land_Title') IS NOT NULL
DROP TABLE #land_Title

CREATE TABLE #land_Title
(
LandId int primary key,
TitleID NVARCHAR(255),
CONSTRAINT fk_inv_Landid
FOREIGN KEY (Landid)
REFERENCES #land (Landid)

)

  1. The table #Land is the parent and #Land_tile is the child.
  2. I want to transpose the [LandTitle] field into TitleID in #land_Title.

The table #Land is 1:M relationship in #land_title.

Thanking you in advance and happy weekend

J

2

I want to transpose the [LandTitle] field into TitleID in #land_Title.

You should not do that. Instead, create a view with land_title and land that includes the JOIN to do the lookup, so you won't have to code it on every query. But you do not want to repeat the title in multiple tables. That will be a nightmare when a title changes.

3

as long as you are using SQL 2016

Select a.LandID as LandID, v.[Value] as TitleID
from #land a
cross apply String_Split(a.LandTitle, ',') v

4

Thank you guys