Best methods to INSERT and UPDATE?

[Max]

I'm looking for best methods in terms of performance and simplicity to do an
INSERT and UPDATE. Using Microsoft's Application Blocks and SQL Server
stored procedures, this is simple:

Insert:
iNewCustomerID = Convert.ToInt32 (SqlHelper.Exec uteScalar(strCo nn, _
"myStoredPr oc", _
param1, _
param2, _
param3
.....))

Update:
Same idea, but use SqlHelper.Execu teNonQuery

THE PROBLEM:
When I insert large tables like a "customers" table that has 50+ fields, now
my stored procedure becomes VERY large and harder to maintain.

Does anyone have any tips on making this easier? My stored proc just seems
way out of hand now (see below). Is there a way I can at least trim it
down?

Thanks!

Max

------- working stored proc that returns the new id --------------
CREATE PROCEDURE InsertCustomers
(
@discountid int,
@email varchar,
@password varchar,
@bill_company varchar,
@bill_firstname varchar,
@bill_lastname varchar,
@bill_add1 varchar,
@bill_add2 varchar,
@bill_city varchar,
@bill_state varchar,
@bill_postal varchar,
@bill_prov varchar,
@bill_country varchar,
@bill_phone varchar,
@bill_fax varchar,
@ship_company varchar,
@ship_firstname varchar,
@ship_lastname varchar,
@ship_add1 varchar,
@ship_add2 varchar,
@ship_city varchar,
@ship_state varchar,
@ship_postal varchar,
@ship_prov varchar,
@ship_country varchar,
@ship_phone varchar,
@ship_fax varchar,
@maillist bit,
@hearaboutus varchar,
@comment varchar,
@created datetime,
@lastlogin datetime,
@logincount int,
@cc_name varchar,
@cc_number varchar,
@cc_expires_mon th varchar,
@cc_expires_yea r varchar,
@cc_cvn varchar,
@ip varchar,
@active bit,
@hearaboutus_ot her varchar,
@check_number varchar,
@browser_type varchar,
@entry_person varchar
)
AS

INSERT INTO customers
(
discountid,
email,
[password],
bill_company,
bill_firstname,
bill_lastname,
bill_add1,
bill_add2,
bill_city,
bill_state,
bill_postal,
bill_prov,
bill_country,
bill_phone,
bill_fax,
ship_company,
ship_firstname,
ship_lastname,
ship_add1,
ship_add2,
ship_city,
ship_state,
ship_postal,
ship_prov,
ship_country,
ship_phone,
ship_fax,
maillist,
hearaboutus,
comment,
created,
lastlogin,
logincount,
cc_name,
cc_number,
cc_expires_mont h,
cc_expires_year ,
cc_cvn,
ip,
active,
hearaboutus_oth er,
check_number,
browser_type,
entry_person
)
VALUES
(
@discountid,
@email,
@password,
@bill_company,
@bill_firstname ,
@bill_lastname,
@bill_add1,
@bill_add2,
@bill_city,
@bill_state,
@bill_postal,
@bill_prov,
@bill_country,
@bill_phone,
@bill_fax,
@ship_company,
@ship_firstname ,
@ship_lastname,
@ship_add1,
@ship_add2,
@ship_city,
@ship_state,
@ship_postal,
@ship_prov,
@ship_country,
@ship_phone,
@ship_fax,
@maillist,
@hearaboutus,
@comment,
@created,
@lastlogin,
@logincount,
@cc_name,
@cc_number,
@cc_expires_mon th,
@cc_expires_yea r,
@cc_cvn,
@ip,
@active,
@hearaboutus_ot her,
@check_number,
@browser_type,
@entry_person
)
SELECT CAST(@@IDENTITY AS INTEGER)
GO

[Raterus]

Nope! unless you can trim out fields that aren't going to be
updated/inserted ever. Here's some things I've found that make this a
little easier.

The best way I've found to manage big statements like you have is to use
unix utilities like gawk and sed to automatically generate some of this
stuff, for a certain input text file. Like for example in an input file, I
have fieldnames, fieldtypes, lengths, etc (which by the way you can get out
of a sql query - which I pasted below), then I have awk/sed scripts that run
off this file to create everything I need to access the table. I've even
gone as far as automatically creating sql parameters for insertion into
code. This is very powerful to be able to do this, but you won't figure it
out in 2 minutes, it takes some time to realize what you can do with these
tools. Also too, you don't have to use these unix utilities, you can create
a program in the language you are comfortable with to generate this stuff.

Hope this helps too, it took me awhile to figure this one out, but this is
some sql to get this information out of sql server.

select
c.table_name as TableName,
c.column_name as ColumnName,
p.value as ColumnDescripti on,
c.data_type as DataType,
c.character_max imum_length as DataTypeSize
from information_sch ema.columns as c
inner join dbo.sysusers as u on u.name = c.table_schema
inner join dbo.sysobjects as o on o.name = c.table_name and o.uid = u.uid
left outer join dbo.sysproperti es as p on p.id = o.id and p.smallid =
c.ordinal_posit ion
Where c.table_name = 'myTableName'

--Michael

"Max" <ma*****@portvi sta.com> wrote in message
news:F2******** ************@tw ister.tampabay. rr.com...

I'm looking for best methods in terms of performance and simplicity to do an INSERT and UPDATE. Using Microsoft's Application Blocks and SQL Server
stored procedures, this is simple:

Insert:
iNewCustomerID = Convert.ToInt32 (SqlHelper.Exec uteScalar(strCo nn, _
"myStoredPr oc", _
param1, _
param2, _
param3
....))

Update:
Same idea, but use SqlHelper.Execu teNonQuery

THE PROBLEM:
When I insert large tables like a "customers" table that has 50+ fields, now my stored procedure becomes VERY large and harder to maintain.

Does anyone have any tips on making this easier? My stored proc just seems
way out of hand now (see below). Is there a way I can at least trim it
down?

Thanks!

Max

------- working stored proc that returns the new id --------------
CREATE PROCEDURE InsertCustomers
(
@discountid int,
@email varchar,
@password varchar,
@bill_company varchar,
@bill_firstname varchar,
@bill_lastname varchar,
@bill_add1 varchar,
@bill_add2 varchar,
@bill_city varchar,
@bill_state varchar,
@bill_postal varchar,
@bill_prov varchar,
@bill_country varchar,
@bill_phone varchar,
@bill_fax varchar,
@ship_company varchar,
@ship_firstname varchar,
@ship_lastname varchar,
@ship_add1 varchar,
@ship_add2 varchar,
@ship_city varchar,
@ship_state varchar,
@ship_postal varchar,
@ship_prov varchar,
@ship_country varchar,
@ship_phone varchar,
@ship_fax varchar,
@maillist bit,
@hearaboutus varchar,
@comment varchar,
@created datetime,
@lastlogin datetime,
@logincount int,
@cc_name varchar,
@cc_number varchar,
@cc_expires_mon th varchar,
@cc_expires_yea r varchar,
@cc_cvn varchar,
@ip varchar,
@active bit,
@hearaboutus_ot her varchar,
@check_number varchar,
@browser_type varchar,
@entry_person varchar
)
AS

INSERT INTO customers
(
discountid,
email,
[password],
bill_company,
bill_firstname,
bill_lastname,
bill_add1,
bill_add2,
bill_city,
bill_state,
bill_postal,
bill_prov,
bill_country,
bill_phone,
bill_fax,
ship_company,
ship_firstname,
ship_lastname,
ship_add1,
ship_add2,
ship_city,
ship_state,
ship_postal,
ship_prov,
ship_country,
ship_phone,
ship_fax,
maillist,
hearaboutus,
comment,
created,
lastlogin,
logincount,
cc_name,
cc_number,
cc_expires_mont h,
cc_expires_year ,
cc_cvn,
ip,
active,
hearaboutus_oth er,
check_number,
browser_type,
entry_person
)
VALUES
(
@discountid,
@email,
@password,
@bill_company,
@bill_firstname ,
@bill_lastname,
@bill_add1,
@bill_add2,
@bill_city,
@bill_state,
@bill_postal,
@bill_prov,
@bill_country,
@bill_phone,
@bill_fax,
@ship_company,
@ship_firstname ,
@ship_lastname,
@ship_add1,
@ship_add2,
@ship_city,
@ship_state,
@ship_postal,
@ship_prov,
@ship_country,
@ship_phone,
@ship_fax,
@maillist,
@hearaboutus,
@comment,
@created,
@lastlogin,
@logincount,
@cc_name,
@cc_number,
@cc_expires_mon th,
@cc_expires_yea r,
@cc_cvn,
@ip,
@active,
@hearaboutus_ot her,
@check_number,
@browser_type,
@entry_person
)
SELECT CAST(@@IDENTITY AS INTEGER)
GO