Query

Thanks for your help with query for some reason the data is in the database but when I try to go preview the data from the server explorer it shows null.
So I decided to just pull everything from memory which works very well for me instead of trying to execute the query from access. For some reasons thats the only query that dont work for me. The rest of the queries im calling from access works fine. Thanks for your help krishna

Im getting a syntax error in access, it says a create syntax error


CREATE PROCEDURE proc_TrunandInser
AS
BEGIN
       truncate table Example
       INSERT INTO Example(OpTimeStamp,UutMSN,maxoftestid,maxoftestnames, Status) select   OpTimeStamp,UutMSN,maxoftestid,maxoftestnames,Status       
        FROM CIF_TestErrors_Crosstab

END

Im getting a syntax error in access, it says a create syntax error


CREATE PROCEDURE proc_TrunandInser
AS
BEGIN
       truncate table Example
       INSERT INTO Example(OpTimeStamp,UutMSN,maxoftestid,maxoftestnames, Status) select   OpTimeStamp,UutMSN,maxoftestid,maxoftestnames,Status       
        FROM CIF_TestErrors_Crosstab

END

Hi are you saying post this query in the access database in the sql view, access doesnt use use stored procedures, sql does, unless you want to call it the stored procedure a query in access?

Hai darnell

Truncate and insert are two different sql statements.If u want both to happen simultaneously then u have to include it inside a stored procedure and execute the procedure whenever u want  the actions to be done....

Here is the procedure

CREATE PROCEDURE proc_TrunandInser
AS
BEGIN
       truncate table Example
       INSERT INTO Example(OpTimeStamp,UutMSN,maxoftestid,maxoftestnames, Status) select   OpTimeStamp,UutMSN,maxoftestid,maxoftestnames,Status        FROM CIF_TestErrors_Crosstab

END

First Execute the procedure  in the database which u are using....


Now u can execute this procedure using the sql statement exec proc_TrunandInser


Hope i made myself clear

With Regards
P.Krishna Prasad

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If Someway my answer helped u... Don't forget to mark it as correct answer....

Thanks this is the query I use earlier and it works,

I do have a question when I add the truncate table Example, on top of the insert I get a syntax error, how do i include the truncate table as part of the query

truncate table Example
INSERT INTO Example(OpTimeStamp,UutMSN,maxoftestid,maxoftestnames, Status) select OpTimeStamp,UutMSN,maxoftestid,maxoftestnames, Status FROM CIF_TestErrors_Crosstab

Hai darnell

AS per your req..

To always delete the content in table retaining it's structure use truncate table tbl2
To insert value from table A to table B use insert into tbl2(colA,colB,colC) select colA,colB,colC from tbl1

If u want both to happen  write it as a stored procedure....

Hope i made myself clear

With Regards
P.Krishna Prasad

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If Someway my answer helped u... Don't forget to mark it as correct answer....