Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Inserting into Identical Table

    Hi guys I am dealing with two databases that are the same (as in column names and some data) I am trying to merge two tables (Parent_Scair and Parent_Ind)that have the same name and almost the same data except one has more then the other. So I am trying to consolidate. I keep getting an error message that says. Can anyone tell me what I am doing wrong pls

    Msg 102, Level 15, State 1, Procedure InsertParent_IndToParentScair, Line 7
    Incorrect syntax near ','.


    Code:
    [Parent ID] [nvarchar](50) NULL,
    	[Family ID] [nvarchar](50) NULL,
    	[Date of Referral] [nvarchar](50) NULL,
    	[Parent First Name] [nvarchar](50) NULL,
    	[Parent Last Name] [nvarchar](50) NULL,
    	[Parent SS#] [nvarchar](50) NULL,
    	[Telephone #] [nvarchar](50) NULL,
    	[Message #] [nvarchar](50) NULL,
    	[Address] [nvarchar](50) NULL,
    	[City] [nvarchar](50) NULL,
    	[State] [nvarchar](50) NULL,
    	[Zip] [nvarchar](50) NULL,
    	[E Mail Address] [nvarchar](50) NULL,
    	[Tribal Affiliation] [nvarchar](50) NULL,
    	[Event ID] [nvarchar](50) NULL,
    	[TANF staff making Referral] [nvarchar](50) NULL,
    	[Type Participant] [nvarchar](50) NULL,
    	[Weekly Hrs] [int] NULL,
    	[Mo Hours] [int] NULL,
    	[Special Instrution] [nvarchar](max) NULL,
    	[GED] [bit] NOT NULL,
    	[High School Diploma] [bit] NOT NULL,
    	[Drivers License] [bit] NOT NULL,
    	[Assessement Date] [bit] NOT NULL,
    	[Assessement] [datetime] NULL,
    	[Career Assessment Date] [bit] NOT NULL,
    	[Career Assessment] [datetime] NULL,
    	[Other] [bit] NOT NULL,
    	[Explain Other] [nvarchar](50) NULL,
    	[GED/High School Diploma-VC Adult School] [nvarchar](50) NULL,
    	[Higher Education] [nvarchar](50) NULL,
    	[Culture] [nvarchar](50) NULL,
    	[Community Service] [nvarchar](50) NULL,
    	[Vocational] [nvarchar](50) NULL,
    	[DMV] [nvarchar](50) NULL,
    	[Other 1] [nvarchar](50) NULL,
    	[Other 2] [nvarchar](50) NULL,
    	[Other 3] [nvarchar](50) NULL,
    	[Other 4] [nvarchar](50) NULL,
    	[Referral Status] [nvarchar](255) NULL,
    	[Service Area] [nvarchar](255) NULL

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by desireemm
    Can anyone tell me what I am doing wrong pls

    Msg 102, Level 15, State 1, Procedure InsertParent_IndToParentScair, Line 7
    Incorrect syntax near ','.
    You have a syntax error in the procedure InsertParent_IndToParentScair which you have not posted...

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    sorry about that


    Code:
    CREATE PROCEDURE [InsertParent_IndToParentScair]
    AS 
    INSERT INTO Parent_Scair
    SELECT ([Parent ID],
    	[Family ID] ,
    	[Date of Referral] ,
    	[Parent First Name] ,
    	[Parent Last Name],
    	[Parent SS#] ,
    	[Telephone #] ,
    	[Message #] ,
    	[Address] ,
    	[City] ,
    	[State],
    	[Zip] ,
    	[E Mail Address] ,
    	[Tribal Affiliation] ,
    	[Event ID] ,
    	[TANF staff making Referral] ,
    	[Type Participant] ,
    	[Weekly Hrs],
    	[Mo Hours] ,
    	[Special Instrution] ,
    	[GED] [bit] 
    	[High School Diploma] ,
    	[Drivers License] ,
    	[Assessement Date] ,
    	[Assessement] ,
    	[Career Assessment Date] ,
    	[Career Assessment] ,
    	[Other] [bit] ,
    	[Explain Other] ,
    	[GED/High School Diploma-VC Adult School] ,
    	[Higher Education],
    	[Culture] ,
    	[Community Service],
    	[Vocational] ,
    	[DMV] ,
    	[Other 1] ,
    	[Other 2] ,
    	[Other 3] ,
    	[Other 4] ,
    	[Referral Status] ,
    	[Service Area] )
    FROM Parent_Ind
    WHERE NOT EXISTS(SELECT * FROM Parent_Scair
    WHERE [Parent ID] = [Parent ID])

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your errors are
    Code:
    INSERT INTO Parent_Scair
    SELECT ([Parent ID],   <-- here
    	...
    	[Service Area] )  <-- and here
    FROM ...
    WHERE [Parent ID] = [Parent ID]) <-- and here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Ok so I revised it and it worked but the only problem is it returned 0 rows and theres theres a total of 77 records that have not been inserted

    Code:
    INSERT INTO Parent_Scair
                          ([Parent ID], [Family ID], [Date of Referral], [Parent First Name], [Parent Last Name], [Parent SS#], [Telephone #], [Message #], Address, City, State, Zip, 
                          [E Mail Address], [Tribal Affiliation], [Event ID], [TANF staff making Referral], [Type Participant], [Special Instrution], GED, [High School Diploma], 
                          [Drivers License], [Assessement Date], Assessement, [Career Assessment Date], [Career Assessment], Other, [Explain Other], 
                          [GED/High School Diploma-VC Adult School], [Higher Education], Culture, [Community Service], Vocational, DMV, [Other 1], [Other 2], [Other 3], [Other 4])
    SELECT     [Parent ID], [Family ID], [Date of Referral], [Parent First Name], [Parent Last Name], [Parent SS#], [Telephone #], [Message #], Address, City, State, Zip, 
                          [E Mail Address], [Tribal Affiliation], [Event ID], [TANF staff making Referral], [Type Participant], [Special Instrution], GED, [High School Diploma], 
                          [Drivers License], [Assessement Date], Assessement, [Career Assessment Date], [Career Assessment], Other, [Explain Other], 
                          [GED/High School Diploma-VC Adult School], [Higher Education], Culture, [Community Service], Vocational, DMV, [Other 1], [Other 2], [Other 3], 
                          [Other 4]
    FROM         Parent_Ind
    WHERE     (NOT EXISTS
                              (SELECT     [Parent ID], [Family ID], [Date of Referral], [Parent First Name], [Parent Last Name], [Parent SS#], [Telephone #], [Message #], Address, 
                                                       City, State, Zip, [E Mail Address], [Tribal Affiliation], [Event ID], [TANF staff making Referral], [Type Participant], [Weekly Hrs], [Mo Hours], 
                                                       [Special Instrution], GED, [High School Diploma], [Drivers License], [Assessement Date], Assessement, [Career Assessment Date], 
                                                       [Career Assessment], Other, [Explain Other], [GED/High School Diploma-VC Adult School], [Higher Education], Culture, 
                                                       [Community Service], Vocational, DMV, [Other 1], [Other 2], [Other 3], [Other 4], [Referral Status], [Service Area]
                                FROM          Parent_Scair AS Parent_Scair_1
                                WHERE      ([Parent ID] = [Parent ID])))

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you still have this problem --

    Code:
    WHERE (NOT EXISTS
       (SELECT ...  { see note 1 } 
          FROM Parent_Scair AS Parent_Scair_1
      WHERE ([Parent ID] = [Parent ID]))) { see note 2 }
    note 1: in a NOT EXISTS subquery, listing columns is superfluous

    some people say [NOT] EXISTS ( SELECT * which is one of the very few instances where it is actually okay to use the dreaded, evil "select star"

    some people say [NOT] EXISTS ( SELECT NULL which also works

    what matters is not the columns you select, but whether or not a row exists

    note 2: this is always going to be true as written -- you need to distinguish these columns as coming from different tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by r937
    you still have this problem --

    Code:
    WHERE (NOT EXISTS
       (SELECT ...  { see note 1 } 
          FROM Parent_Scair AS Parent_Scair_1
      WHERE ([Parent ID] = [Parent ID]))) { see note 2 }
    note 1: in a NOT EXISTS subquery, listing columns is superfluous

    some people say [NOT] EXISTS ( SELECT * which is one of the very few instances where it is actually okay to use the dreaded, evil "select star"

    some people say [NOT] EXISTS ( SELECT NULL which also works

    what matters is not the columns you select, but whether or not a row exists

    note 2: this is always going to be true as written -- you need to distinguish these columns as coming from different tables

    ok thank you R937 I appreciate it

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Ok got it now and the insert was successful

    Code:
    INSERT INTO Parent_Scair
                          ([Parent ID], [Family ID], [Date of Referral], [Parent First Name], [Parent Last Name], [Parent SS#], [Telephone #], [Message #], Address, City, State, Zip, 
                          [E Mail Address], [Tribal Affiliation], [Event ID], [TANF staff making Referral], [Type Participant], [Special Instrution], GED, [High School Diploma], 
                          [Drivers License], [Assessement Date], Assessement, [Career Assessment Date], [Career Assessment], Other, [Explain Other], 
                          [GED/High School Diploma-VC Adult School], [Higher Education], Culture, [Community Service], Vocational, DMV, [Other 1], [Other 2], [Other 3], [Other 4])
    SELECT     [Parent ID], [Family ID], [Date of Referral], [Parent First Name], [Parent Last Name], [Parent SS#], [Telephone #], [Message #], Address, City, State, Zip, 
                          [E Mail Address], [Tribal Affiliation], [Event ID], [TANF staff making Referral], [Type Participant], [Special Instrution], GED, [High School Diploma], 
                          [Drivers License], [Assessement Date], Assessement, [Career Assessment Date], [Career Assessment], Other, [Explain Other], 
                          [GED/High School Diploma-VC Adult School], [Higher Education], Culture, [Community Service], Vocational, DMV, [Other 1], [Other 2], [Other 3], 
                          [Other 4]
    FROM         Parent_Ind
    WHERE     (NOT EXISTS
                              (SELECT     [Parent ID], [Family ID], [Date of Referral], [Parent First Name], [Parent Last Name], [Parent SS#], [Telephone #], [Message #], Address, 
                                                       City, State, Zip, [E Mail Address], [Tribal Affiliation], [Event ID], [TANF staff making Referral], [Type Participant], [Weekly Hrs], [Mo Hours], 
                                                       [Special Instrution], GED, [High School Diploma], [Drivers License], [Assessement Date], Assessement, [Career Assessment Date], 
                                                       [Career Assessment], Other, [Explain Other], [GED/High School Diploma-VC Adult School], [Higher Education], Culture, 
                                                       [Community Service], Vocational, DMV, [Other 1], [Other 2], [Other 3], [Other 4], [Referral Status], [Service Area]
                                FROM          Parent_Scair 
                                WHERE      (Parent_Scair.[Parent ID] = Parent_Ind.[Parent ID])))

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by desireemm
    Ok got it now and the insert was successful
    congrats

    i think you missed the point i was trying to make in "note 1"

    your query will also work like this --

    Code:
    INSERT INTO Parent_Scair
                          ([Parent ID], [Family ID], [Date of Referral], [Parent First Name], [Parent Last Name], [Parent SS#], [Telephone #], [Message #], Address, City, State, Zip, 
                          [E Mail Address], [Tribal Affiliation], [Event ID], [TANF staff making Referral], [Type Participant], [Special Instrution], GED, [High School Diploma], 
                          [Drivers License], [Assessement Date], Assessement, [Career Assessment Date], [Career Assessment], Other, [Explain Other], 
                          [GED/High School Diploma-VC Adult School], [Higher Education], Culture, [Community Service], Vocational, DMV, [Other 1], [Other 2], [Other 3], [Other 4])
    SELECT     [Parent ID], [Family ID], [Date of Referral], [Parent First Name], [Parent Last Name], [Parent SS#], [Telephone #], [Message #], Address, City, State, Zip, 
                          [E Mail Address], [Tribal Affiliation], [Event ID], [TANF staff making Referral], [Type Participant], [Special Instrution], GED, [High School Diploma], 
                          [Drivers License], [Assessement Date], Assessement, [Career Assessment Date], [Career Assessment], Other, [Explain Other], 
                          [GED/High School Diploma-VC Adult School], [Higher Education], Culture, [Community Service], Vocational, DMV, [Other 1], [Other 2], [Other 3], 
                          [Other 4]
    FROM         Parent_Ind
    WHERE     (NOT EXISTS
                              (SELECT  937
                                FROM          Parent_Scair 
                                WHERE      (Parent_Scair.[Parent ID] = Parent_Ind.[Parent ID])))
    do you see why this works now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •