SQL Server query to group records and insert in other tables

I am using SQL Server database which has tables x, y and mapping table xy.

I have a table X with x_id, date, text and receiver id and mapping table xy with x_id and y_id. I need a script to generate XY and Y tables in such a way that for each unique text there should be a record in y table and based on number of receivers there should be mapping in XY table. date column in Y table should be populated with shortest date for that specific text.

Table: x

x_id             date               text           receiver
---------------------------------------------------------
| 1  |  2019-02-22 20:40:30.617  |    txt1   |        4
| 2  |  2019-02-22 20:40:06.103  |    txt1   |        5
| 3  |  2019-02-22 20:28:21.393  |    txt2   |        4

So, final data in XY & Y for the above records should end up with

Table: Y

y_id             date               text
---------------------------------------------
| 1  |  2019-02-22 20:40:06.103   |    txt1   | 
| 2  |  2019-02-22 20:28:21.393   |    txt2   |

Table: XY

y_id   x_id 
-----------
| 1  |  1  
| 1  |  2 
| 2  |  3  

Answer

first get the data fro Y table and insert, I have used Row Number for Id of Y table, you can create identity for that if you want. second select data for XY table with join with X and Y table text property.

please do not use any keyword for field name like text or date.

DECLARE @x TABLE(
x_id int,
date datetime,
text varchar(100),
receiver int
)

insert INTO @x
(
    x_id,
    [date],
    [text],
    receiver
)
VALUES
(1,'2019-02-22 20:40:30.617 ','txt1',4),
(2,'2019-02-22 20:40:06.103 ','txt1',5),
(3,'2019-02-22 20:28:21.393 ','txt2',4)


--for Y table
--insert into Y
SELECT ROW_NUMBER() OVER(ORDER BY [text] ASC) AS Id, Min(date) AS [date], [text] 
INTO #Y
FROM @x
GROUP BY [text]

--for XY table
SELECT Y.Id AS y_id, X.x_Id AS x_id FROM @x AS X
    INNER JOIN #Y Y ON X.[text] = Y.[text]

DROP TABLE #Y