Database

Re: SQL BulkCopy using IDataReader - how can you cast the fields?

26-Dec-12 20:32

Well I would say you are screwed, only because you are trying to transform the data in your load. IMHO transforms are the biggest time waster ever inflicted on the load process. I would split the operations to a load process and then a transform. Caveat I am not addressing the size issue as I have never had the problem (and 500mb is very small beer in our environment).

I would do the following.
Read in the data file and get the header record.
Create a staging table in sql server exactly matching the column headers - every column to be varchar or nvarchar if needed
Convert the csv file to a datatable (This article [^]may help).
Use BulkCopy to load the data (everything is varchar so it WILL load)
Use a stored proc to do the transforms from the staging table to the target table. If you have to use a dictionary file then you are going to have to take that into account and it will be a challenge (probably a crap load of dynamic sql).
Drop the staging table

Never underestimate the power of human stupidity
RAH

AllanW1111127-Dec-12 6:02

AllanW11111

27-Dec-12 6:02

That's a very interesting idea that should work. I would need to create a temporary SP based on the fields and data types for the conversion. It also pushes the field validation to the SP but that shouldn't be a problem.

Thanks for the feedback.

Re: SQL BulkCopy using IDataReader - how can you cast the fields?

PIEBALDconsult27-Dec-12 8:48

PIEBALDconsult

27-Dec-12 8:48

Allan Watkins wrote:
a DataTable gets fully populated

That sounds like a bad idea; there's no need to have more than one record in memory at a time.

Allan Watkins wrote:
cannot be converted

Sounds like you may need to specify a Parse routine. Take a look at the values that are causing the trouble -- it's possible that they are empty.

Subquery returns more than 1 row

Jassim Rahma24-Dec-12 1:02

Jassim Rahma

24-Dec-12 1:02

Hi,

Why I am getting the above error for this stored procedure?

SQL

CREATE DEFINER=`root`@`192.168.1.%` PROCEDURE `sp_open_file`(IN param_file_no int, IN param_clinic_id int, IN param_doctor_id int, IN param_account_category int, IN param_account_id int)
BEGIN
    DECLARE param_patient_account_number BIGINT;
    DECLARE param_price_list_id int;
    DECLARE param_account_number BIGINT;

    SET param_patient_account_number = (SELECT account_number FROM patients WHERE file_no = param_file_no);

    IF (param_account_category  = 1) THEN
        SET param_account_number = param_patient_account_number;
    ELSE
        SET param_account_number = (SELECT account_number FROM accounts WHERE account_id = param_account_id);
    END IF;

    SET param_price_list_id = (SELECT price_list_id FROM accounts WHERE account_number = param_account_number);

    INSERT INTO visits (file_no, clinic_id, doctor_id, account_category, account_number, price_list_id) VALUES (param_file_no, param_clinic_id, param_doctor_id, param_account_category , param_account_number, param_price_list_id);
    UPDATE patients SET file_is_open = TRUE;
END

Technology News @ www.JassimRahma.com

Eddy Vluggen24-Dec-12 1:29

Eddy Vluggen

24-Dec-12 1:29

..because one of the three subqueries returns more than one row.

SQL

(SELECT account_number FROM patients WHERE file_no = param_file_no);
(SELECT account_number FROM accounts WHERE account_id = param_account_id);
(SELECT price_list_id FROM accounts WHERE account_number = param_account_number);

Meaning that the table holds more than a single record with the requested Id.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

MaulikDusara24-Dec-12 21:44

MaulikDusara

24-Dec-12 21:44

Quote:
Meaning that the table holds more than a single record with the requested Id.

True, this is the main reason for such errors. Many resolution are available for this errors.
1) Use Top 1 keyword i.e.

SQL

(SELECT TOP 1 account_number FROM patients WHERE file_no = param_file_no);
(SELECT TOP 1 account_number FROM accounts WHERE account_id = param_account_id);
(SELECT TOP 1 price_list_id FROM accounts WHERE account_number = param_account_number);

2) Some of your data require 1 or more condition (filter) in your sql query.

3) If any duplicate entry found, Please remove.

Maulik Dusara
Sr. Sofware Engineer

Eddy Vluggen25-Dec-12 2:41

Eddy Vluggen

25-Dec-12 2:41

MaulikDusara wrote:
True, this is the main reason for such errors. Many resolution are available for this errors.

1) Use Top 1 keyword i.e.

That's assuming that there's more of those records allowed in the table, and being an "Id", this COULD lead to an inconsistent database - exaggerating the problem.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Mycroft Holmes26-Dec-12 11:17

26-Dec-12 11:17

I agree with Eddy, while top 1 fixes this problem it ignores the underlying data problem that the user was expecting 1 record per ID and is not getting the correct result.

Never underestimate the power of human stupidity
RAH

MaulikDusara26-Dec-12 18:45

MaulikDusara

26-Dec-12 18:45

Quote:
not getting the correct result.

I also agree and I also mentioned that

Quote:
Many resolution are available for this errors

Please also check my other resolutions.

Maulik Dusara
Software Engineer/Team Leader

On the Naming of Columns for Lookup tables.

Eddy Vluggen27-Dec-12 4:24

Eddy Vluggen

27-Dec-12 4:24

MaulikDusara wrote:
Please also check my other resolutions.

That's the point; he should not "try resolutions", but analyze the bug; if there are multiple records coming back on a key-field selection, chances are that there's an inconsistency. Selecting the "top 1" might hide that problem a little longer.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Brady Kelly17-Dec-12 22:44

17-Dec-12 22:44

I have drafted a rough design of a new database for a job application web site. I have a quintillion lookup tables with no more than a primary key and a string column, e.g. Province, with Id int and Province nvarchar(50). In this case, Province seems a natural and logical name for the description field, but in others the description is more vague, and better named as simple Description.

Now, if I use the name Description for all description columns in the DB I gain the opportunity to use more generalized T-SQL and C# code to deal with lookup tables, but I lose in semantics, especially where e.g. a WorkCategory lookup table grows to represent a business entity and not just a value domain; then Description would become less meaningful among more than just two columns.

I understand there is no black and white, clear cut answer to this question, but I am only asking for suggestions and input from people who may have faced similar dilemmas before.

Mycroft Holmes17-Dec-12 23:19

17-Dec-12 23:19

Do NOT go down the path of description, do precisely what you are doing, name your fields intelligently and when nothing comes to mind only then use description. It'll save you a crap load of refactoring in the future.

I use a code generator which reduces the cost of refactoring and I still get irritated when I do this.

Never underestimate the power of human stupidity
RAH

Brady Kelly17-Dec-12 23:26

17-Dec-12 23:26

Mycroft Holmes wrote:
I use a code generator which reduces the cost of refactoring

So do I, being the T4 based code generated by the EF Wizard or Power Tools, but I'm interested in what causes the mountains of refactoring you are warning me against. Is it, as I suspect, when you need to rename Description to something more specific?

Mycroft Holmes18-Dec-12 1:11

18-Dec-12 1:11

Brady Kelly wrote:
Is it, as I suspect, when you need to rename Description to something more specific?

You suspect correctly, although I have to admit my problem is caused more by using views and never allowing Select * From ViewName

But I find it immensely irritating to get to the reporting phase of an app only to wish I had used something more descriptive than description. And having to rename them in a query when you have 5 description fields hanging off a Fact table view!

Never underestimate the power of human stupidity
RAH

Brady Kelly18-Dec-12 1:15

18-Dec-12 1:15

I see. I'm currently on a big renaming exercise, and will try and reduce my Description usage. Luckily that field isn't used in any FK's.

PIEBALDconsult18-Dec-12 4:05

PIEBALDconsult

18-Dec-12 4:05

I mostly agree with Mycroft; the odds definitely favour what he recommends.

But as he and I seem to work on very different types of systems, I generally never display the column headings and in many cases ( Code , Meaning ) will suffice. Yet in many others a broader range of text values is required, so more descriptive names are suitable; one such I have here has ( ID , Tag , Description ) -- the Tag is used for a Dictionary and the Description is used in a ComboBox. There's another example here that pre-dates me wherein four different text values are required ( ID , Name , Description , LongName , ShortName ).

Oh, I just remembered another one that I created a week or two ago ( Code , Meaning , Description ) -- the Description is intended only as documentation for developers looking at the table; this table contains flags to be used in bitmaps.

What matters more is using names like "Name" and "Description" which are or may be reserved words -- you should probably avoid that, and by being in the habit of using more descriptive names you may never need to worry about that.

Consider each table on its own, don't try to enforce a single rule on all of them, but err on the side of caution. As I hope I showed here, habits are hard to break, so please try not to follow my path. Sigh | :sigh:

(Now I'm picturing Jacob Marley...)

Brady Kelly18-Dec-12 4:12

18-Dec-12 4:12

PIEBALDconsult wrote:
Description is intended only as documentation for developers looking at the table

That kind of info I am storing in a separate data dictionary, which has not yet moved beyond an Excel doc, but I am seriously considering writing something to use SQL Server's Extended Properties for data dictionary data.

PIEBALDconsult wrote:
What matters more is using names like "Name" and "Description" which are or may be reserved words -- you should probably avoid that, and by being in the habit of using more descriptive names you may never need to worry about that.

Yes, and even names that aren't reserved but are prickly, like having a table called ReferenceType with Id and Type columns. I have avoided all of those things.

PIEBALDconsult18-Dec-12 4:46

PIEBALDconsult

18-Dec-12 4:46

Brady Kelly wrote:
SQL Server's Extended Properties

Can those be applied to individual rows? Even if so, would a developer look there when perusing the table?

Brady Kelly18-Dec-12 5:34

On Strategies to Spell Check the Structure of a Database.

18-Dec-12 5:34

Duh, no.

I had crossed channels in my mind when I write that. Blush | :O

Brady Kelly14-Dec-12 3:02