April 2, 2009 at 11:47 am
Hi All, I am new to this forum, and wanted to first say hello! My problem: I am trying to create a stored procedure that I will call from vb.net that will drop and re-create a single table that has different columns each time the proc is ran (and I will be running it 100's of times each time the report is generated, as the vb.net creates one excel file per Supervisor), and I am getting this error:
"Insert Error: Column name or number of supplied values does not match table definition."
and the reason I am getting it is because each time I call the stored procedure, I am passing a SupervisorID to rebuild the table for the employees that report to them.
So, I wrote a cursor that uses a table with this layout:
Select
StoreDept,
Question,
RepName,
Answer,
From tTable
That creates a table with this layout (RepName
represents different number of columns and the column name)
Drop table tNewTable
Insert tNewTable
Select
StoreDept,
Question,
RepName1,
RepName2,
RepName3,
From tNewTable (in the RepName1 column is the answer etc)
I know why I am getting the error, but I was hoping someone might have a better solution for this problem. Any help would be much appreciated.
Also, I am running SQL 2005, set to be 2000 compatible, so the PIVOT operator is not a viable solution (I tried it).
April 2, 2009 at 1:58 pm
It seems to me that if you have multiple columns depending on the number of employees reporting to the supervisor, then if you have column 'Question' along side them, you must necessarily have the same value of Question for each employee. Is this the case?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
April 2, 2009 at 2:11 pm
Greg Snidow (4/2/2009)
It seems to me that if you have multiple columns depending on the number of employees reporting to the supervisor, then if you have column 'Question' along side them, you must necessarily have the same value of Question for each employee. Is this the case?
Hi Greg,
Yes, the question column contains the questiontext (How many hours did you work today?) and the rep columns hold the answer. Rep1 (8) etc.
April 3, 2009 at 12:47 pm
sfisher, I have seen this type of question asked many times, and one of the usual responses is 'do it in the app'. I tend to agree with that answer, or with the fact that the better approach would be to have a non-pivoted report. However, the good folks here always provide an answer to the question, but usually coding this type of thing is over my head, so I wanted to try to do it myself. With that being said, there are security risks involved with dynamic SQL, but I am assuming you are aware of that, and I had to make some assumptions about your data. This will do what I think you are asking for, and if not, it should give you some ideas.
/*=================================================================
Set up test table. I am assuming each employee has a SupervisorID
in the table tTable. If this is not the case, you could change this
to work with a view having your needed columns. You
=================================================================*/
--You will need to change DB name to your test environment, and or
--change the table name to something other than your real table
IF OBJECT_ID('ZTEST..tTable','u') IS NOT NULL
DROP TABLE tTable
CREATE TABLE tTable
(
SupervisorID INT,
StoreDept INT,
Question VARCHAR(20),
RepName VARCHAR(20),
Answer INT
)
--I am making some assumptions about your data, since there was
--not a lot to go on, but I think you will get the idea
INSERT INTO tTable
SELECT 1,1,'NumHrs','Rep1',8 UNION ALL
SELECT 1,1,'NumHrs','Rep2',8 UNION ALL
SELECT 1,1,'NumHrs','Rep3',4 UNION ALL
SELECT 2,1,'NumHrs','Rep4',8 UNION ALL
SELECT 2,1,'NumHrs','Rep5',4
--Create the procedure
CREATE PROCEDURE sptTable @SupervisorID VARCHAR(10)
AS
--Declare the local variables to build the final SELECT statement
DECLARE @SQL1 VARCHAR(4000)
DECLARE @SQL2 VARCHAR(4000)
DECLARE @SQL3 VARCHAR(4000)
DECLARE @SQL4 VARCHAR(4000)
DECLARE @Comma CHAR(1)
DECLARE @RepList VARCHAR(4000)
DECLARE @SELECT VARCHAR(4000)
DECLARE @WHERE VARCHAR(4000)
DECLARE @EmpCount INT
DECLARE @MaxEmpCount INT
DECLARE @RepName VARCHAR(20)
DECLARE @Table TABLE
(
RepName VARCHAR(20),
Answer VARCHAR(20),
ID INT IDENTITY(1,1)
)
--Insert your rep's data into table variable
INSERT INTO @Table (RepName,Answer)
SELECT
RepName,
Answer
FROM tTable
WHERE SupervisorID = @SupervisorID
SET NOCOUNT ON
--Get the total number of reps for the given SupervisorID
SET @MaxEmpCount = (SELECT MAX(ID) FROM @Table)
--Start the count at 1
SET @EmpCount = 1
--Start building the SELECT statement
SET @SQL1 = 'SELECT StoreDept, Question '
--Set up the various parts of the rep columns, for ease of building the statement
SET @Comma = ','
SET @SQL2 = ' = SUM(CASE WHEN RepName = '''
SET @SQL3 = ''' THEN Answer ELSE NULL END)'
SET @RepList = ''
--Use the loop to build the rep's columns
WHILE @EmpCount < = @MaxEmpCount
BEGIN
SELECT @RepName = (SELECT RepName FROM @Table
WHERE ID = @EmpCount)
SELECT @RepList = @RepList + @Comma + @RepName + @SQL2 + @RepName + @SQL3
SET @EmpCount = @EmpCount + 1
IF @EmpCount < @MaxEmpCount
CONTINUE
END
--Build the last part of SELECT statement
SET @SQL4 = '
FROM tTable '
--Put the parts together
SET @SELECT = @SQL1 + @RepList + @SQL4
--Add the WHERE and GROUP BY for the CROSSTAB
SET @WHERE = ' WHERE SupervisorID = ''' + @SupervisorID + ''' GROUP BY StoreDept,Question'
--Put it all together
IF @SELECT IS NOT NULL
BEGIN
SELECT @SELECT = @SELECT + @WHERE
END
--Let er rip! Good Luck!
EXEC (@SELECT)
--See the results
--EXEC sptTable '1'
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
April 6, 2009 at 12:25 pm
Thank you Greg! I will give this a try.
April 7, 2009 at 2:22 pm
shawna.fisher (4/6/2009)
Thank you Greg! I will give this a try.
Shawna, you might want to look at this post, before you think about using my solution. http://www.sqlservercentral.com/Forums/Topic678702-8-1.aspx. Although to me it looks ok, I am sure there is someone out there who could figure out a way to do damage with my code. Just a thought.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
April 7, 2009 at 2:37 pm
As predicted, I'm going to suggest doing this kind of pivot operation in the application, not in the database. It's faster, easier, more flexible, and more secure.
If you absolutely must do it in the database, there's a very good article on how to do it here[/url].
It's going to be dynamic SQL if you do it in the database, and that has pretty significant security issues.
Reporting Services, Excel, Crystal Reports, etc., all do better pivoting than SQL does.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply