Insert Statement 1

[tj007]

I am trying to insert records from one table (bs_loc) to (Rt1Stps) table. All the fields are text except date1.

Here is the sql query:

INSERT INTO Rt1Stps (STOP, LOC_ID, DESCR, MUNIC, AMENITIES, ADA, DIST_IN, MAIN, CROSS, RW, HISTORY, DATE1)
VALUES ('STOP' Loc_id, DESCR, MUNIC, AMENITIES, ADA, DIST_IN, MAIN, CROSS, RW, HISTORY, DATE1);

I keep getting enter Parameter Value for each field. I would like to be prompted for the STOP field only so I can enter the string or :-(

 

[jrbarnett]

Tj007

If you want to copy records from one table to another, you have to use a slightly different INSERT syntax:

INSERT INTO Rt1Stps (STOP, LOC_ID, DESCR, MUNIC, AMENITIES, ADA, DIST_IN, MAIN, CROSS, RW, HISTORY, DATE1)
SELECT 'STOP' Loc_id, DESCR, MUNIC, AMENITIES, ADA, DIST_IN, MAIN, CROSS, RW, HISTORY, DATE1 FROM SourceTableName
WHERE <condition>;

For the Enter stop field definition, use a form or input box to enter the value and dynamically edit the Where condition as appropriate.
etc.

John

 

[SkipVought]

Hi,

You're not getting anything from bs_loc

INSERT INTO Rt1Stps (STOP, LOC_ID, DESCR, MUNIC, AMENITIES, ADA, DIST_IN, MAIN, CROSS, RW, HISTORY, DATE1)
VALUES ([Enter STOP] (Select Loc_id, DESCR, MUNIC, AMENITIES, ADA, DIST_IN, MAIN, CROSS, RW, HISTORY, DATE1 from bs_loc Where ????));

but...
you need to select only ONE ROW -- is it based on the value of STOP?

(Select STOP, Loc_id, DESCR, MUNIC, AMENITIES, ADA, DIST_IN, MAIN, CROSS, RW, HISTORY, DATE1 from bs_loc Where STOP='[Enter STOP]')

????

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tj007]

The value is based on the bs_loc.loc_id field. All the other data in the row based on that field is what I would like to move to the Rt1Stps table.

 

[SkipVought]

How do you propose that the value of bs_loc.loc_id field be determined in order to retrieve one row?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tj007]

The new record is created in bs_loc table based on an autonumber and the other data is added to that row.

 

[tj007]

The Rt1Stps table will be renumbered

UPDATE Rt1Stps SET STOP = Format(Val([STOP])+1,"00000")
WHERE (Val([STOP]) Mod 1000)>25;

 

[tj007]

Just thought I would post back after reading myself sql literate this worked perfectly

INSERT INTO Rt1Stps ( STOP, LOC_ID, DESCR, MUNIC, AMENITIES, ADA, DIST_INT, MAIN, [CROSS], RW, HISTORY, DATE1 )
SELECT bs_loc.Stop, bs_loc.LOC_ID, bs_loc.DESCR, bs_loc.MUNIC, bs_loc.AMENITIES, bs_loc.ADA, bs_loc.DIST_INT, bs_loc.MAIN, bs_loc.CROSS, bs_loc.RW, bs_loc.HISTORY, bs_loc.DATE1
FROM BS_LOC
WHERE BS_LOC.LOC_ID=[Please Enter Location ID];

Can someone help me with prompting the user to enter a number in this query: I do not want to hard code the 25. I would like to mae it dynamic.

UPDATE Rt1Stps SET STOP = Format(Val([STOP])+1,"00000")
WHERE (Val([STOP]) Mod 1000)>25;

 

[Golom]

Try
[blue][tt]
PARAMETERS [Enter The Remainder] Integer;
UPDATE Rt1Stps SET STOP = Format(Val([STOP])+1,"00000")
WHERE (Val([STOP]) Mod 1000) > [Enter The Remainder];
[/tt][/blue]

 

[tj007]

Okay, thanks for your input.

 

[tj007]

Its perfect. I would have never figured this out and it was the most important part of the automation. I just tested it on three different scenarios. All passed without error.