create new table having many to one relationship between other two tables-sql server management studio

Question

Hi, I have two tables, one table has rowguid column and other has auto incremented column but both tables dont have primary key or foreign key. I want to create third table that store the relationship between above two tables. can somebody give me the correct and easiest way to do that. i m new using sql server. please reply me

Answer 1

Firstly, you will have to make a call on how these tables relate to each other. There will have to be some business/logic rule to do this, and that would be the original association.

Then you can try something like

DECLARE @TableGuid TABLE(
        GuidID UNIQUEIDENTIFIER DEFAULT NEWID(),
        Val VARCHAR(10)
)

DECLARE @TableID TABLE(
        ID INT IDENTITY(1,1),
        Val VARCHAR(10)
)

INSERT @TableGuid (Val) SELECT 'A'
INSERT @TableID (Val) SELECT 'A'

SELECT * FROM @TableGuid
SELECT * FROM @TableID

DECLARE @TableManyMany TABLE(
        GuidID UNIQUEIDENTIFIER,
        ID INT,
        PRIMARY KEY(GuidID, ID)
)

INSERT INTO @TableManyMany
SELECT  GuidID,
        ID
FROM    @TableGuid g INNER JOIN
        @TableID i ON g.Val = i.Val

SELECT  *
FROM    @TableManyMany

Answer 2

Amby, never do anything to a database except through a script. It is a very poor practice to use the GUI interface and you need to break the habit right now. YOu need things in scripts so that you can put them in source control and use them to deploy to other servers.

Never and, I do mean never, have a table without a primary key. That's database design day one. If you don't have a primary key, do you at least have a unique index? If not, you can get duplicate values which is bad. Any table without a way to uniquely identify a row is broken. Fix that before you go any farther.

What you also need is some way to determine what the relationship is between the two tables. How do you know which record in tablea is to match to which record in tableb? Since you have two different datatypes, that is not the relationship so you need to have some other field (or combination of fields) which is the same in both tables to build the relationship on. If you don't have that, what is the basis of the relationship. Please show sample data in both tables and show which data you want in the bridge table. I believe you have not yet thought out the table design enough based on what you posted.