درج و آپدیت 1000 رکورد به صورت یکجا در sql

[negar.rafie]

سلام
چطوری میتونیم 1000 رکورد را به صورت یکجا وارد جدول کنیم؟

[morteza271]

سلام.
به این صورت :
Insert Into Table1(Col1, Col2)
Select Col1, Col2 From Table2

[systam]

سلام
به صورت یکجا نه
بوسیله حلقه میتونی این کار رو انجام بدی

[negar.rafie]

اخه توی یه جایی خوندم اگر از حلقه استفاده کنیم به حدی که برسه ارور میده
و پیشنهاد شده از BULK INSERT استفاده بشه
میشه راهنمایی کنید

[morteza271]

اخه توی یه جایی خوندم اگر از حلقه استفاده کنیم به حدی که برسه ارور میده
و پیشنهاد شده از BULK INSERT استفاده بشه
میشه راهنمایی کنید
این هزار رکورد کجا هستن؟
توی جدول دیگه ای هستن؟ یا مثلا یه مقدار خاصی میخواین؟
بیشتر توضیح بدین تا بهتون بگم چیکار کنین...

[systam]

اخه توی یه جایی خوندم اگر از حلقه استفاده کنیم به حدی که برسه ارور میده
یک دیشکنری پیدا کردم بودم بانکش اکسس بود میخواستم تبدیلش SQL که 72 هزار رکورد داشت
باید حلقه ساده تمامشو ریختم داخل بانک SQL

[negar.rafie]

داخل یه فایل داخل دیتاتیبل

[systam]

حالا اطلاعاتت چی هست اون هزار تا رکورد ?
میتونم برات مثال بزنم البته به زبون Vb

[rezaei_y]

یه مقداری زمان میبره مثلا حدود 96 هزار رکورد تو تقریبا 11 دقیقه ولی هیچ خطایی نداد

[morteza271]

اگه این امکان رو میخواین داخل برنامه تون بذارین راه دیگه ای جز همون حلقه ندارین. بعید هم میدونم خطا بده!

ولی اگه فقط یه فایل دارین که میخواین اطلاعاتشو انتقال بدین با استفاده از Import کردن فکر کنم بتونین انتقال بدین.
اگر هم به صورت مستقیم به sql نشد میتونین اول بریزین توی اکسس بعد بریزین توی sql

موفق باشید

[negar.rafie]

حالا این BULK INSERT چی هستش؟
ایا بهتر از حلقه نیست

[veniz2008]

حالا این BULK INSERT چی هستش؟
ایا بهتر از حلقه نیست

سلام.
در چنین مواردی که قراره تعداد زیادی رکورد رو پشت سر هم درج کنید، حتما از bulkInsert استفاده کنید چون روش های معمولی سرعت بسیار پایینی دارند.
فرض کنید یه جدول TblStudent با 3 فیلد StudentdName،StudentID و StudentFamily دارید که می خواید با یک دیتاتیبل که در اختیار دارید پرش کنید :
استفاده از Bulk :

SqlConnection con = new SqlConnection("server=.\\md2012;database=test;integrated security=true");
SqlBulkCopy bulk = new SqlBulkCopy(con);
bulk.DestinationTableName = "TblStudent";
con.Open();
bulk.WriteToServer(dt);
con.Close();
MessageBox.Show("Added");
سرعتش با روش معمولی و استفاده از حلقه اصلا قابل مقایسه نیست.
موفق باشید.

[negar.rafie]

درسته همینه فقط آقای ونیز دیتاتیبل دارای نام ستون است و موقع درج پیغام میده؟
The given value of type String from the data source cannot be converted to type int of the specified target column

یا شاید ترتیب ستونها با هم فرق دارن

[veniz2008]

درسته همینه فقط آقای ونیز دیتاتیبل دارای نام ستون است و موقع درج پیغام میده؟
The given value of type String from the data source cannot be converted to type int of the specified target column

یا شاید ترتیب ستونها با هم فرق دارن
ترتیب ستون های جدولتون و دیتاتیبل باید یکی باشه، چون بصورت اتومات هر ستون دیتاتیبل روی فیلد متناظر جدول، نگاشت میشه.
اگر ستون های دیتاتیبل، بیشتر از تعداد ستون های جدولتون هست، ابتدا دیتاتیبل رو اصلاح کنید (ستون های اضافه رو حذف کنید) و بعد از یکسان سازی دیتایتبل با جدول، از Bulk استفاده کنید.

[negar.rafie]

ترتیب را عوض کردم حالا پیغام زیر را میده؟
The given value of type String from the data source cannot be converted to type nvarchar of the specified target column
میگه نمیتونه داخل نوع nvarchar ذخیره کنه
چکار کنم

[negar.rafie]

ممنون مشکل حل شد
حالا ایا برای ابدایت این هزار رکورد هم دستوری داریم که به صورت یکجا همه را ابدایت کنیم؟

[veniz2008]

ترتیب را عوض کردم حالا پیغام زیر را میده؟
The given value of type String from the data source cannot be converted to type nvarchar of the specified target column
میگه نمیتونه داخل نوع nvarchar ذخیره کنه
چکار کنم
خطای رخ داده، احتمالا بخاطر کمبود فضای فیلد nvarchar بوده (اگر طول داده هاتون زیاده از (nvarchar(max استفاده کنید).
نوع داده فارسی رو حتما nvarchar بگیرید وگرنه مشکل ساز خواهد شد.

[sohil_ww]

می خوای این کار یه بار انجام بشه یا هر سری انجام بشه ؟

[negar.rafie]

ممنون مشکل شد
فقط برای ابدایت به صورت یکجا چکار کنم؟
ایا دستوری داریم که بشه همه ی رکوردها را به صورت یکجا ابدایت کرد؟یا نه باید یکی یکی ابدایت کنیم؟

[veniz2008]

فقط برای ابدایت به صورت یکجا چکار کنم؟
ایا دستوری داریم که بشه همه ی رکوردها را به صورت یکجا ابدایت کرد؟یا نه باید یکی یکی ابدایت کنیم؟
سناریوی شما برای آپدیت کردن باید مشخص باشه.
1. برای همه رکوردهای جدول یا بعضی از رکوردهای جدول ،میخواید ستون (هایی) رو به یک مقدار مشخص تغییر بدید : در چنین مواردی همون یک دستور Update ای که می نویسید کفایت میکنه و سرعتش مناسب است (حدود 200 هزار رکورد رو حدودا 1 ثانیه ویرایش میکنه).
2. هدفتون اینه که برای هر رکورد، یکسری از مقادیر رو تغییر بدید. یعنی بعضی از رکوردهاتون دست نخورده قراره باقی بمونن و بعضی های دیگه به مقادیر مختلف قراره تغییر پیدا کنند. توی چنین جاهایی یه راه حل خوب استفاده از جدول مجازی هستش. به این شکل که رکوردهای جدید رو درون یک جدول موقت (temp) می ریزن و بعد با جدول اصلی JOIN می کنن (روی فیلد مشترک که معمولا کلید جدول هست). در واقع دستور UPdate شما، از JOIN درونش استفاده می کنید تا رکوردهای مشترک بین دو جدول اصلی و موقت شناسایی و بروزرسانی شود و سایر رکوردها دست نخورده باقی بمونن.
اگر نام جدول موقتتون TblTemp باشه با 3 فیلد StdName،StdID و StdFamily که شامل رکوردهایی هست که قراره ویرایش بشن و جدول اصلی شما هم جدول TblStudent باشه (با هر تعداد فیلدی). کد شما به اینصورت میشه :

Update TblStudent Set StudentName = StdName, StudentFamily = StdFamily From TblStudent INNER JOIN TblTemp ON TblStudent.StudentID = TblTemp.StdID
بعد از انجام عملیات آپدیت ، با دستور drop این جدول موقت رو حذف کنید تا منابعی رو که از سیستم گرفته، پس بگیرید.

[negar.rafie]

خوب اگر جدول را حذف کنیم دفعه بعد چکار کنیم؟
دوباره باید جدول را بسازیم؟

[veniz2008]

خوب اگر جدول را حذف کنیم دفعه بعد چکار کنیم؟
دوباره باید جدول را بسازیم؟
جدول موقت رو حذف می کنیم نه جدول اصلی رو.
جدول موقت شما، شامل رکوردهایی است که باید درون جدول اصلی اعمال بشن، حالا بعد از اعمال این تغییرات، دیگه نیازی به جدول موقت نیست و با دستور drop جدول موقت رو حذف می کنیم.

[negar.rafie]

منظورم همون جدول موقت است؟

[veniz2008]

منظورم همون جدول موقت است؟
شما یک بار کدنویسی رو انجام می دید و هر بار که قصد ویرایش داشتید از همون کدها استفاده می کنید. تمام این کارها با کدنویسی سمت #C انجام میشن :
الگوریتم کار بصورت کامل به شرح زیر هستش :
1. ساخت یک جدول مجازی با تعداد فیلدهایی که قصد ویرایش دارید.
2. ریختن رکوردهای جدید که قراره روی رکوردهای قدیمی، ریخته (بروز) بشن درون این جدول موقت. بهترین راه برای این کار استفاده از همون Bulk Insert هستش. پس این رکوردها رو با Bulk Insert به جدول موقت اضافه کنید (سرعت این کار بسیار بالا هستش).
3. استفاده از دستور Update ای که با JOIN بکار میره. توی این مرحله کار تمومه.
4. حذف کردن جدول موقت برای برگشت منابع به سیستم در همون لحظه (اینم بگم که با قطع ارتباط با sql این جدول موقت خود به خود حذف میشه ولی ممکنه کاربر سریعا قطع ارتباط نکنه و بی جهت چندین ساعت منابع سیستم شما توسط این جدول موقت استفاده بشه. به همین خاطر است که میگم بعد از انجام کارهاتون از drop استفاده کنید).

[negar.rafie]

حالا چطوری این جدول موقت را بسازم و حذف کنیم؟
واقعا شرمندم

[veniz2008]

حالا چطوری این جدول موقت را بسازم؟
نکته اول اینکه جداول موقت با # شروع میشن (حتما باید با # شروع بشن) :
کد زیر جدول موقت رو ایجاد میکنه و داده هایی رو که درون دیتاتیبل ریختید و قراره روی رکوردهای قدیمی ریخته بشن رو درون این جدول موقت قرار میده :

SqlConnection con = new SqlConnection("server=.\\md2012;database=test;integrated security=true");
SqlCommand cmd = new SqlCommand("Create Table #TblTemp(StdID int,StdName nvarchar(50), StdFamily nvarchar(50))", con);
con.Open();
cmd.ExecuteNonQuery();
SqlBulkCopy bulkCopy = new SqlBulkCopy(con);
bulkCopy.DestinationTableName = "#TblTemp";
bulkCopy.WriteToServer(dt);
con.Close();
MessageBox.Show("جدول موقت ایجاد و رکوردهای مورد نظر به آن اضافه گردید");
حالا باید دستور Join رو به ادامه کدهای بالا اضافه کنید. فقط یه نکته خیلی مهم رو بهش توجه کنید، اونم ;()con.Close هست. این کد رو فقط زمانی استفاده کنید که کارتون با جدول موقت تموم شده، در واقع همونطور که بالا هم گفتم هر زمان که کانکشن قطع بشه، جدول موقت هم از بین میره (همونطور که دستور drop هم همین کار رو انجام میده). توصیه میکنم این دستور رو در خط آخر کدهاتون قرار بدید تا خیال خودتون رو راحت کنید.

[negar.rafie]

ممنون دوست عزیز
فقط قبل از اینکه از این کد استفاده کنم
می خواستم بدونم ایا با کلاس SqlCommandBuilder هم میشه این کار را کرد؟
اگر میشه فرق با جدول موقت چیه؟

[veniz2008]

ممنون دوست عزیز
فقط قبل از اینکه از این کد استفاده کنم
می خواستم بدونم ایا با کلاس SqlCommandBuilder هم میشه این کار را کرد؟
اگر میشه فرق با جدول موقت چیه؟
بله امکان پذیره ولی ...
سرعت کار کردن با جدول موقت ( و کلا کار با دیتابیس سمت sql ) قابل مقایسه با کار کردن در سمت سی شارپ نیست.
توصیه کلی که می تونم توی این زمینه بهتون داشته باشم اینه هر جایی که با داده های دیتابیس سر و کار دارید اول تحقیق کنید که آیا SQL برای اون منظور راهکاری داره یا نه، اگر داشت حتما از امکانات خودش استفاده کنید چون این امکانات به بهترین شکل، بهینه شده هستن تا جایی که می تونید کمتر در سمت سی شارپ از ابزار فیلتر و مقایسه استفاده کنید.
با استفاده از SQLCommandBuilder :
فرض بگیرید که یک دیتاتیبل دارید که کل اطلاعات جدول اصلی رو نگهداری میکنه و یه دیتاتیبل هم رکوردهای جدیدی رو که باید با رکوردهای قبلی جایگزین (بروزرسانی) بشه.
در کدهای زیر :
dt : دیتاتیبلی که شامل تمامی رکوردهای جدول اصلی است.
dt2 : دیتاتیبلی که رکوردهای جدیدی که قراره روی مقادیر قبلی ریخته بشن رو شامل میشه. برای سادگی کار از همون جدول اصلی با یک شرط، نصف رکوردهای جدول اصلی رو بیرون کشیدم (با شرط زوج بودن ID) و ستون نام خانوادگی رو تغییر دادم (به آخرشون مثلا کلمه test رو اضافه کردم).
و سپس درون حلقه، سطرهای دیتاتیبل ها رو با هم مقایسه کردم و هرجایی که این برابری صورت گرفته از break استفاده کردم تا مقایسه بی جهتی صورت نگیره و در خارج از حلقه، دیتاتیبل شامل جدول اصلی (dt) رو که درون حلقه ویرایش شده رو بروزرسانی کردم تا بر روی جدول درون دیتابیس اعمال بشه.
روند کار برای حدود 2500 رکورد جدید (بعد از فیلتر شدن از بین 5000 رکورد) بسیار ناامید کننده بود و برای من حدود 35 ثانیه زمان گرفت.
کدها رو مشاهده کنید :

SqlConnection con = new SqlConnection("server=.\\md2012;database=test;integrated security=true");
SqlDataAdapter da = new SqlDataAdapter("select StudentID,StudentName ,StudentFamily from TblStudent", con);
DataTable dt = new DataTable();
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.Fill(dt);
//
SqlDataAdapter da2 = new SqlDataAdapter("select StudentID,StudentName ,StudentFamily + ' test' AS StdFamily from TblStudent where StudentID % 2 = 0", con);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
//
for(int i=0;i<dt2.Rows.Count;i++)
{
for(int j =0;j<dt.Rows.Count;j++)
{
if(dt2.Rows[i]["StudentID"].ToString() == dt.Rows[j]["StudentID"].ToString())
{
dt.Rows[j]["StudentFamily"] = dt2.Rows[i]["StdFamily"];
break;
}
}
}
con.Open();
da.Update(dt);
con.Close();
MessageBox.Show("Updated");
استفاده از جدول موقت برای همون جدول با همون مقدارهای مورد قبل :

SqlConnection con = new SqlConnection("server=.\\md2012;database=test;integrated security=true");
SqlDataAdapter da = new SqlDataAdapter("select StudentID,StudentName ,StudentFamily + ' test' AS StdFamily from TblStudent where StudentID % 2 = 0", con);
DataTable dt = new DataTable();
da.Fill(dt);
//
SqlCommand cmd = new SqlCommand("Create Table #TblTemp(StdID int,StdName char(50), StdFamily nvarchar(50))", con);
con.Open();
cmd.ExecuteNonQuery();
SqlBulkCopy bulkCopy = new SqlBulkCopy(con);
bulkCopy.DestinationTableName = "#TblTemp";
bulkCopy.WriteToServer(dt);
//
SqlCommand cmd2 = new SqlCommand("Update TblStudent Set StudentFamily = StdFamily From TblStudent INNER JOIN #TblTemp ON TblStudent.StudentID = #TblTemp.StdID", con);
cmd2.ExecuteNonQuery();
con.Close();
con.Dispose();
MessageBox.Show("Updated");
بروزرسانی برای 2500 رکورد در 1 ثانیه انجام شد.
همچنین همونطور که مشاهده می کنید حجم کدنویسی کمتر، خوانایی به مراتب بیشتر و مدیریت بهتر منابع رو هم شاهد هستیم.
موفق باشید.

[negar.rafie]

ممنون واقعا ممنون
خیلی کامل و حرفه ای جواب دادید
موفق باشید