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:

  • Add “Select top 1000” command to toolbar in SSMS
  • How to backup a SQL Server 2014 Express Localdb (.mdf) file programmatically
  • How to emulate a BEFORE INSERT trigger in T-SQL / SQL Server for super/subtype (Inheritence) entities?
  • How to get Previous Value for Null Values
  • Trigger Bulk Update without Cursor
  • Why does LINQ to SQL translate GroupBy into multiple queries
  •         Using cn As New TdConnection("User Id=XYZ12345;Password=XYZ12345;Data;Persist Security Info=False")
                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 
                'create a DataSet to hold all of our tables
                Dim dsMain As New DataSet("MainDataset")
                'now we add the DataTable to our DataSet
                '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"
                '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"
                    .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
            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.

  • How to filter the String based on , and delete the duplicates
  • SQLAlchemy order by function result
  • Grouping data in SQL Server for particular patterns
  • SQL query for finding rows with special characters only
  • SQL Server: Self-reference FK, trigger instead of ON DELETE CASCADE
  • What is the SQL command to return the field names of a table?
  • 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

    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, .

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.