CODEWITHSUNDEEP
sqlsql-server
Alan2
Alan2

Reputation: 24562

How can I populate a table from another with a look up for each row?

I have three tables:

CREATE TABLE [dbo].[Word] (
    [WordId]        INT            IDENTITY (1, 1) NOT NULL,
    [Word]          VARCHAR (20)   NOT NULL
)

CREATE TABLE [dbo].[Temp] (
    [HeaderWord]    VARCHAR (20) NOT NULL,
    [OtherWord]     VARCHAR (20) NULL
);


CREATE TABLE [dbo].[WordRelationship] (
    [HeaderWordId] INT NOT NULL,
    [OtherWordId]  INT NULL,
    CONSTRAINT [PK_WordRelationship] PRIMARY KEY CLUSTERED ([HeaderWordId] ASC, [OtherWordId] ASC)
);

The third table is empty. Is there a way with SQL and not with needing a cursor that I could populate the WordRelationship table with the Id values of the Header and Other word? Note that in some case there may not be an OtherWord for each row in Temp2. Also every HeaderWord and OtherWord has an entry in the Word table.

Here's some sample data for the Word Table

WordId  Word
------  ----
5472    abandon
5473    abandoned
5474    abandoning
5475    abandonment
5476    abandons    
5477    abstraction 
5478    abstractions
5479    abstractly
5480    abstracts

Here's some sample data for the Temp Table

Header  Other
Word    Word
------- ----------
abandon abandoned
abandon abandoning
abandon abandonment
abandon abandons

Upvotes: 1

Views: 63

Answers (2)

gofr1
gofr1

Reputation: 15977

INSERT INTO WordRelationship
SELECT DISTINCT a.WordId, b.WordId
FROM Temp t
INNER JOIN Word a
    ON t.HeaderWord = a.Word
INNER JOIN Word b
    ON t.OtherWord = b.Word

You need DISTINCT cause there may be duplicates in Temp table. Also I recommend INNER JOIN, it shall eliminate null's if there are no words from Temp table in Word table.

Upvotes: 1

Turo
Turo

Reputation: 4914

Try

INSERT INTO [dbo].[WordRelationship]
 SElECT a.WordId, b.WordId from [dbo].[Temp] t
 JOIN [dbo].{Word] a on t.HeaderWord = a.Word  
 LEFT JOIN [dbo].{Word] b on t.OtherWord = b.Word

EDIT

removed LEFT in Join over Headerword since it is a not null field in WordRelationship.

Upvotes: 2

Related Questions