SQL insert

Discussion in 'Questions (Windows Mobile)' started by operprincipal, Jun 12, 2009.

  1. operprincipal

    operprincipal Member Licensed User

    Hi there
    I need some help. Supose 2 SQL tables (Master and movs). Every month i need to search active members of master and insert new records in the movs table, with some fields of the master and another fileds from program variables, like date andother stuffs. How should i write
    the "insert into ... select statement"?
    Is there another way to do it?
    I'm new with SQL and i cant find the right way to write it ... Thanks for your help!
     
  2. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    Something along these lines:

    INSERT INTO
    TABLE2(FIELD1, FIELD2)
    SELECT TABLE1.FIELD1, TABLE1.FIELD2
    FROM
    TABLE1
    WHERE
    FIELD1 = 'X'

    RBS
     
  3. Ariel_Z

    Ariel_Z Active Member Licensed User

    Sql

    Hi,
    A good place to start from is SQL Tutorial.

    Anyway, you might be looking for something like

    "SELECT * FROM [t_master] WHERE isActive=1"

    or, using parameters, "SELECT * FROM [t_master] WHERE isActive=@isActive"

    then, you can read the data and save desired values into variables, later used as parameters for the INSERT statement:
    "INSERT INTO [t_master] (column_name1, col_name2...) VALUES (v1, v2)"

    Hope this helps. Say if it does not. A complete SQL reference is about to be published in a couple of days, and I think it will answer better.

    Ariel
     
  4. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    To be on the safe side make the SQL along these lines:

    INSERT INTO
    TABLE2(FIELD1, FIELD2)
    SELECT TABLE1.FIELD1, TABLE1.FIELD2
    FROM
    TABLE1
    WHERE
    TABLE1.FIELD1 = 'X'

    RBS
     
  5. operprincipal

    operprincipal Member Licensed User

    Thanks for your kind replys.
    I've tried along with your sugestions, but ... i still have a problem when
    i put variables on the inside statement


    Cmd.CommandText = "INSERT INTO Movimentos SELECT MASTER.NUMERO, MANO, MASTER.VENCIMENTO FROM MASTER"

    CMD.ExecuteNonQuery

    It says "no such column: mano" wich is a variable from the program.

    The sql tutorials that i've found doesn't help me very much with this ...
     
  6. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    Try this one:

    Cmd.CommandText = "INSERT INTO Movimentos " & _
    "SELECT MASTER.NUMERO, " & MANO & _
    ", MASTER.VENCIMENTO FROM MASTER"

    RBS
     
  7. operprincipal

    operprincipal Member Licensed User

    That works! Thanks for your help.
    By the way: the manual that Ariel refers will be great help.
    This stuff is not very dificult, but sometimes, with a little help
    (some small examples)
    the syntax becomes much clear ...
     
  8. Ariel_Z

    Ariel_Z Active Member Licensed User

    Please note a security problem with the code RB Smissaert has suggested. Although commonly in use, this kind of code may cause "SQL injection". I don't know where your input comes from, but if the user enters a string for MANO, your SQL statement becomes unsafe. Instead, you could try

    Cmd.AddParameter("pMano")
    Cmd.SetParameter("pMano", Mano)

    Cmd.CommandText = "INSERT INTO Movimentos SELECT MASTER.NUMERO, @Mano, MASTER.VENCIMENTO FROM MASTER"

    CMD.ExecuteNonQuery


    ---> note the @ sign, indicating parameter.

    This will prevent the user from entering something like
    1;DELETE FROM Movimentos
    which will delete your table.


    The tutorial covers this as well: should be up in a day or two.
     
  9. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    Unless I am mistaken I don't think you can use parameters in this situation.
    Parameters are for variable values, not for bits of the SQL.
    So what you can do is:

    select field from table where value = ?

    But you can't do:

    select ? from table where where value = 10

    RBS
     
  10. Ariel_Z

    Ariel_Z Active Member Licensed User

    :sign0161:

    You are right of course. I gave a wrong answer. Of course you don't have to use parameters at all these cases. Parameters are mainly for user-input scenarios.

    SQL tutorial is published here.
     
  11. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    As a newbie think twice before correcting a senior member:)

    RBS
     
  12. ayman12

    ayman12 Member Licensed User

    yes sure it is a very good site for SQL, but I prefare

    SQLite Download Page

    becouse Basic4PPC use SQLite which is not 100% SQL.
    for example in SQLite :-

    SELECT * FROM MyTable WHERE MyField like '[abcd]%';

    this will list ONLY records with myField that starts with '[abcd]'
    :BangHead:

    Is there any one know an alternative in SQlite !!!
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice