How do I insert records using foreign keys in SQLite

nikel · June 29, 2018, 1:44pm

Hello, I'm trying to add some records to my table using SQLite. Here's my code:

INSERT INTO "tbl_sheet" (
    P.txt_press, 
    CA.txt_category, 
    S.txt_repertory_no, 
    T.txt_taken_from, 
    S.txt_revision_date1, 
    S.txt_revision_date2, 
    S.txt_revision_date3, 
    R.txt_region, 
    PR.txt_prepared_by, 
    S.txt_research_date1, 
    S.txt_research_date2, 
    S.txt_research_date3, 
    SP.txt_source_by, 
    M.txt_measure, 
    S.txt_time, 
    C.txt_compiled_by, 
    S.txt_compilation_date, 
    N.txt_notation_by, 
    S.txt_mp3_paths, 
    A.txt_artist, 
    S.txt_song_name, 
    S.txt_lyrics, 
    S.image1, S.image2, S.image3, S.image4, S.image5, S.image6, S.image7, 
    S.image8, S.image9, S.image10, S.image11, S.image12, S.image13, S.image14
) 
SELECT (
    P.id, 
    CA.id, 
    "3", 
    T.id, 
    "1980", 
    null, 
    null, 
    R.id, 
    PR.id, 
    null, 
    null, 
    null, 
    SP.id, 
    M.id, 
    "120", 
    C.id, 
    "1980", 
    N.id, 
    "C:\test.mp3?C:\test2.mp3", 
    A.id, 
    "Test Song", 
    "Line1\nLine2\nLine3", 
    null, null, null, null, null, null, null, 
    null, null, null, null, null, null, null
)
FROM "tbl_sheet"  
LEFT JOIN tbl_category CA 
    ON CA.id = S.int_category_id 
LEFT JOIN tbl_press P 
    ON P.id = S.int_press_id 
LEFT JOIN tbl_taken_from T 
    ON T.id = S.int_taken_from_id 
LEFT JOIN tbl_prepared_by PR 
    ON PR.id = S.int_prepared_by_id 
LEFT JOIN tbl_region R 
    ON R.id = S.int_region_id 
LEFT JOIN tbl_source_by SP 
    ON SP.id = S.int_source_by_id 
LEFT JOIN tbl_measure M 
    ON M.id = S.int_measure_id 
LEFT JOIN tbl_compiled_by C 
    ON C.id = S.int_compiled_by_id 
LEFT JOIN tbl_notation_by N 
    ON N.id = S.int_notation_by_id 
LEFT JOIN tbl_artist A 
    ON A.id = S.int_artist_id 
WHERE ( 
        P.txt_press = "TRT Müzik Dairesi Yayınları" 
    AND 
        CA.txt_category = "Genel" 
    AND 
        T.txt_taken_from = "Selim Kasap" 
    AND 
        R.txt_region = "Şarkışla / Sivas" 
    AND 
        PR.txt_prepared_by = "<Bilinmiyor>" 
    AND 
        SP.txt_source_by = "Âşık Ali İzzet Özkan" 
    AND 
        M.txt_measure = "Sekizlik" 
    AND 
        C.txt_compiled_by = "Nida Tüfekçi" 
    AND 
        N.txt_notation_by = "Nida Tüfekçi" 
    AND 
        A.txt_artist = "Zara"
)

I'm getting error: near ".": syntax error

stepson · June 29, 2018, 4:12pm

1.Welcome to Microsoft Sql Server forum!
2. List of column, in the Insert should not have alias
ex:

INSERT INTO "tbl_sheet" (
    txt_press, 
    txt_category, 
    txt_repertory_no, 
    txt_taken_from, 
    txt_revision_date1, 
    txt_revision_date2,

Select statement has ( and ) in your query .Line 27 and 52. This should also not be present. Probably , the same for Where clause, line 74 and 94

nikel · June 29, 2018, 6:21pm

Hi, I changed my code regarding your reply. I also removed null values. Here's my code so far:

INSERT INTO "tbl_sheet" (
    int_press_id, 
    int_category_id, 
    txt_repertory_no, 
    int_taken_from_id, 
    txt_revision_date1, 
    txt_revision_date2, 
    txt_revision_date3, 
    int_region_id, 
    int_prepared_by_id, 
    txt_research_date1, 
    txt_research_date2, 
    txt_research_date3, 
    int_source_by_id, 
    int_measure_id, 
    txt_time, 
    int_compiled_by_id, 
    txt_compilation_date, 
    int_notation_by_id, 
    txt_mp3_paths, 
    int_artist_id, 
    txt_song_name, 
    txt_lyrics, 
    image1, image2, image3, image4, image5, image6, image7, 
    image8, image9, image10, image11, image12, image13, image14) 
SELECT 
    P.id, 
    CA.id, 
    "3", 
    T.id, 
    "1980", 
    "test", 
    "test", 
    R.id, 
    PR.id, 
    "test", 
    "test", 
    "test", 
    SP.id, 
    M.id, 
    "120", 
    C.id, 
    "1980", 
    N.id, 
    "C:\test.mp3?C:\test2.mp3", 
    A.id, 
    "Test Song", 
    "Line1\nLine2\nLine3", 
    "<I inserted some blob string here>", "here", "here", "here", "here", "here", "here",
     "here", "here", "here", "here", "here", "here", "here" 
FROM "tbl_sheet" 
LEFT JOIN tbl_category CA 
    ON CA.id = int_category_id 
LEFT JOIN tbl_press P 
    ON P.id = int_press_id 
LEFT JOIN tbl_taken_from T 
    ON T.id = int_taken_from_id 
LEFT JOIN tbl_prepared_by PR 
    ON PR.id = int_prepared_by_id 
LEFT JOIN tbl_region R 
    ON R.id = int_region_id 
LEFT JOIN tbl_source_by SP 
    ON SP.id = int_source_by_id 
LEFT JOIN tbl_measure M 
    ON M.id = int_measure_id 
LEFT JOIN tbl_compiled_by C 
    ON C.id = int_compiled_by_id 
LEFT JOIN tbl_notation_by N 
    ON N.id = int_notation_by_id 
LEFT JOIN tbl_artist A 
    ON A.id = int_artist_id 
WHERE 
        P.txt_press = "TRT Müzik Dairesi Yayınları" 
    AND 
        CA.txt_category = "Genel" 
    AND 
        T.txt_taken_from = "Selim Kasap" 
    AND 
        R.txt_region = "Şarkışla / Sivas" 
    AND 
        PR.txt_prepared_by = "<Bilinmiyor>" 
    AND 
        SP.txt_source_by = "Âşık Ali İzzet Özkan" 
    AND 
        M.txt_measure = "Sekizlik" 
    AND 
        C.txt_compiled_by = "Nida Tüfekçi" 
    AND 
        N.txt_notation_by = "Nida Tüfekçi" 
    AND 
        A.txt_artist = "Zara"

I'm not getting any errors but the rows aren't affectied.

stepson · June 30, 2018, 4:03am

First of all, remove the insert , and run only the select, to see what is the output

nikel · June 30, 2018, 11:52am

woaw you guided me in this complexity just with 17 word? Thanks a lot.

Here's my final code:

INSERT INTO 'tbl_sheet' (
    int_press_id, 
    int_category_id, 
    txt_repertory_no, 
    int_taken_from_id, 
    txt_revision_date1, 
    txt_revision_date2, 
    txt_revision_date3, 
    int_region_id, 
    int_prepared_by_id, 
    txt_research_date1, 
    txt_research_date2, 
    txt_research_date3, 
    int_source_by_id, 
    int_measure_id, 
    txt_time, 
    int_compiled_by_id, 
    txt_compilation_date, 
    int_notation_by_id, 
    txt_mp3_paths, 
    int_artist_id, 
    txt_song_name, 
    txt_lyrics, 
    image1, image2, image3, image4, image5, image6, image7, 
    image8, image9, image10, image11, image12, image13, image14 
) 
SELECT 
    P.id, 
    CA.id, 
    '3', 
    T.id, 
    '1990', 
    '1991', 
    '', 
    R.id,
    PB.id, 
    '', 
    '', 
    '', 
    SB.id, 
    M.id, 
    '120', 
    C.id, 
    '1995', 
    N.id, 
    'C:\musuc4mp3?C:\music5mp3', 
    A.id, 
    'Test Song 3', 
    'Test line 1', 
    '', '', '', '', '', '', '', 
    '', '', '', '', '', '', '' 
FROM 'tbl_sheet' S 
LEFT JOIN 'tbl_press' P 
    ON P.id = S.int_press_id 
LEFT JOIN 'tbl_category' CA 
    ON CA.id = S.int_category_id 
LEFT JOIN 'tbl_taken_from' T 
    ON T.id = S.int_taken_from_id 
LEFT JOIN 'tbl_region' R 
    ON R.id = S.int_region_id 
LEFT JOIN 'tbl_prepared_by' PB 
    ON PB.id = S.int_prepared_by_id 
LEFT JOIN 'tbl_source_by' SB 
    ON SB.id = S.int_source_by_id 
LEFT JOIN 'tbl_measure' M 
    ON M.id = S.int_measure_id 
LEFT JOIN 'tbl_compiled_by' C 
    ON C.id = S.int_compiled_by_id 
LEFT JOIN 'tbl_notation_by' N 
    ON N.id = S.int_notation_by_id 
LEFT JOIN 'tbl_artist' A 
    ON A.id = S.int_artist_id 
WHERE 
        P.txt_press = 'TRT Müzik Dairesi Yayınları' 
    AND 
        CA.txt_category = 'Genel' 
    AND 
        T.txt_taken_from = '<Bilinmiyor>' 
    AND 
        R.txt_region = 'Barla / Isparta' 
    AND 
        PB.txt_prepared_by = '<Bilinmiyor>' 
    AND 
        SB.txt_source_by = 'Yalçın Özsoy' 
    AND 
        M.txt_measure = 'Sekizlik' 
    AND 
        C.txt_compiled_by = 'Nida Tüfekçi' 
    AND 
        N.txt_notation_by = 'Nida Tüfekçi' 
    AND 
        A.txt_artist = 'Zara'

Thanks again

stepson · June 30, 2018, 12:19pm

Super! Glad to help!