Copy records from one database to another (Teradata to SQL Server)
I have a project where I need to query a Teradata database and then copy the records returned to a SQL Server database. I can hit the Teradata db no problem and I can get the results into a DataTable. The SQL server db is already setup and has the same columns as the Teradata results (except for the auto id column). I am having trouble figuring out how to take the records in the DataTable and insert them into the SQL server db.
Here is what i have with some pseudo code where I didn’t think the details were relevant:
Using cn As New TdConnection("User Id=XYZ12345;Password=XYZ12345;Data Source=teradataserver.company.com;Persist Security Info=False") cn.Open() Dim cmd As TdCommand = cn.CreateCommand() 'build the SELECT part of the command we will issue cmd.CommandText = GetTeradataSqlString() 'setup the DataAdapter Dim da As New TdDataAdapter(cmd) ' Provider specific types will be used in the data table da.ReturnProviderSpecificTypes = False 'True=Use Teradata types, False=Use .NET types ' Adapter will determine how many statements will be batched da.UpdateBatchSize = 0 Dim cb As New TdCommandBuilder(da) 'create a DataTable to hold our returned data Dim dtCheck As New DataTable("TableCheck") ' Filling the data table with data retrieved from the select statement da.Fill(dtCheck) 'create a DataSet to hold all of our tables Dim dsMain As New DataSet("MainDataset") 'now we add the DataTable to our DataSet dsMain.Tables.Add(dtCheck) 'at this point a cycle through the DataTable to the debug window shows we have the data we need from the Teradata db. 'now we will pump it into our SQL server database Dim connSqlSvr As New System.Data.SqlClient.SqlConnection connSqlSvr.ConnectionString = "Data Source=DestSqlServer;Initial Catalog=DestDb;Connect Timeout=15" connSqlSvr.Open() 'now we create a SQL command to take the data in the Teradata DataTable and insert it into the SQL server table Dim sqlCmd As New SqlCommand With sqlCmd .CommandType = CommandType.Text Dim sbSqlCmd As New StringBuilder sbSqlCmd.AppendLine("INSERT INTO [DestDb].[dbo].[Events] ([CityCode],[CarNum],[VIN],[Fleet],[EventItm])") sbSqlCmd.AppendLine("SELECT City,CarNo,VIN,Fleet,EventDesc FROM @MyTable;") .CommandText = sbSqlCmd.ToString Dim sqlParam As New SqlParameter sqlParam.ParameterName = "@MyTable" sqlParam.SqlDbType = SqlDbType.Structured sqlParam.Value = dtCheck sqlParam.TypeName = "TableCheck" .Parameters.Add(sqlParam) .Connection = connSqlSvr Dim rowsAffectedLoad As Integer = .ExecuteNonQuery() debug.print(rowsAffectedLoad & " rows were loaded into the SQL server table.") End With 'close and dispose the SQL server database connection connSqlSvr.Close() connSqlSvr.Dispose() End Using
Running the code I get an exception:
"Column, parameter, or variable @MyTable. : Cannot find data type TableCheck."
I’ve looked for a method to insert a DataTable into a database and noticed many samples were using the INSERT INTO. I just dont think I am using the SqlParameter properly.
One Solution collect form web for “Copy records from one database to another (Teradata to SQL Server)”
Your example appears to be using a Table Valued Parameter of type TableCheck but you have not defined that type within SQL Server. See http://msdn.microsoft.com/en-us/library/bb510489.aspx
CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT );
Although I can’t guarantee that you can pass a TVP directly into a raw SQL statement.
I would actually suggest you use a different approach using SqlBulkCopy, http://msdn.microsoft.com/en-us/library/ex21zs8x(v=vs.110).aspx .