Stumped - 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 2005 Forums

Transact-SQL (2005)

Stumped

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2010-08-03 : 15:10:05

I have an external db that I attach, which is not normalized, with about 500,000 records:

ExternalDB - tableA:
AcctNo, PDFFile, Index(Identity, incremented by 1) - the jjj, kk, lll represents pdf's just used as example:
101, jjjj,1
201, kkk,2
301, llll,3

that I need to insert the records into our normalized db - this process will happen several times a month.

The tablestatement and tableaccount contain a many-to-many relationship, thus the targets table to relate the 2.

The psuedo for the insert is:

1. Get acctNo from externalDB and compare against tableAcct. If exists, grab AcctID else insert account and grab scope Identity

2. Insert into tabletargets acctID and StatementID (statementID is a value passed in) and grab tableTargetID.

3. Insert into tablePDF targetID and PDFFile

In the sample tables, I'm using varchar to represent image of PDF files that will be stored:

CREATE TABLE [dbo].[ExternalDB_TableA](
[AcctNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Index] [int] IDENTITY(1,1) NOT NULL,
[PDFFile] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[TableStatement](
[StatementID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[TableAcct](
[AcctID] [int] IDENTITY(1,1) NOT NULL,
[AcctNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[TableTargets](
[TargetID] [int] NOT NULL,
[StateID] [int] NOT NULL,
[AcctID] [int] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[TablePDFs](
[TargetID] [int] NOT NULL,
[PDFFile] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

INSERT INTO ExternalDB_TableA
([AcctNo]
,[PDFFile])
VALUES
('101'
,'jjj')

INSERT INTO ExternalDB_TableA
([AcctNo]
,[PDFFile])
VALUES
('201'
,'kkk')

INSERT INTO ExternalDB_TableA
([AcctNo]
,[PDFFile])
VALUES
('301'
,'lll')

Expected output:

InternalDB
TableStatement (not needed for this)
StatementID, blah, yada
1, junk, more junk
2, junk, more junk
3, junk, more junk

TableAcct
AcctID, AcctNo
1, 101
2,201
3,301

TableTargets
TargetID, StateID, AcctID
1, 1, 1
2, 1, 2
3, 1, 3

TablePDFs
TargetID, PDFFile
1, jjj
2, kkk
3, lll

Can anyone help? I know somehow I have to loop through the records, but not sure how to do this

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-04 : 08:25:01

try this:


--output1
select 
	ta.AcctID
	,a.AcctNo
from ExternalDB_TableA as a
	join TableAcct as ta
	on ta.AcctNo = a.AcctNo

same logic applies to all other two outputs.

ann
Posting Yak Master

220 Posts

Posted - 2010-08-04 : 13:35:28

My thoughts are I need to loop through the externaldb table. I was searching on the net and found this:

DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(UserID) FROM @myTable)

-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1

-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
DECLARE @iUserName VARCHAR(50), @iPassword VARCHAR(50), @iEmail VARCHAR(50)

-- Get the data from table and set to variables
SELECT @iUserName = UserName, @iPassword = Password, @iEmail = Email FROM @myTable WHERE UserID = @I
-- Display the looped data
PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I)
PRINT 'UserName = ' + @iUserName + ', Password = ' + @iPassword + ' Email = '+ @iEmail
-- Increment the iterator
SET @I = @I + 1
END

Obviously, I need to change this for my needs - but can anyone see why this would/would not be a good idea?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources