" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
If you want to copy all the data from one table to another ,then this tip can be helpful to you. The following code snippet copies all the data from theEmployee Table to “Employee1” provided the tables have same columns and constraints.
Public Sub TranseferAllEmployeeData()
Dim Sqlcmd As New SqlCommand
Dim strSQL As String = String.Empty
Dim dt As New DataTable
Try
Using SqlCon As New SqlConnection("Connection String")
strSQL = "Select * from Employee "
Sqlcmd = New SqlCommand(strSQL, SqlCon)
SqlCon.Open()
Dim myReader As SqlDataReader = Sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
Try
If myReader.HasRows Then
dt.Load(myReader)
Dim bulkData As SqlBulkCopy = New SqlBulkCopy(SqlCon)
SqlCon.Open()
bulkData.DestinationTableName = "Employee1"
bulkData.WriteToServer(dt)
myReader.Close()
End If
Catch ex As Exception
End Try
End Using
Catch ex As Exception
End Try
End Sub
Public Sub TranseferEmployeeDataWithColumnMapping()
Dim Sqlcmd As New SqlCommand
Dim strSQL As String = String.Empty
Dim dt As New DataTable
Try
Using SqlCon As New SqlConnection("Connection String")
strSQL = "Select EmployeeFirstName,EmployeeLastName from Employee "
Sqlcmd = New SqlCommand(strSQL, SqlCon)
SqlCon.Open()
Dim myReader As SqlDataReader = Sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
Try
If myReader.HasRows Then
dt.Load(myReader)
Dim bulkData As SqlBulkCopy = New SqlBulkCopy(SqlCon)
Dim mapEmpFName As New SqlBulkCopyColumnMapping("EmployeeFirstName", "EmpFName")
bulkData.ColumnMappings.Add(mapEmpFName)
SqlCon.Open()
Dim mapEmpLName As New SqlBulkCopyColumnMapping("EmployeeLastName", "EmpLName")
bulkData.ColumnMappings.Add(mapEmpLName)
bulkData.DestinationTableName = "Employee1"
bulkData.WriteToServer(dt)
myReader.Close()
End If
Catch ex As Exception
End Try
End Using
Catch ex As Exception
End Try
End Sub