Multiple insert into access table 1

[Nixx123]

Hi
Please can i get a pointer on the syntax for JET sql
FOR ms access 2007 , i would like to run a script where there are multiple value inserts to various columns in the same Table

The script currently is as follows:
__________________________________

INSERT INTO [tblPcw]
(PCW, PCW DESCRIPTION,PCW Cat)
VALUES ('20252','BONDS BANKING BOOK',' ');
___________________________________________________________
The ' ' quotation marks at the end of the statement represent an empty string value

Any assistance on this will be greatly appreciated
Kind regards

 

[PHV]

INSERT INTO tblPcw
(PCW, [!][[/!]PCW DESCRIPTION[!]][/!],[!][[/!]PCW Cat[!]][/!])
VALUES ('20252','BONDS BANKING BOOK',' ');

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Nixx123]

Thanks for the advise
The script sample i posted is only inserting 3 values into a single row
My next question is this
I need to insert values into a min of 5 rows
How can i change the syntax to accomplish this with one script ?

Kind regards

 

[PHV]

You can't with JetSQL.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MaxieMax]

If the values you are wanting to insert are from the result of a query or held within another table you could open the result and a record set and then loop through the recordset and perform the insert for each row of the record set.... so for example if one time you get 5 rows of data it will loop through 5 times inserting the data each time, but if there are 5000 rows the loop will run 5000 times to insert each line of data.

 

[Nixx123]

The values are present in a table in access
TblPcw the thing is i need to create the same table on a pervasive database which i have done , now my challenge is to get the values from the Table in access to the newly created table on Pervasive ,ultimately having a linked table

You must please forgive me i am not that familiar with loops
so guidance , and educating will be most appreciated

 

[PWise]

If the values are the result of a table or query
use

INSERT INTO tblPcw(PCW, [PCW DESCRIPTION],[PCW Cat])
Select Field1,Field2,'' As field3
From Tablenme
where  Field1=xxxx

 

[Nixx123]

By Field1,Field2, ' '
You are referring to the columns i assume

Please can you also advise why you are using an alias
in the syntax

 

[PHV]

Something like this ?

INSERT INTO [i]linkedPervasiveTable[/i](PCW,[PCW DESCRIPTION],[PCW Cat])
SELECT PCW,[PCW DESCRIPTION],[PCW Cat]
FROM [i]localAccessTable[/i]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

The reson i amuseing an alias is because I dont i assumed that you dont have this value in yur local access table if you do indeed have it you dont need an alias

 

[Nixx123]

Thank you everybody for the valuable info
i will definitely give it a go and advise the outcome