CODEWITHSUNDEEP
sql-server
user995689
user995689

Reputation: 825

SQL Server : copy table data with FK Constraint

I import data into a SQL Server database from Excel.

The data is inserted into a tmptable which has no primary key defined.

tmptable:

course, trainer, jockey, horse, won, pos, date

I want to copy the data from tmptable into another table mastertable which has a primary key column.

mastertable:

coursefk, trainerfk, jockeyfk, horsefk, won, pos, date.

The columns suffixed 'fk' refer to individual other tables.

My hope is that during the insert into mastertable, the script would take the value of course column from tmptable and refer to the table Courses for the appropriate value to be placed into mastertable. The primary and foreign key relationships between the tables have been created, but I cannot create a script that will achieve what I want.

Can anyone please help.

Upvotes: 0

Views: 233

Answers (1)

ughai
ughai

Reputation: 9890

You can join tmptable with other foreign key tables.

Something like this.

INSERT INTO mastertable(coursefk, trainerfk, jockeyfk, horsefk, won, pos, date)
SELECT mastercourse.coursefk, mastertrainer.trainerfk, masterjockey.jockeyfk, masterhorse.horsefk, won, pos, date
FROM tmptable
LEFT JOIN mastercourse ON mastercourse.course = tmptable.course 
LEFT JOIN mastertrainer ON mastertrainer.trainer = tmptable.trainer
LEFT JOIN masterjockey ON masterjockey.jockey = tmptable.jockey
LEFT JOIN masterhorse ON masterhorse.horse = tmptable.horse

Note: Since I do not know structure of other FK tables, Please replace and use appropriate table and column names.

Upvotes: 2

Related Questions