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

Re: SQL Insert statement syntax

 
0
  #6
Dec 4th, 2008
•
•
•
•
Originally Posted by Teme64 View Post
I'm trying to re-produce your case.

Here's my simplified tables.
Temp-table:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. USE [EditorTest]
  2. GO
  3. /****** Object:  Table [dbo].[CASE_MILESTONE_TEMP]    Script Date: 12/04/2008 10:35:36 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_PADDING ON
  9. GO
  10. CREATE TABLE [dbo].[CASE_MILESTONE_TEMP](
  11. 	[CASE_IDENTIFIER] [int] NULL,
  12. 	[MILESTONE_DATE] [datetime] NULL,
  13. 	[TEXT_FIELD] [varchar](50) NULL,
  14. 	[DROP_THIS_FIELD] [varchar](50) NULL
  15. ) ON [PRIMARY]
  16.  
  17. GO
  18. SET ANSI_PADDING OFF
Here's my milestone table:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. CREATE TABLE [CASE_MILESTONE_TEMP] (
  2. 	[CASE_IDENTIFIER] [varchar] (10)  NULL,
  3. 	[DOCUMENT_TYPE] [varchar] (3) NULL ,
  4. 	[DOCUMENT_NUMBER] [varchar] (3) NULL,
  5. 	[MILESTONE] [varchar] (10) NULL,
  6. 	[MILESTONE_DATE_TYPE] [varchar] (1) NULL,
  7. 	[MILESTONE_DATE] [varchar] (14) NULL,
  8. 	[MILESTONE_REMARKS] [varchar] (500) NULL 
  9. ) ON [PRIMARY]
  10. GO
I forgot there's no primary key... sorry...

•
•
•
•
Originally Posted by Teme64 View Post
and Clean-table:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. USE [EditorTest]
  2. GO
  3. /****** Object:  Table [dbo].[CLEAN_MILE_DUPES]    Script Date: 12/04/2008 10:35:05 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_PADDING ON
  9. GO
  10. CREATE TABLE [dbo].[CLEAN_MILE_DUPES](
  11. 	[CASE_IDENTIFIER] [int] NULL,
  12. 	[MILESTONE_DATE] [datetime] NULL,
  13. 	[TEXT_FIELD] [varchar](50) NULL
  14. ) ON [PRIMARY]
  15.  
  16. GO
  17. SET ANSI_PADDING OFF
Here's my clean table:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. CREATE TABLE [CLEAN_MILE_DUPES] (
  2. 	[ID] [int] NULL ,
  3. 	[CASE_IDENTIFIER] [varchar] (10) NULL,
  4. 	[DOCUMENT_TYPE] [varchar] (3) NULL,
  5. 	[DOCUMENT_NUMBER] [varchar] (3) NULL,
  6. 	[MILESTONE] [varchar] (10) NULL,
  7. 	[MILESTONE_DATE_TYPE] [varchar] (1) NULL,
  8. 	[MILESTONE_DATE] [varchar] (10) NULL
  9. ) ON [PRIMARY]
  10. GO

•
•
•
•
Originally Posted by Teme64 View Post
with CASE_IDENTIFIER as a duplicate key:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. USE [EditorTest]
  2. GO
  3. CREATE UNIQUE NONCLUSTERED INDEX [test] ON [dbo].[CLEAN_MILE_DUPES]
  4. (
  5.  [CASE_IDENTIFIER] ASC
  6. )
  7. WITH (IGNORE_DUP_KEY = ON)
Here's my index:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. CREATE  UNIQUE  INDEX [RM_MILE_DUPES] ON [dbo].[CLEAN_MILE_DUPES]
  2. (
  3.     [CASE_IDENTIFIER]
  4.    ,[DOCUMENT_TYPE]
  5.    ,[DOCUMENT_NUMBER]
  6.    ,[ID]
  7.    ,[MILESTONE]
  8.    ,[MILESTONE_DATE_TYPE]
  9.    ,[MILESTONE_DATE]
  10. ) 
  11. WITH  IGNORE_DUP_KEY  ON [PRIMARY]
  12. GO

•
•
•
•
Originally Posted by Teme64 View Post
and I tested with this code:
VB Syntax (Toggle Plain Text) 
  1. Option Explicit
  2.  
  3. Private oConn As ADODB.Connection
  4. Private oCmd As ADODB.Command
  5.  
  6. Private Sub Command1_Click()
  7. '
  8. Dim strSQL As String
  9. Dim ConStr As String
  10.  
  11. On Error GoTo ErrorHandler
  12.  
  13. ConStr = "PROVIDER=SQLOLEDB;Data Source=JASMIN\SQLEXPRESS; INITIAL CATALOG=EditorTest; User ID=XXXXXXX; Password=XXXXXXX;"
  14. Set oConn = New ADODB.Connection
  15. oConn.Open (ConStr)
  16.  
  17. strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
  18.            & "       CASE_IDENTIFIER" & vbCrLf _
  19.            & "       ,MILESTONE_DATE" & vbCrLf _
  20.            & "      ,TEXT_FIELD) " & vbCrLf _
  21.            & "(SELECT CASE_IDENTIFIER" & vbCrLf _
  22.            & "       ,MILESTONE_DATE" & vbCrLf _
  23.            & "       ,TEXT_FIELD " & vbCrLf _
  24.            & "   FROM CASE_MILESTONE_TEMP)"
  25.  
  26. Set oCmd = New ADODB.Command
  27. oCmd.CommandText = strSQL
  28. oCmd.CommandType = adCmdText
  29. Set oCmd.ActiveConnection = oConn
  30. oCmd.Execute
  31.  
  32. Sub_Exit:
  33.   oConn.Close
  34.   Exit Sub
  35. ErrorHandler:
  36.   ' Breakpoint here
  37.   Resume Sub_Exit
  38.  
  39. End Sub
The error I get in the Breakpoint-line is:
? oConn.Errors(0).Description
Duplicate key was ignored.
and that comes from a duplicate CASE_IDENTIFIER-field value from the Temp-table. And now the Case-table has all the rows from the Temp-table, except one dropped duplicate, and the column DROP_THIS_FIELD is missing of course.

Is this anything similar to your code?

(I tested with SQL Server 2005 EE, but that shouldn't make any difference)
Your code is very similar, just differences in how we do the database code:
VB Syntax (Toggle Plain Text) 
  1. Option Explicit
  2. Public cn As New ADODB.Connection
  3. Public cmd As New ADODB.Command
  4. Public rs As New ADODB.Recordset
  5.  
  6. Private Sub removeMileDupes()
  7.     Dim strSQL As String
  8.     If cn.State = 0 Then
  9.         'fileset says which set of files are being loaded at the moment
  10.         Call doDBaction("openDB", fileSet)
  11.     End If
  12.     'clean_dupes tables have ignore duplicates flag on
  13.     'vb catches the error and stops
  14.     'so resume next.
  15.     On Error Resume Next
  16.     strSQL = "TRUNCATE TABLE CLEAN_MILE_DUPES"
  17.     cmd.CommandText = strSQL
  18.     cmd.CommandType = adCmdText
  19.     Set rs = cmd.Execute
  20.  
  21.     strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
  22.            & "       CASE_IDENTIFIER" & vbCrLf _
  23.            & "      ,DOCUMENT_TYPE" & vbCrLf _
  24.            & "      ,DOCUMENT_NUMBER" & vbCrLf _
  25.            & "      ,MILESTONE" & vbCrLf _
  26.            & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  27.            & "      ,MILESTONE_DATE)" & vbCrLf _
  28.            & "SELECT CASE_IDENTIFIER" & vbCrLf _
  29.            & "      ,DOCUMENT_TYPE" & vbCrLf _
  30.            & "      ,DOCUMENT_NUMBER" & vbCrLf _
  31.            & "      ,MILESTONE" & vbCrLf _
  32.            & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  33.            & "      ,MILESTONE_DATE " & vbCrLf _
  34.            & "  FROM CASE_MILESTONE_TEMP"
  35.     cmd.CommandText = strSQL
  36.     cmd.CommandType = adCmdText
  37.     Set rs = cmd.Execute
  38. 'THIS ^^^ IS THE COMMAND THAT IS FAILING. IT HANGS FOR 5 OR 
  39. 'MORE SECONDS. DOING COUNT(*) ON THAT TABLE GIVES 0 
  40. 'RECORDS. THEREFORE THE FOLLOWING CODE JUST REMOVES 
  41. 'EVERYTHING FROM THE TEMP TABLE.
  42.  
  43.     strSQL = "TRUNCATE TABLE CASE_MILESTONE_TEMP"
  44.     cmd.CommandText = strSQL
  45.     cmd.CommandType = adCmdText
  46.     Set rs = cmd.Execute
  47.  
  48.     strSQL = "INSERT CASE_MILESTONE_TEMP (" & vbCrLf _
  49.            & "       CASE_IDENTIFIER" & vbCrLf _
  50.            & "      ,DOCUMENT_TYPE" & vbCrLf _
  51.            & "      ,DOCUMENT_NUMBER" & vbCrLf _
  52.            & "      ,MILESTONE" & vbCrLf _
  53.            & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
  54.            & "      ,MILESTONE_DATE)" & vbCrLf _
  55.            & "      SELECT CASE_IDENTIFIER" & vbCrLf _
  56.            & "             ,DOCUMENT_TYPE" & vbCrLf _
  57.            & "             ,DOCUMENT_NUMBER" & vbCrLf _
  58.            & "             ,MILESTONE" & vbCrLf _
  59.            & "             ,MILESTONE_DATE_TYPE" & vbCrLf _
  60.            & "             ,MILESTONE_DATE " & vbCrLf _
  61.            & "         FROM CLEAN_MILE_DUPES"
  62.     cmd.CommandText = strSQL
  63.     cmd.CommandType = adCmdText
  64.     Set rs = cmd.Execute
  65.  
  66.     strSQL = "TRUNCATE TABLE CLEAN_MILE_DUPES"
  67.     cmd.CommandText = strSQL
  68.     cmd.CommandType = adCmdText
  69.     Set rs = cmd.Execute
  70. End Sub
I want to remove all records that are duplicates on all fields. This code works perfectly on my machine, but deletes everything on the other two machines. It is failing randomly, it seems, and I'm stumped. I'm an Oracle person, and have been working with SQL Server only 4 months. Even so, I don't know that doing this with Oracle would make a difference.

Thank you so much for your help!
Reply With Quote