stored proceedure vs regular query

[ccampbell]

I have a very long SQL query consisting of about 200 inserts. Someone suggested creating a stored proceedure with these queryies. Each line of the query is the same except the field names. (i.e.
INSERT INTO TEST (c1, c2,...cn) VALUES (v1, v2,...vn)

or

SQL = "INSERT INTO TEST(C1) VALUE (V1)
INSERT INTO TEST (C2) VALUE (V2)
...
INSERT INTO TEST (CN) VALUE (VN)"

How would I create a stored proceedure for these statements, and what would be the advantage to doing it that way rather than the way that I currently have it?

 

[tlbroadbent]

Are you sure you want to INSERT? INSERT appends new records or rows to a table. The way you describe your process, you are updating or inserting multiple columns in a single record or row.

You should be doing one of the following.

INSERT TblName (c1, c2, c3, ..., cN)
VALUES (v1, v2, v3, ..., vN)

UPDATE TblName
SET c1=v1, c2=v2, c3=v2, ..., cN=vN
WHERE <your criteria>

If I've missed understood, please provide some more info to help me understand your process. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[ccampbell]

No, I need to actually INSERT new records to a specific table. Here is the scenario.
1. I have a given string to insert into the database (between 100 and 300 components in the string)
2. I take the id number from that string and search the entire database for that is number. If the id number is found, all records (from teh primary table and child tables) relating to that string are deleted from the database. (The string that will be inserted into the database contains all of the information in an updated format that is needed to replace what was deleted).
3. If the id number is not found, then the string will be parsed and inserted into a table in the database using the insert function.

I am new to database programming, so I am not sure that this is the most effective way to handle things, but it works. If you have other suggestions for making this more effective please feel free to share them. Thanks

 

[tlbroadbent]

Does you table only have one column? I'm confused by your description that lists C1 thru CN. That notation suggests multiple columns in the same table. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm