SQL insert

[operprincipal]

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!

 

[RB Smissaert]

Something along these lines:

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

RBS

 

[Ariel_Z]

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

 

[RB Smissaert]

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

 

[operprincipal]

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 ...

 

[RB Smissaert]

Try this one:

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

RBS

 

[operprincipal]

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 ...

 

[Ariel_Z]

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.

 

[RB Smissaert]

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

 

[Ariel_Z]

: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.

 

[RB Smissaert]

As a newbie think twice before correcting a senior member

RBS

 

[ayman12]

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

Ariel

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 !!!