Query - 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)

Query

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2005-11-16 : 00:07:06

I download a file from the SEC that contains this data:

CompanyName FormType CIK DateFiled SECWebAddress
21ST CENTURY HOLDING CO 8-K 1069996 20050701 edgar/data/1069996/0001144204-05-020553.txt

Then I use the SECWebAddress field to acquire the XML that I then put in the FormXML field.

The entire record then looks like this:
CompanyName FormType CIK DateFiled SECWebAddress FormXML
21ST CENTURY HOLDING CO 8-K 1069996 20050701 edgar/data/1069996/0001144204-05-020553.txt Some XML

I want to insert this entire record into 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).

I believe this is the correct approach, if I want my tables to be normalized, right?

If it is the correct approach, then how should the Insert Query be written? Consider the data in tables Company_Name_and_CIK, Date_Filed, Form_Type to be static.

Thanks,

Paul

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-16 : 00:25:53

Are you going to give us DDL and DML for this? We can't really answer your questions without it. Take a look at this article on how to post:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

If you can post like that, we should be able to give you a good, quality answer instead of just guessing.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

kloepper
Yak Posting Veteran

77 Posts

Posted - 2005-11-16 : 00:48:55

There is a select at the bottom, any suggestions for an insert?

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'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)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources