Insert and Left Outer Join Tables

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

Transact-SQL (2000)

Insert and Left Outer Join Tables

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2005-11-16 : 16:05:24

I don't understand how to insert a record or a recordset into a set of normalized tables that have left outer joins. How is that done?

The data originates in a table with the following columns...

CompanyName FormType CIK DateFiled SEC WebAddress
A R 1324999 20050419 ...49.txt
B 8 946738 20050427 ...91.txt
C 10 1062273 20050427 ...16.txt
C 10 1062273 20050427 ...17.txt
D R 1225082 20050421 ...34.txt
E 4 1318932 20050427 ...69.txt
F 8 883787 20050427 ...70.txt
G 5 910638 20050427 ...58.txt
G 5 910638 20050427 ...59.txt
H 4 58592 20050427 ...08.txt
H 4 58592 20050427 ...09.txt

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

The normalized tables would look something like this...(correct?)

Company_Name_and_CIK
CompanyName CIK
A 1324999
B 946738
C 1062273
D 1225082
E 1318932
F 883787
G 910638
H 58592

Form_Type
FormType FormType_ID
4 1
5 2
8 3
10 4
R 5

Date_Filed DateFiled_ID
DateFiled 1
20050419 2
20050421 3
20050427 4

SEC_WEB_Address
SECWebAddress SECWebAddress_ID
...08.txt 1
...09.txt 2
...16.txt 3
...17.txt 4
...34.txt 5
...49.txt 6
...58.txt 7
...59.txt 8
...69.txt 9
...70.txt 10
...91.txt 11

So how does one insert new data to this type of table structure?

Thanks,

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'4';
SET @DateFiled = 20050421;
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)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-16 : 20:10:43

INSERT statements affect one table. For each table that you want data to be inserted into, you'll need another statement.

Tara Kizer
aka tduggan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-16 : 21:12:26

if you have foreign key on the tables, you need to insert into the tabels in the correct sequence.

Example : Company_Name_and_CIK, Date_Filed first before _Company_Info_01

and you will need to use @@identity to obtain the ID inserted

[KH]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-16 : 22:31:24

Why do you have a "DateFiled" table? A date is an attribute, something that should be stored with a transaction, not an entity that you need to track and generate artifical "ID"'s for. What purpose would breaking dates out into another table serve?

I think you might need some help with your design to properly normalize it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources