SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/22/2007 :  10:49:11  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by combi

Hi.

What about calculated values?

When I try to bulkcopy a table with a calculated column, i get a sqlexception.

Any Ideas?
Thany You.
Chris.



Specify the column mappings and exclude the calculated column. The article explains how to do this.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

wieitan
Starting Member

Israel
6 Posts

Posted - 06/09/2008 :  05:09:26  Show Profile  Reply with Quote
Hello
I have a massive performance problem, when trying to insert a large amount of input data into SQL Server. The data is stored in 60 (more or less) tables.
Does this mechanism of SqlBulkCopy works also on
DataSet - Collection of several Tables ?

Thanks in advance
Eitan

Eitan
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 06/09/2008 :  06:20:16  Show Profile  Visit spirit1's Homepage  Reply with Quote
you have to load each table by itself.
what kind of a perf problem?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

wieitan
Starting Member

Israel
6 Posts

Posted - 06/09/2008 :  08:07:57  Show Profile  Reply with Quote
Hello!
Thanks for the quick reply.
I Insert into Database by using SqlDataAdapter.Insert Command
The performance problem are as follows:
- At first it seems work O.K. (Counting every 500 Event ==
Rows of primary table).
- After a porion of time, insert rate becomes slower and slower.
The Task Manager shows: less ans less activity
of Sql Server & My Application processes.

I understand I need to insert many rows in one operation,
this is the reason I thaught SqlBulkCopy would help me.
I need to insert data into several tables together becuse of thier
connectivity. I hope SqlBulkCopy can be tied somehow to DataSet

I hpoe I make myself clear.
Thanks
Eitan

Eitan
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 06/09/2008 :  08:29:42  Show Profile  Visit spirit1's Homepage  Reply with Quote
well it isn't.
SqlBulkCopy inserts per table. you have to disconnect your PK FK constraints
or load the tables in the correct order.

and try using the Tablock when loading. this will speed up bulk insert but will prevent other processes to change data while bulk insert is in progress.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

wieitan
Starting Member

Israel
6 Posts

Posted - 06/10/2008 :  14:54:35  Show Profile  Reply with Quote
Hello!
Thanks again for the quick reply.
I woukd like to ask the following:
1) Can I solve my performance problem
(slower & slower for large amount of data), by adding rows to
Tables exist within In Memory DataSet.
Every 10,000 Event(== Rows), I shall call DataSet.AcceptChanges ()
Today I insert every row direct into DB, by using DataAdapter.
Is this approach better then previous one ?
2) If the answer to first question can be true, How shall I connect
between DataSet & DataAdapter, used for Connection & Transaction ?

Thanks again

Eitan
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 06/10/2008 :  14:57:20  Show Profile  Visit spirit1's Homepage  Reply with Quote
so why don't you use SqlBulkCopy?
adding so many rows to a dataset isn't reccomended and you can run out of memory if there are to many rows.
dataset was never meant for bulk inserts.



_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

wieitan
Starting Member

Israel
6 Posts

Posted - 06/10/2008 :  15:13:26  Show Profile  Reply with Quote
Hello!
1) I think I can not use SqkBulkCopy, because some of the tables are
connected by PK & FK.
2) About memory run out. I mean every 10,000 (or other constant),
The contents of DataSet shall be inseted to DB, and all rows
shall be deleted from DB. Do you think insert performance
can be better from other approach, Every row is inserted into DB ?

Thank you very much for your support



Eitan
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 06/10/2008 :  15:18:24  Show Profile  Visit spirit1's Homepage  Reply with Quote
so drop PK-FK relationships before you insert and recreate them after that.

be aware that when doing inserts with dataadapter you are inserting row per row. this means that your insert command is
getting called once for each row. this is tremendous overhead.

again this is not the way to go for bulk inserts.


_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

wieitan
Starting Member

Israel
6 Posts

Posted - 06/11/2008 :  14:37:20  Show Profile  Reply with Quote
Hello!
Thanks again for the quick reply.

I would like to ask about inset perfromance
(again, hopefully for the last time):
- Can I use SqlBulkCopy for each DataTable within DataSet,
without deleting the relationship: PF - FK ?
- Can I insert via SqlBulkCopy the Tables with PK first
and then load the tables with FK after that table ?
- Every N:M connection is being divide into 1:N connection:
PF - FK.

Thank you very much for your support

Eitan
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 06/11/2008 :  14:45:13  Show Profile  Visit spirit1's Homepage  Reply with Quote
ok once more:
forget the dataset.
load the tables with SqlBulkCopy from the parent to the child in correct order and that's it.

why are you so stuck on that dataset?



_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

wieitan
Starting Member

Israel
6 Posts

Posted - 06/11/2008 :  15:39:17  Show Profile  Reply with Quote
Hi!
First of all Thank you for your answers & patience.
I shall try it tomorrow at work.
Yoy ask: "why are you so stuck on that dataset" ?
As I see it:
- Dataset contains relations between DataTables (PK-FK).
- DataSet is a collection of all data in DataTables.

Thank you very much

Eitan
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 06/11/2008 :  15:53:08  Show Profile  Visit spirit1's Homepage  Reply with Quote
yes it is.
but for the purpose of batch loading it is completly useless.



_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

karthi2009
Starting Member

23 Posts

Posted - 05/20/2009 :  05:30:24  Show Profile  Reply with Quote
Hi,

I am using SqlBulkCopy Class to import data from Excel File. Currently I am facing an issue with DateTime Type Column.

Database Table Desc
--------------------
emp# int,
empname varchar(100),
timein datetime (Date with Time)

DataTable Columns Spec
-----------------------
emp# string,
empname string,
date string,
time string

before importing the data, I merge date and time columns to match database table column named 'timein'.

DateTime dTime = Convert.ToDateTime(date + " " + time);

But when I try to execute it, I am getting an exception in WriteToServer() method. The exception I am getting is "System.FormatException: String was not recognized as a valid DateTime.".

Please guide me out in this regard.

Thanks in advance.
Karthikeyan

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000