Insert row for each foreign key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert row for each foreign key

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-04-06 : 05:08:51

Hi all,

I have a table ProductType and another table ProductSubType. The ProductSubType table has a foreign key constraint with the ProductType table.
I would like to insert a record "Common" in the ProductSubType table for each existing ProductType. I could use temporary tables or cursors, but I'm guessing there must be a better way of doing this. Any suggestions?

The ProductType table

CREATE TABLE [dbo].[ProductType](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ProductType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The ProductSubType table

CREATE TABLE [dbo].[ProductSubType](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FK_ProductType_ID] [int] NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ProductSubType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ProductSubType]  WITH CHECK ADD  CONSTRAINT [FK_ProductSubType_ProductType] FOREIGN KEY([FK_ProductType_ID])
REFERENCES [dbo].[ProductType] ([ID])
GO

ALTER TABLE [dbo].[ProductSubType] CHECK CONSTRAINT [FK_ProductSubType_ProductType]
GO

I was hoping I could do something like this (pseudo code)

For Each ID in ProductType
  If not exists(ID, 'Common') in ProductSubType Then
    insert into ProductSubType
    (FK_ProductType_ID,Description)
    values
    (ID,'Common')
  End If
Next

Thanks in advance!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-06 : 10:48:57

[code]
INSERT INTO ProductSubType
([FK_ProductType_ID],[Description])
SELECT
p.[ID], 'Common'
FROM
[ProductType] p
LEFT JOIN
(SELECT [FK_ProductType_ID] from ProductSubType where description = 'Common') s
ON
p.id = s.[FK_ProductType_ID]
WHERE
s.[FK_ProductType_ID] IS NULL
[/code]
EDIT : oops

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-09 : 07:44:36

I dont understand what u mean by "insert a record "Common" in the ProductSubType table for each existing ProductType".
Existing??...Existing in which table?...Every id in the productsubtype will be existing in the producttype table as its a foreign key. Please be more elaborate about what u mean by "Existing".

Vinu Vijayan

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-04-10 : 03:36:36

Thanks DonAtWork, this is perfect! Very easy and clear to understand. Makes me feel ashamed I couldn't come up with it myself...

@vinu: I don't think I could have explained it much better than "insert a record "Common" in the ProductSubType table for each existing ProductType". I thought it was clear that the ProductType table is the master table and the ProductSubType table the child table.
So your "Every id in the productsubtype will be existing in the producttype table as its a foreign key" is not true. For every ID in the ProductType table, there could be none, one or more records in the the ProductSubType table.
I wanted at least one record "Common" in the ProductSubType table for every existing(!) record in the ProductType table. The DDL of the tables should have explained this as well, where you can see the foreign key constraint created for the ProductSubType table.
Finally I ended my post with a piece of pseudo code, where I start with "For Each ID in ProductType". Which also should have made it clear.
My apologies for the confusion.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-10 : 04:59:56

Please Correct me if I am wrong.

The ProductSubType table has a field which is a Foreign Key. Doesn't that mean that this table can't have new values which are not present in the table it references, in which this case is the ProductType table.

Doesn't Foreign Key field mean that it only has values which are references of values in the table it is referencing?

Vinu Vijayan

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-04-10 : 06:01:22

Exactly.

So for each existing ProductType I wanted a record in the ProductSubType with the value of "Common".

Example of the end result

ProductType
-------------------
ID	Description
1	'Product 1'
2	'Product 2'
3	'Product 3'



Product SubType
-------------------------------------------------------
ID	FK_ProductType_ID	Description
1	1			'Subtype for Product 1'
2	1			'Common'
3	2			'Subtype for Product 2'
4	2			'Common'
5	3			'Common'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources