December 10, 2013 at 8:44 am
Hi All,
Please I need help with this. I am having an error when I try to run a report that contains this code. The error is this "the Select list for the insert statement contains more items than the insert list. The number of select values must match the number of INSERT columns. Incorrect syntax near ','. " I have tried to have E.empl_uno IN (@employee) in the code but still the error persits. Any help will be appreciated.
INSERT INTO [BO_CUSTOM].[dbo].[Property_Coorindators]
([Employee_Uno]
,[Employee_Name]
,[Offc_Code]
,[Email]
Select @employee, employee_name, @Offc, EMAIL
from BO_live3.dbo.HBM_persnl E
where E.empl_uno = @employee
December 10, 2013 at 8:52 am
eobiki10 (12/10/2013)
Hi All,Please I need help with this. I am having an error when I try to run a report that contains this code. The error is this "the Select list for the insert statement contains more items than the insert list. The number of select values must match the number of INSERT columns. Incorrect syntax near ','. " I have tried to have E.empl_uno IN (@employee) in the code but still the error persits. Any help will be appreciated.
INSERT INTO [BO_CUSTOM].[dbo].[Property_Coorindators]
([Employee_Uno]
,[Employee_Name]
,[Offc_Code]
,[Email]
Select @employee, employee_name, @Offc, EMAIL
from BO_live3.dbo.HBM_persnl E
where E.empl_uno = @employee
You are missing the closing parenthesis after the column list.
INSERT INTO [BO_CUSTOM].[dbo].[Property_Coorindators]
([Employee_Uno]
,[Employee_Name]
,[Offc_Code]
,[Email])
Select @employee, employee_name, @Offc, EMAIL
from BO_live3.dbo.HBM_persnl E
where E.empl_uno = @employee
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 10, 2013 at 8:54 am
It appears that you are missing a ) after the fields you have listed
Insert into table1 (field1, field2) select fielda, fieldb from table2
December 10, 2013 at 9:08 am
Actually my value has the closing parameter. I must have have missed it when I copied the sql to paste on this forum
December 10, 2013 at 9:31 am
eobiki10 (12/10/2013)
Actually my value has the closing parameter. I must have have missed it when I copied the sql to paste on this forum
So are you good to go then or do you still have an issue?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 10, 2013 at 9:42 am
Yes, I still have issue with error.
December 10, 2013 at 9:48 am
eobiki10 (12/10/2013)
Yes, I still have issue with error.
So what is the EXACT query you are trying and what is the error message? This is probably incredibly simple to fix but we can't what you see.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 10, 2013 at 10:19 am
The error is this "the Select list for the insert statement contains more items than the insert list. The number of select values must match the number of INSERT columns. Incorrect syntax near ','. And the query is again
INSERT INTO [BO_CUSTOM].[dbo].[Property_Coorindators]
([Employee_Uno]
,[Employee_Name]
,[Offc_Code]
,[Email])
Select @employee, employee_name, @Offc, EMAIL
from BO_live3.dbo.HBM_persnl E
where E.empl_uno = @employee
SELECT *
FROM BO_custom.dbo.property_coorindators
WHERE offc_code IN (@Offc)
December 10, 2013 at 10:25 am
The query doesn't seem to haev an error. However, there's something that intrigues me. You're using WHERE offc_code IN (@Offc) and seems that @Offc might be a comma-separated value.
If it's really a comma-separated value and you're building a string value for the query, then that might be the error, if you're not building the query on the fly, you might not get the correct value on your SELECT.
December 10, 2013 at 10:33 am
Thanks for your information but what should I do on this case?
December 10, 2013 at 12:10 pm
eobiki10 (12/10/2013)
Thanks for your information but what should I do on this case?
That depends. Do you really want a comma delimited list in a single column or do you want multiple rows in your table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 10, 2013 at 1:32 pm
I think a comma delimited list would be fine.
December 10, 2013 at 1:41 pm
eobiki10 (12/10/2013)
I think a comma delimited list would be fine.
You realize that violates 1NF right? A column of data in a row of a table should hold 1 and only 1 piece of information. A delimited list is storing a bunch of values in a single cell. This will come back to bite you badly at some point. How do go about updating something like that?
Does your datatype even have enough room to accommodate such a thing? I think we are at the point where you need to share some details. At the very least the ddl for your table and a clear understanding of what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 10, 2013 at 2:00 pm
This is exactly my problem and my sql. The report has cascading parameters and it works well for a single value. I tried to make it work for multi- select but to no avail. The error as shown is "the Select list for the insert statement contains more items than the insert list. The number of select values must match the number of INSERT columns. Incorrect syntax near ','."
IF @Action = 'U'
BEGIN
UPDATE p
Set P.Employee_uno = e.Empl_uno,
p.Employee_name = E.employee_name,
p.email = E.email
FROM BO_custom.dbo.property_coorindators P cross JOIN
BO_live3.dbo.hbm_persnl E
WHERE P.employee_uno = @ChangeEmpl AND p.offc_code = @Offc AND e.empl_uno = @employee
END
If @Action = 'I'
Begin
INSERT INTO [BO_CUSTOM].[dbo].[Property_Coorindators]
([Employee_Uno]
,[Employee_Name]
,[Offc_Code]
,[Email])
Select @employee, employee_name, @Offc, EMAIL
from BO_live3.dbo.HBM_persnl E
where E.empl_uno = @employee
End
If @Action = 'X'
Begin
Delete from [BO_CUSTOM].[dbo].[Property_Coorindators]
where offc_code = @Offc
and @ChangeEmpl = employee_uno
End
SELECT *
FROM BO_custom.dbo.property_coorindators P
WHERE offc_code = @Offc
Thanks so much
December 10, 2013 at 2:23 pm
Are you using SSRS to call tha procedure? SSRS might be changing your variables to the actual values in the query using the Join function. To correctly insert the values, you have to remove the JOIN function for your variable.
You could either use 2 variables (one for the insert with a single string and one which will return the values) or use a single variable and handle the single string with T-SQL.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply