"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:OxKXIZrwGHA.4460@TK2MSFTNGP04.phx.gbl...
> Just remove the line containing the keyword VALUES.  That's for single-row
> INSERTs:
>
> INSERT INTO tblquotation_order
>
> (Order_Number, LisaNo, Organisation_Name,Order_Date, Order_Value,
> Createdby)
>
> SELECT OrderNumber AS Order_Number, 'LIZ' + RIGHT(ProjectNumber,
>
> LEN(ProjectNumber) - 3) AS LisaNo,
>
> NameOfSoldToParty AS organisation_Name, PODate AS order_date, NetValue AS
> order_value, 'System' AS CreatedBy
>
> FROM tblSAPOrders
>
> WHERE OrderNumber NOT IN
>
> (SELECT tblquotation_order.order_number
>
> FROM
>
> tblquotation_Order)
>
> I've also deleted some unnecessary parentheses.
>
> --
>   Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON   Canada
> .
> "John" <topgu***@hotmail.com> wrote in message
> news:44e5811c$0$1391$db0fefd9@news.zen.co.uk...
>
>
> Can i do this :
>
> INSERT INTO tblquotation_order
>
> (Order_Number, LisaNo, Organisation_Name,Order_Date, Order_Value,
> Createdby)
> VALUES
>
> (SELECT OrderNumber AS Order_Number, 'LIZ' + RIGHT(ProjectNumber,
>
> LEN(ProjectNumber) - 3) AS LisaNo,
>
> NameOfSoldToParty AS organisation_Name, PODate AS order_date, NetValue AS
> order_value, 'System' AS CreatedBy
>
> FROM tblSAPOrders
>
> WHERE (OrderNumber NOT IN
>
> (SELECT tblquotation_order.order_number
>
> FROM
>
> tblquotation_Order)))
>
> Or will i need to use a temp table to store the Select query first before
> runnin the insert statment ?
>
> Thanks in advance
>
> John
>
>