How can I populate the parent and child tables from another table?

Question

I have a table containing a list of words and related words. Typical data in the table is as follows. Note that some of the WordForms columns end, for example. and some just end with the last word wordform

Id Word WordForms 1 abandon abandoned, abandoning, abandonment, abandons 2 abstract abstraction, abstractions, abstractly, abstracts, eg 

Here is the layout of the source data table:

 CREATE TABLE [dbo].[TempWords] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Word] NVARCHAR (MAX) NOT NULL, [WordForms] NVARCHAR (MAX) NULL, ) 

I would like to use this data to populate two tables. I know about using SQL INSERT INTO, but I think that I will only help me with one table. What I would like to do is take the first Word, put it in the word table, and then separate the word forms, which are now separated by a comma, and put them in the WordForms table.

 CREATE TABLE [dbo].[Words] ( [WordId] INT IDENTITY (1, 1) NOT NULL, [Word] NVARCHAR (MAX) NOT NULL ) CREATE TABLE [dbo].[WordForms] ( [Id] INT IDENTITY (1, 1) NOT NULL, [WordId] INT NOT NULL, [Text] NVARCHAR (MAX) NULL, ) 

Can someone give me some tips on how I can do this?

Answer 1

First of all, you can create a UDF function to separate CSV values.

 CREATE FUNCTION dbo.fn_Split ( @InputString VARCHAR(8000), @Delimiter VARCHAR(50) ) RETURNS @Items TABLE ( Item VARCHAR(8000) ) AS BEGIN IF @Delimiter = ' ' BEGIN SET @Delimiter = ',' SET @InputString = REPLACE(@InputString, ' ', @Delimiter) END IF (@Delimiter IS NULL OR @Delimiter = '') SET @Delimiter = ',' --INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic --INSERT INTO @Items VALUES (@InputString) -- Diagnostic DECLARE @Item VARCHAR(8000) DECLARE @ItemList VARCHAR(8000) DECLARE @DelimIndex INT SET @ItemList = @InputString SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0) WHILE (@DelimIndex != 0) BEGIN SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex) INSERT INTO @Items VALUES (@Item) -- Set @ItemList = @ItemList minus one less item SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList) -@DelimIndex ) SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0) END -- End WHILE IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString BEGIN SET @Item = @ItemList INSERT INTO @Items VALUES (@Item) END -- No delimiters were encountered in @InputString, so just return @InputString ELSE INSERT INTO @Items VALUES (@InputString) RETURN END -- End Function GO 

You can then use the INSERT statements below to populate the tables.

 INSERT INTO [Words] SELECT Word FROM TempWords INSERT INTO WordForms SELECT W.WordId, LTRIM(RTRIM(FNT.Item)) AS Item FROM TempWords AS TW INNER JOIN [Words] AS W ON TW.[Word]=W.[Word] CROSS APPLY fn_Split(REPLACE(TW.WordForms,', eg',''),',') AS FNT SELECT * FROM [Words] SELECT * FROM WordForms 

Answer 2

You can insert words into the first table than to parse text forms and insert them into a child table with a link to the parent table.

A link to the parent table can be obtained by joining in the word column (I think this is unique) or by doing some MERGE + OUTPUT things to get SOURCE.ID (from @words_csv) and INSERTED.ID in one step. How do you like.

The analysis can be implemented in many ways, check this example (in fact, I would not recommend dealing with sql at all).

 DECLARE @words_csv TABLE (Id INT IDENTITY(1, 1), Word VARCHAR(100), WordForms VARCHAR(1000)) INSERT INTO @words_csv(word, wordforms) VALUES ('abandon', 'abandoned, abandoning, abandonment, abandons, eg'), ('abstract', 'abstraction, abstractions, abstractly, abstracts') --INSERT INTO [dbo].[Words](word) --SELECT w.word --FROM @words_csv w ;WITH word_forms_extracted AS ( SELECT w.id, w.word, ltrim(rtrim(cast(case when CHARINDEX(',', w.WordForms) > 0 then substring(w.wordforms, 1, CHARINDEX(',', w.WordForms)-1) end AS VARCHAR(1000)))) wordform, stuff(w.wordforms, 1, CHARINDEX(',', w.WordForms), '') wordforms FROM @words_csv w UNION ALL SELECT w.id, w.word, ltrim(rtrim(cast(case when CHARINDEX(',', wfe.WordForms) > 0 then substring(wfe.wordforms, 1, CHARINDEX(',', wfe.WordForms)-1) else wfe.wordforms end AS VARCHAR(1000)))) wordform, case when CHARINDEX(',', wfe.WordForms) > 0 then stuff(wfe.wordforms, 1, CHARINDEX(',', wfe.WordForms), '') ELSE '' end wordforms FROM @words_csv w INNER JOIN word_forms_extracted wfe ON wfe.id = w.id WHERE wfe.wordforms != '' ) SELECT wf.id, wf.word, wf.wordform FROM word_forms_extracted wf --INNER JOIN [dbo].[Words] w --ON w.word = wf.word WHERE wf.wordform NOT IN ('', 'eg') ORDER BY wf.id, wf.wordform OPTION(MAXRECURSION 1000) 

The SELECT finale can be easily changed to INSERT INTO dbo.WordForms (...) SELECT ... link to dbo.Words obtained here, as you can see by joining the word column.

Answer 3

Using XML:

 INSERT INTO [dbo].[Words] SELECT DISTINCT [Word] FROM [dbo].[TempWords] DECLARE @xml xml SELECT @xml = ( SELECT CAST('<row><word>'+WORD+'</word><w>' + REPLACE(WordForms,', ','</w><w>') +'</w></row>' as xml) FROM [dbo].[TempWords] FOR XML PATH('') ) INSERT INTO [dbo].[WordForms] SELECT w.[WordId], tvvalue('.','nvarchar(max)') as [Text] FROM @xml.nodes('/row/w') as t(v) LEFT JOIN [dbo].[Words] w ON tvvalue('../word[1]','nvarchar(max)') = w.[Word] 

In [dbo].[Words]

 WordId Word 1 abandon 2 abstract 

In [dbo].[WordForms]

 Id WordId Text 1 1 abandoned 2 1 abandoning 3 1 abandonment 4 1 abandons 5 2 abstraction 6 2 abstractions 7 2 abstractly 8 2 abstracts