Unable to compare SQL and datatable and copy new entries to sql table

Question

I try to run a Left Join on my SQL Server 2012 database in VB.NET and I am stuck with this piece of code:

Dim dtTabelle1 as DataTable
(first Column of dtTabelle1 = Zyklus ID)

Dim con As SqlConnection
Dim conString, cmdString As String
Dim cmd As SqlClient.SqlCommand

conString = "path"
con = New SqlConnection(conString)
con.Open()

dtTabelle1.TableName = "Test"

cmdString = "INSERT INTO Daten ([Zyklus ID])" & _
                "Select [Zyklus ID]" & _
                "FROM Test " & _
                "LEFT JOIN Daten ON (Test.[Zyklus ID] = Daten.[Zyklus ID])" & _
                    "WHERE (Daten.[Zyklus ID] IS NULL);"

cmd = New SqlCommand(cmdString, con)
cmd.CommandType = CommandType.Text

cmd.ExecuteNonQuery()
con.Close()

I get an error:

Invalid Object name 'Test'

(Btw I can't change the blank in "Zyklus ID" :( ).

dtTabelle1 is filled and the schema is identical with the target database on the server Daten.

Answer 1

It will be better if You Use Alias For Table

Try like this:

cmdString = "INSERT INTO Daten ([Zyklus ID])" & _
                "Select T.[Zyklus ID]" & _
                "FROM Test T " & _
                "LEFT JOIN Daten D ON (T.[Zyklus ID] = D.[Zyklus ID])" & _
                    "WHERE (D.[Zyklus ID] IS NULL);"

Answer 2

Please put the appropriate spaces between the SQL statements of your query

cmdString = "INSERT INTO Daten ([Zyklus ID]) " & _
            "SELECT [Zyklus ID] " & _
            "FROM Test " & _
            "LEFT JOIN Daten ON (Test.[Zyklus ID] = Daten.[Zyklus ID]) " & _
            "WHERE (Daten.[Zyklus ID] IS NULL);"