Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sanghavi17389
Starting Member
26 Posts |
 Posted - 2012-05-03 : 06:18:30
|
| I have made a datatable.Now i want to update that datatable records in existing sql database table.By this I am doing bulk update.please let me know how can I update all records of sql table by my datatable records?harshal sanghavi |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-03 : 07:17:05
|
| The usual terminology people use is DATABASE and TABLEs in a database.DATATABLE usually refers to an object in a .Net application (and may be other places).Where is your DATATABLE? Is it in a text file, excel file, a data table in a .Net application, or some place else? How you would bring in the data in that DATATABLE into the SQL Database Table depends largely on what type of DATATABLE it is. |
 |
|
|
sanghavi17389
Starting Member
26 Posts |
Posted - 2012-05-03 : 07:39:54
|
| I making data table in application only.using system.data.datatable.In that I am collecting all records.Now I want to update sql table with these data of datatable which i made.please go through my code so you can get it well. PositionUpdates.CommandText = "SELECT DRM.RunningBoard,DRM.DutyBoard,DRM.DriverId,DRM.PublicServiceCode,DRM.ServiceCode,DRM.DepotCode,DRM.Direction,DRM.EOJMethod,DRM.JourneyNo,CONVERT(datetime, MW.MessageTimeStamp,2) as MessageTimeStamp FROM DimRequestMasterTestHS DRM JOIN ETMMessageTestHS EM ON EM.ETMMessageTestHSID= DRM.ETMMessageID JOIN MessageWrapperTestHS1 MW ON EM.WrapperID= MW.ID WHERE ((case when MW.MessageTimeStamp like '%00/00/00%' or MW.MessageTimeStamp like '%03/01/01%' then '01/01/9999' else CONVERT(datetime, MW.MessageTimeStamp,2) end) between dateadd(day,datediff(day,2,'" + today.Date + "'),0) and dateadd(day,datediff(day,0,'" + today.Date + "'),0))"; PositionUpdates.Connection = myStagingConnection; da.SelectCommand = PositionUpdates; da.Fill(ds, "PositionUpdates");I am filling this datatable.now updating rows in that. foreach (DataRow pr in ds.Tables["PositionUpdates"].Rows) { if (Convert.ToDateTime(pr["MessageTimeStamp"]) >= JourneyStart && Convert.ToDateTime(pr["MessageTimeStamp"]) <= JourneyEnd) { pr["RunningBoard"] = RunningBoard; pr["DutyBoard"] = DutyBoard; pr["DriverId"] = DriverId; pr["PublicServiceCode"] = PublicServiceCode; pr["ServiceCode"] = ServiceCode; pr["DepotCode"] = DepotCode; pr["Direction"] = Direction; pr["EOJMethod"] = EOJMethod; pr["JourneyNo"] = JourneyNo; } } ds.Tables["PositionUpdates"].AcceptChanges(); SqlBulkCopy bulkCopy = new SqlBulkCopy(myStagingConnection, SqlBulkCopyOptions.TableLock, null); bulkCopy.DestinationTableName = "dbo.DimRequestMasterTestHS"; bulkCopy.BatchSize = ds.Tables["PositionUpdates"].Rows.Count; bulkCopy.WriteToServer(ds.Tables["PositionUpdates"], DataRowState.Modified); bulkCopy.Close();I am confused because only some of column I am updating.Number of columns in datatable which i made and number of columns in sql table are different.harshal sanghavi |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-03 : 07:54:35
|
| I don't have experience with using SqlBulkCopy when the source and destination columns are different. However, the documentation seems to indicate that you CAN do this. There is description and example code here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopycolumnmappingcollection.aspx |
|
|
|
|
|
|
|
|
Update sql database table by Datatable which I ha