Help getting started writing Stored Proc

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)

Help getting started writing Stored Proc

Author

Topic

speterson
Starting Member

11 Posts

Posted - 2005-10-27 : 15:22:48

I was told what I am trying to do can be accomplished by writing some stored procedures..."lots of coding but it can be done!". However, I am knew to stored procs and am not sure exactly where to start. At least twice a year I will be receiving files from all of our customers that has a number of things that we keep up to date in our customer information center. Each account will send a .dat file that I save as a .txt file and import into a database within SQL. Once imported into sql, I get a table name (that corresponds to the customer number) with 1 column and multiple records. Each record in the table contains a field label and it's corresponding value (from the customer's system). This field label is an actual field in my customer information SQL database. I need to step through each record to determine if I need this record in my system or not. If I do I need to pull the record and put it into a temp table. I will then take that temp table and pivot it so I will have two columns in order to get the actual value and field names. I would also like to have the pivot table act as the table that I will use to import the data into my customer database in that this pivot table will be appended on to with all of the other customers' information.

The problem is that I don't know where to start. Here is an example of what my source data looks like:

State Bank of Danvers Danvers MN 56231-0019
359
09/14/2005
BEGIN FORMS INFORMATION
BRANCH 1
CIF Check Digit A
Teller Machine B
Inquiry Charging Yes
Deposit Doc
Doc Transfer
Dep Doc File
Credit Line Statements S
END FORMS INFORMATION
BEGIN APPLICATION INFORMATION
Customer Info 3,475
CIF Alien Stat 2
CIF Demographics 0
Demand Deposit 1,864
END APPLICATION INFORMATION

The data stored between the BEGIN/END lines correspond to the tables in my SQL Customer Database. The ending result needs to look like this:

SQL Table: SysInfo_Bank
Fields
Values

Customer # Customer Info CIF Alien Stat
359 3,475 2
697 5,390 5
438 2,543 2

I am running SQL 2000. Where do I start? Thanks in advance...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-27 : 17:20:53

Hi S,

A good place to start would be providing DDL and DML for your source tables and target table. The link (below) says more about this. I'm guessing the source tables will be the tables that you import from your customer's dat files. Provide the code to create an example of those tables (DDL). Provide some insert statements that will populate the tables with some sample rows (DML). Finally provide the code to create the target table and post what the expected rows should look like based on your sample source data. Once you have that done, you'll probably get more help than you want

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

Be One with the Optimizer
TG

speterson
Starting Member

11 Posts

Posted - 2005-10-27 : 18:20:51

Here is what I have put together; I hope this clarifies it up a bit. I have not attempted any code as of yet. I'm not sure where to start. All I know at this point is that sprocs will some how get me what I need.

1)The source tables will be the tables that I import into SQL (via the Import/Export wizard) from the customers' .dat file that I saved as a text. The table is created as the customer number:

CREATE TABLE [359] (
[Col001] [varchar] (8000) NULL ,
[Col002] [varchar] (8000) NULL ,
[Col003] [varchar] (8000) NULL ,
[Col004] [varchar] (8000) NULL ,
[Col005] [varchar] (8000) NULL ,
[Col006] [varchar] (8000) NULL ,
[Col007] [varchar] (8000) NULL
) ON [PRIMARY]
GO

Populate the source table:

INSERT INTO [359] (Col001) VALUES ('State Bank of Danvers Danvers MN 56231-0019')
INSERT INTO [359] (Col001) VALUES ('359')
INSERT INTO [359] (Col001) VALUES ('09/14/2005')
INSERT INTO [359] (Col001) VALUES ('BEGIN FORMS INFORMATION')
INSERT INTO [359] (Col001) VALUES ('BRANCH 1')
INSERT INTO [359] (Col001) VALUES ('CIF Check Digit A')
INSERT INTO [359] (Col001) VALUES ('Teller Machine B')
INSERT INTO [359] (Col001) VALUES ('Doc Transfer')
INSERT INTO [359] (Col001) VALUES ('END FORMS INFORMATION')
INSERT INTO [359] (Col001) VALUES ('BEGIN APPLICATION INFORMATION')
INSERT INTO [359] (Col001) VALUES ('Customer Info 3,475')
INSERT INTO [359] (Col001) VALUES ('CIF Alien Stat 2')
INSERT INTO [359] (Col001) VALUES ('CIF Demographics 0')
INSERT INTO [359] (Col001) VALUES ('END APPLICATION INFORMATION')

2) The name of the source table will always be the Customer ID. I will need to take this number and join it to my Account table to get the internal Customer accountID. This table that will be joined to is the following:

CREATE TABLE [C_ACCOUNT] (
[ACCOUNTID] [char] (12) NULL ,
[CUSTOMER_ID] [int] NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Populate this table as follows:

INSERT INTO [C_ACCOUNT] (AccountID, Customer_ID) VALUES ('A6UJ9A00001','359')

3) The records in table [359] between BEGIN FORMS INFORMATION and END FORMS INFORMATION will need to some how get into the following target table:

CREATE TABLE [C_SYSINFO_FORM] (
[ACCOUNTID] [char] (12) NOT NULL ,
[CIF_CHECK_DIGIT] [char] (1) NULL ,
[TELLER_MACHINE] [char] (1) NULL ,
[DOC_TRANSFER] [char] (1) NULL ,
) ON [PRIMARY]
GO

The data in this target table will appear like so (field:value) based on the data in the source table and the joined account table.

AccountID:A6UJ9A00001
CIF_Check_Digit:A
Teller_Machine:B
Doc_Transfer:NULL

4) The records in table [359] between BEGIN APPLICATION INFORMATION and END APPLICATION INFORMATION will need to some how get into the following target table:

CREATE TABLE [C_SYSINFO_BANK] (
[ACCOUNTID] [char] (12) NULL ,
[CUSTOMER_INFO] [int] NULL ,
[CIF_ALIEN_STAT] [int] NULL ,
[CIF_DEMOGRAPHICS] [int] NULL ,
) ON [PRIMARY]
GO

The data in this target table will appear like so (field:value) based on the data in the source table and the joined account table.

AccountID:A6UJ9A00001
Customer_Info:3,475
CIF_Alein_Stat:2
CIF_Demographics:0

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-28 : 10:54:16

couple questions:

1. will the count of rows between BEGIN FORMS INFORMATION and END FORMS INFORMATION always be the same and the data always come in the same order?

2. same question for APPLICATION INFORMATION

3. will you only be running this code for one customer table at a time? ie: customer 359?

4. do you just need help with code that populates C_SYSINFO_FORM and C_SYSINFO_BANK?

Be One with the Optimizer
TG

speterson
Starting Member

11 Posts

Posted - 2005-10-28 : 11:58:37

1. The count of rows between BEGIN FORMS INFORMATION and END FORMS INFORMATION will always be the same and the data will always come in the same order.

2. same as above

3. I would like to somehow merge all of the data that I strip out from each of the individual customer files into two separate tables so that all of the customers exist in both tables. These two tables would be what I would use to write the DTS package to populate the C_SysInfo_Form and C_SysInfo_Bank tables in our customer database.

4. I need help with getting the data out of these files in a format that will allow #3 to happen.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-28 : 15:35:28

well, here is something that might help you get started. You can just paste this code into a QA window and run it.

I added an identity column as a primary key to your [359] table. This code creates a procedure that populates your 2 sysinfo tables and the c_account table based on passed in parameters. You're going to need to do additional validation if the imported data does not conform to the data types of the target tables. This also counts on what you said being true that your imported customer tables will always have the same values in the same order.


set nocount on 
set ansi_warnings off

CREATE TABLE [359] (
rowid int identity(1,1) primary key clustered ,
[Col001] [varchar] (8000) NULL ,
[Col002] [varchar] (8000) NULL ,
[Col003] [varchar] (8000) NULL ,
[Col004] [varchar] (8000) NULL ,
[Col005] [varchar] (8000) NULL ,
[Col006] [varchar] (8000) NULL ,
[Col007] [varchar] (8000) NULL 
) ON [PRIMARY]
GO


CREATE TABLE [C_ACCOUNT] (
[ACCOUNTID] [char] (12) NULL ,
[CUSTOMER_ID] [int] NULL ,
) 
GO


CREATE TABLE [C_SYSINFO_FORM] (
[ACCOUNTID] [char] (12) NOT NULL ,
[CIF_CHECK_DIGIT] [char] (1) NULL ,
[TELLER_MACHINE] [char] (1) NULL ,
[DOC_TRANSFER] [char] (1) NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [C_SYSINFO_BANK] (
[ACCOUNTID] [char] (12) NULL ,
[CUSTOMER_INFO] [int] NULL ,
[CIF_ALIEN_STAT] [int] NULL ,
[CIF_DEMOGRAPHICS] [int] NULL ,
) ON [PRIMARY]
GO


INSERT INTO [359] (Col001) VALUES ('State Bank of Danvers Danvers MN 56231-0019')
INSERT INTO [359] (Col001) VALUES ('359')
INSERT INTO [359] (Col001) VALUES ('09/14/2005')
INSERT INTO [359] (Col001) VALUES ('BEGIN FORMS INFORMATION')
INSERT INTO [359] (Col001) VALUES ('BRANCH 1')
INSERT INTO [359] (Col001) VALUES ('CIF Check Digit A')
INSERT INTO [359] (Col001) VALUES ('Teller Machine B')
INSERT INTO [359] (Col001) VALUES ('Doc Transfer')
INSERT INTO [359] (Col001) VALUES ('END FORMS INFORMATION')
INSERT INTO [359] (Col001) VALUES ('BEGIN APPLICATION INFORMATION')
INSERT INTO [359] (Col001) VALUES ('Customer Info 3,475')
INSERT INTO [359] (Col001) VALUES ('CIF Alien Stat 2')
INSERT INTO [359] (Col001) VALUES ('CIF Demographics 0')
INSERT INTO [359] (Col001) VALUES ('END APPLICATION INFORMATION')

go
-----------------------------------------------------------------------------

if object_id('dbo.SysInfoTables_Ins') > 0
	drop procedure dbo.SysInfoTables_Ins
go

create procedure dbo.SysInfoTables_Ins
	@companyid varchar(15)
	,@AccountID varchar(12)
AS
set nocount on 
set ansi_warnings off

INSERT INTO [C_ACCOUNT] (AccountID, Customer_ID) VALUES ('A6UJ9A00001', @companyid)

if object_id('dbo.temp_companyTB') > 0
	drop table dbo.temp_companyTB
exec ('select rowid, Col001 into dbo.temp_companyTB from [' + @companyid + ']')


--append rows to c_sysinfo_form for current customer
set ansi_warnings off
insert [C_SYSINFO_FORM]
	(accountid
	,cif_check_digit
	,teller_machine
	,doc_transfer)
select	[ACCOUNTID] = max(a)
	,[CIF_CHECK_DIGIT] = max(b)
	,[TELLER_MACHINE] = max(c)
	,[DOC_TRANSFER]  = max(d)
from	(
	select a = accountid
		,b = null
		,c = null
		,d = null
	from	c_account
	where	Customer_ID = @companyid
	union all
	select	a = null
		,b = case when rowid = 6 then ltrim(replace(Col001,'CIF Check Digit','')) end
		,c = case when rowid = 7 then ltrim(replace(col001,'Teller Machine','')) end
		,d = case when rowid = 8 then ltrim(replace(col001,'Doc Transfer','')) end
	from 	temp_companyTB a
	where	rowid between 6 and 9
	) a


insert [C_SYSINFO_BANK]
	([ACCOUNTID]
	,[CUSTOMER_INFO]
	,[CIF_ALIEN_STAT]
	,[CIF_DEMOGRAPHICS])
select	[ACCOUNTID] = max(a)
	,[CUSTOMER_INFO] = max(b)
	,[CIF_ALIEN_STAT] = max(c)
	,[CIF_DEMOGRAPHICS]  = max(d)
from	(
	select a = accountid
		,b = null
		,c = null
		,d = null
	from	c_account
	where	Customer_ID = @companyid
	union all
	select	a = null
		,b = case when rowid = 11 then ltrim(replace(replace(Col001,'Customer Info',''),',','')) end
		,c = case when rowid = 12 then ltrim(replace(col001,'CIF Alien Stat','')) end
		,d = case when rowid = 13 then ltrim(replace(col001,'CIF Demographics','')) end
	from 	temp_companyTB a
	where	rowid between 11 and 13
	) a

go

print 'use the procedure to populate the c_sysinfo tables'
exec dbo.SysInfoTables_Ins 
	@companyid = '359'
	,@accountid = 'A6UJ9A00001'

go
print ''
print 'return inserted values'
select * from c_account
select * from c_sysinfo_form
select * from c_sysinfo_bank

go

drop proc dbo.SysInfoTables_Ins
drop table [359]
drop table C_ACCOUNT
drop table [C_SYSINFO_FORM]
drop table [C_SYSINFO_BANK]
drop table temp_companyTB

go

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources