CODEWITHSUNDEEP
sql-server
Alan2
Alan2

Reputation: 24572

How can I insert data into a child table from a parent table columns?

I have a table that contains SampleSentences Sample1, Sample2, Sample3, Sample4 and Sample5.

CREATE TABLE [dbo].[WordForm] 
(
    [WordFormId]       VARCHAR (20)  NOT NULL,
    [WordFormIdentity] INT           IDENTITY (1, 1) NOT NULL,
    [Sample1]          VARCHAR (MAX) NULL,
    [Sample2]          VARCHAR (MAX) NULL,
    [Sample3]          VARCHAR (MAX) NULL,
    [Sample4]          VARCHAR (MAX) NULL,
    [Sample5]          VARCHAR (MAX) NULL,

    PRIMARY KEY CLUSTERED ([WordFormId] ASC))
);

I decided to add a new table for this data:

CREATE TABLE [dbo].[SampleSentence] 
(
    [SampleSentenceId] INT           IDENTITY (1, 1) NOT NULL,
    [WordFormId]       VARCHAR (20)  NOT NULL,
    [Text]             VARCHAR (MAX) NOT NULL,

    CONSTRAINT [PK_SampleSentence] 
        PRIMARY KEY CLUSTERED ([SampleSentenceId] ASC),
    CONSTRAINT [FK_SampleSentenceWordForm] 
        FOREIGN KEY ([WordFormId]) REFERENCES [dbo].[WordForm] ([WordFormId])
);

Can someone suggest how I can take the data out of Sample1, Sample2, Sample3, Sample4 and Sample5 and insert it into the text column of the rows in the new SampleSentence table?

Upvotes: 1

Views: 76

Answers (1)

matt.dolfin
matt.dolfin

Reputation: 672

You want this:

insert into SampleSentence (WordFormId, Text)
select WordFormId, Sample1 from WordForm where Sample1 is not null
union select WordFormId, Sample2 from WordForm where Sample2 is not null
union select WordFormId, Sample3 from WordForm where Sample3 is not null
union select WordFormId, Sample4 from WordForm where Sample4 is not null
union select WordFormId, Sample5 from WordForm where Sample5 is not null

Upvotes: 1

Related Questions