Thread: SQL Insert statement syntax
View Single Post
•
•
Join Date: Oct 2008
Posts: 9
Reputation: pixelmeow is an unknown quantity at this point 
Solved Threads: 0
pixelmeow pixelmeow is offline Offline
Newbie Poster

SQL Insert statement syntax

 
0
  #1
Dec 2nd, 2008
Hello,

I'm using VB6 and SQL Server 2000.

I've got a problem with an insert statement that seems to be randomly failing. Its job is to copy everything from one table into another, removing duplicates using WITH IGNORE_DUP_KEY ON [PRIMARY]. Then my code copies the records back to the original table, duplicate free.

This is my VB code:
VB Syntax (Toggle Plain Text) 
  1.     strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
  2.            & "       CASE_IDENTIFIER" & vbCrLf _
  3.            & "      ,DOCUMENT_TYPE" & vbCrLf _
  4.            & "      ,DOCUMENT_NUMBER" & vbCrLf _
  5.            & "      ,MILESTONE" & vbCrLf _
  6.            & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  7.            & "      ,MILESTONE_DATE)" & vbCrLf _
  8.            & "(SELECT CASE_IDENTIFIER" & vbCrLf _
  9.            & "       ,DOCUMENT_TYPE" & vbCrLf _
  10.            & "       ,DOCUMENT_NUMBER" & vbCrLf _
  11.            & "       ,MILESTONE" & vbCrLf _
  12.            & "       ,MILESTONE_DATE_TYPE" & vbCrLf _
  13.            & "       ,MILESTONE_DATE " & vbCrLf _
  14.            & "   FROM CASE_MILESTONE_TEMP)"

The SQL works in TOAD and Query Analyzer, every time. The code works in my program, on the machine I'm using right now, also every time. I've got two other machines I'm using for testing, with their own databases, and this code fails in the program, but works in TOAD or Query Analyzer.

As I'm debugging this today, it is failing. I removed the second pair of parentheses from the code above (I saw an insert statement here today while trying to find the answer, and it didn't have the select statement in parentheses) and it worked. I am now ripping my hair out.

Either form works in TOAD or QA. It would seem that either works in VB, *unless it doesn't feel like working*. Does anyone have any idea about this?

Thanks,
teresa
Reply With Quote