Click here to Skip to main content
15,882,315 members
Please Sign up or sign in to vote.
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
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.

I actually accomplished this by using a union operation for common types in conjunction with a bulk insert. Thanks guys.
 
Share this answer
 
try BULK INSERT
http://technet.microsoft.com/en-us/library/ms188365.aspx
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900