How to avoid SQL Procedural insert processing

Question

0.00/5 (No votes)

See more:

Hello, I need a little help here on my queries: I have a table with structure:
TableWithRecords( Id int,Field1 varchar,Field2 money , Field3 date ,Field4 bit),
and I have a table representing each data type as
TableForText(Text varchar, TextType),

TableForDates(Date smalldatetime, DateType),

TableForMONEY(Amount MONEY, AmountType) and

TableForBIT(TORF BIT, BitType)

I have procedure which insert into each of the 4 tables above.

For each record in TableWithRecords, I call the procedures: to save amount fields in the record, dates field in record, text fields in the record and bit fields in the record and then I go to next record in TableWithRecords.
So that I have all amounts in one table, dates in one table, all texts in one table and all bits in one table - I have a way to form the rows again whenever I want.

Is there a way to not use a while loop or a cursor to do the processing? I do not want to do a row by row processing since it will slow the performance time. Any help?

Thanks.

Posted 26-Feb-14 0:01am

bassofa1

Add a Solution

Comments

Andrius Leonavicius 26-Feb-14 6:14am

Hi,

Let me ask you a question. Why do you need 4 tables representing each data type...?

bassofa1 26-Feb-14 9:58am

that is the way our schema is designed.

Andrius Leonavicius 26-Feb-14 10:34am

Does these 4 tables have foreign keys? Well, instead of procedure you could create trigger, which would fire automatically: AFTER INSERT, UPDATE or DELETE. Answering to your question: maybe you could do a BULK INSERT, UPDATE or DELETE? I could help you further, but I need some more details...

bassofa1 26-Feb-14 11:17am

I thought this is detailed enough..I am just spreading the data in TableWithRecords into multiple tables based on the type of the fields - so all dates in one table, amounts in one table, texts in one table; such that to get a complete record(row) one has to look in all the tables - I have no problem with saving/retrieving the fields but just didn't want to do row by row processing; because I feel my query will be slow when I get more records to deal with.

Andrius Leonavicius 26-Feb-14 12:30pm

OK, so I suggested you to create trigger and use BULK* INSERT, UPDATE or DELETE (if its suitable in your situation). If you are inserting, updating or deleting multiple records at once, you will get better performance with this because you don't need to use a cursor. But again, it depends on situation (that's I was asking for).

Did you get my idea or you need more explanation?

*By saying BULK, I mean insert, update or delete multiple rows at once.

NeverJustHere 26-Feb-14 15:33pm

Why do you want to split the data across multiple data tables based on data type?

Why is this better than having all the data in one table?

bassofa1 27-Feb-14 5:12am

Because keeping the data in one table will mean that anytime we identify that a new field has to be saved, we have to change the structure by adding new column. That is not desirable if you're dealing with a database which is used by many similar clients who have slight variation in their processes. By having different types having it's own table, I only have to create a row in a reference table when a new field needs to be added. I don't know if you get what I mean.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bassofa1 · Answer 1 · 2014-09-02T20:10:00

Solution 2

I actually accomplished this by using a union operation for common types in conjunction with a bulk insert. Thanks guys.

Posted 2-Sep-14 20:10pm

bassofa1

Suresh P V · Answer 2 · 2014-02-26T20:54:00

Solution 1

try BULK INSERT
http://technet.microsoft.com/en-us/library/ms188365.aspx

Posted 26-Feb-14 20:54pm

Suresh P V