SQL insert

O

operprincipal

Member
Licensed User
Longtime 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!
 
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Something along these lines:

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

RBS
 
A

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
 
R

RB Smissaert

Well-Known Member
Licensed User
Longtime 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
 
O

operprincipal

Member
Licensed User
Longtime 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 ...
 
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Try this one:

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

RBS
 
O

operprincipal

Member
Licensed User
Longtime 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 ...
 
A

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

RB Smissaert

Well-Known Member
Licensed User
Longtime 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
 
A

ayman12

Member
Licensed User
Ariel_Z said:
Hi,
A good place to start from is SQL Tutorial.

Ariel
Click to expand...

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 !!!
 
You must log in or register to reply here.