Click here to Skip to main content
14,924,665 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
I'm trying to copy from table to another with condition and join and when I created it I get an error.

INSERT INTO [ax].[WaitingKitchenStatus]
(
    [TRANSACTIONID]--1
    , [LineNote]--2
    , [Item]--3
    , [CreationDate]--4
    , [CreationTime]--5
    , [active]--6
    ,[Gruop]--7
)

SELECT ab.COMMENT
	,[ax].[ECORESPRODUCTTRANSLATION].[NAME]
    , ab.[ITEMID]
FROM ax.RetailTransactionSalesTrans ab
inner join [ax].[INVENTTABLE] on ab.[ITEMID] =[ax].[INVENTTABLE].[ITEMID]
inner join [ax].[ECORESPRODUCTTRANSLATION] on [ax].[INVENTTABLE].PRODUCT =[ax].[ECORESPRODUCTTRANSLATION].[PRODUCT]
LEFT JOIN ax.[WaitingKitchenStatus] a ON 
a.[TRANSACTIONID]=ab.COMMENT--1
and a.[LineNote]=[ax].[ECORESPRODUCTTRANSLATION].[NAME]--2
and a.[Item]=ab.[ITEMID]--3
and a.[CreationDate]=GETDATE()--4
and a.[CreationTime]= CONVERT(time, GETDATE())--5
and a.active= 1--6
and a.Gruop=''--7
where ab.TRANSACTIONSTATUS !=1
and ab.RECEIPTID !=''
and ab.COMMENT !=''
and NOT EXISTS(select * from ax.[WaitingKitchenStatus] where active= 1 and [TRANSACTIONID]=ab.COMMENT)


I got this error

Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.


What I have tried:

i'v checked from column number yes i copy only 3 from table but other column not copy from that table and even i already insert them
Posted
Updated 1-Mar-20 3:38am
Comments
[no name] 1-Mar-20 9:26am
   
What magic you expect from SQL? You stated that you insert 7 fields but you select only 3. Ask yourself how SQL should solve this, what values should be inserted for the remaining 4 fields...

1 solution

Your INSERT lists 7 fields:
SQL
INSERT INTO [ax].[WaitingKitchenStatus]
(
    [TRANSACTIONID]--1
    , [LineNote]--2
    , [Item]--3
    , [CreationDate]--4
    , [CreationTime]--5
    , [active]--6
    ,[Gruop]--7
)

But the actual SELECT that provides the information only supplies 3:
SQL
SELECT ab.COMMENT
	,[ax].[ECORESPRODUCTTRANSLATION].[NAME]
    , ab.[ITEMID]
FROM ax.RetailTransactionSalesTrans ab
The system won't "invent" data. Either supply 4 NULL values as well, or only list the columns you do provide data for. (I'm guessing you are only providing the LineNote and the Item, plus a Name that has no obvious column to store it in, so you might want to think about your data a little more carefully.)
   
Comments
mohammed mqi 1-Mar-20 10:02am
   
so how can i add other column which no need to copy from that table
OriginalGriff 1-Mar-20 10:08am
   
Sorry?
You're going to have to explain in more detail - remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
mohammed mqi 1-Mar-20 10:12am
   
i mean if you see after statement left
you will see i assign value to the rest columns

and a.[CreationDate]=GETDATE()--4
and a.[CreationTime]= CONVERT(time, GETDATE())--5
and a.active= 1--6
and a.Gruop=''--7

or this not work?!
[no name] 1-Mar-20 10:15am
   
E.g. add this 4 fields into your Select
OriginalGriff 1-Mar-20 10:20am
   
No - those are comparisons. THe INSERT only adds columns that are listed in the SELECT list before the first FROM clause. If you want that data, then try something like:

SELECT ab.COMMENT
,[ax].[ECORESPRODUCTTRANSLATION].[NAME]
, ab.[ITEMID]
, GETDATE()
, CONVERT(time, GETDATE())
, 1
, ''
FROM ax.RetailTransactionSalesTrans ab
...

Or add default values to your Table Column definitions, and don't list them in the INSERT list.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900