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.ExecuteScalar(strConn, _
"myStoredProc", _
param1, _
param2, _
param3
.....))

Update:
Same idea, but use SqlHelper.ExecuteNonQuery

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_month varchar,
@cc_expires_year varchar,
@cc_cvn varchar,
@ip varchar,
@active bit,
@hearaboutus_other 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_month,
cc_expires_year,
cc_cvn,
ip,
active,
hearaboutus_other,
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_month,
@cc_expires_year,
@cc_cvn,
@ip,
@active,
@hearaboutus_other,
@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 ColumnDescription,
c.data_type as DataType,
c.character_maximum_length as DataTypeSize
from information_schema.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.sysproperties as p on p.id = o.id and p.smallid =
c.ordinal_position
Where c.table_name = 'myTableName'

--Michael

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.ExecuteScalar(strConn, _
"myStoredProc", _
param1, _
param2, _
param3
....))

Update:
Same idea, but use SqlHelper.ExecuteNonQuery

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_month varchar,
@cc_expires_year varchar,
@cc_cvn varchar,
@ip varchar,
@active bit,
@hearaboutus_other 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_month,
cc_expires_year,
cc_cvn,
ip,
active,
hearaboutus_other,
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_month,
@cc_expires_year,
@cc_cvn,
@ip,
@active,
@hearaboutus_other,
@check_number,
@browser_type,
@entry_person
)
SELECT CAST(@@IDENTITY AS INTEGER)
GO