Insert and Left Outer Join - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Insert and Left Outer Join

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2005-11-15 : 02:58:22

How does one write INSERT queries for tables that have LEFT OUTER JOINS?

I have 5 tables (Company_Name_and_CIK, Date_Filed, Form_Type, SEC_Web_Address, Form_XML)

that all have left outer joins to one other table (_Company_Info_01)

The first 3 tables above (Company_Name_and_CIK, Date_Filed, Form_Type) generally don't change, but when a new record is added to the _Company_Info_01 table, there will be new values added to the last 2 tables (SEC_Web_Address, Form_XML).

I've looked around on the internet for the past 2 days trying to find a description of how to do this, but to no avail. I have a select and an insert query at the end of this, preceded by the table descriptions.

Any help would be much appreciated.

Thank you,

Paul

-------------------------------------------------------------
GO
CREATE TABLE [dbo].[_Company_Info_01](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CompanyNameCIK_ID] [int] NULL,
[FormType_ID] [int] NULL,
[DateFiled_ID] [int] NULL,
[SECWebAddress_ID] [int] NULL,
[FormXML_ID] [int] NULL,
[LastUpdated] [timestamp] NULL
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
-------------------------------------------------
GO
CREATE TABLE [dbo].[Company_Name_and_CIK](
[CompanyNameCIK_ID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CIK] [int] NOT NULL,
CONSTRAINT [PK_Company_Name_and_CIK] PRIMARY KEY CLUSTERED
(
[CompanyNameCIK_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Date_Filed](
[DateFiled_ID] [int] IDENTITY(1,1) NOT NULL,
[DateFiled] [int] NULL,
CONSTRAINT [PK_Date_Filed] PRIMARY KEY CLUSTERED
(
[DateFiled_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Form_Type](
[FormType_ID] [int] IDENTITY(1,1) NOT NULL,
[FormType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Form_Type] PRIMARY KEY CLUSTERED
(
[FormType_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Form_XML](
[FormXML_ID] [int] IDENTITY(1,1) NOT NULL,
[FormXML] [xml] NULL,
CONSTRAINT [aaaaaForm_XML_PK] PRIMARY KEY CLUSTERED
(
[FormXML_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[SEC_Web_Address](
[SECWebAddress_ID] [int] IDENTITY(1,1) NOT NULL,
[SECWebAddress] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_SEC_Web_Address] PRIMARY KEY CLUSTERED
(
[SECWebAddress_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

=======================================================

My select statement looks like this...
Use SEC

Declare @FormType nvarchar(50), @DateFiled int;

SET @FormType = N'13F-HR/A';
SET @DateFiled = 20000101;
SET NOCOUNT OFF;

SELECT
Company_Name_and_CIK.CompanyName,
Company_Name_and_CIK.CIK,
Form_Type.FormType,
Date_Filed.DateFiled,
SEC_Web_Address.SECWebAddress,
Form_XML.FormXML

FROM
_Company_Info_01
LEFT OUTER JOIN
Company_Name_and_CIK ON _Company_Info_01.CompanyNameCIK_ID = Company_Name_and_CIK.CompanyNameCIK_ID
LEFT OUTER JOIN
Date_Filed ON _Company_Info_01.DateFiled_ID = Date_Filed.DateFiled_ID
LEFT OUTER JOIN
Form_Type ON _Company_Info_01.FormType_ID = Form_Type.FormType_ID
LEFT OUTER JOIN
SEC_Web_Address ON _Company_Info_01.SECWebAddress_ID = SEC_Web_Address.SECWebAddress_ID
LEFT OUTER JOIN
Form_XML ON _Company_Info_01.FormXML_ID = Form_XML.FormXML_ID

WHERE (Form_Type.FormType = @FormType) AND (Date_Filed.DateFiled = @DateFiled)

--------------------------------------------------------------

My Insert attempt looks like this....

INSERT INTO [dbo].[_Company_Info_01]
([CompanyNameCIK_ID], [FormType_ID], [DateFiled_ID], [SECWebAddress_ID], [FormXML_ID])
VALUES (@CompanyNameCIK_ID,@FormType_ID,@DateFiled_ID,@SECWebAddress_ID,@FormXML_ID);
SELECT ID, CompanyNameCIK_ID, FormType_ID, DateFiled_ID, SECWebAddress_ID, FormXML_ID, LastUpdated
FROM _Company_Info_01
WHERE (ID = @@IDENTITY)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources