Dropping and Inserting into a dynamic table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dropping and Inserting into a dynamic table

Author

Topic

shawnafisher
Starting Member

5 Posts

Posted - 2009-04-02 : 13:30:14

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).

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-04-03 : 02:12:00

Can you please post the sample data to understand more clearly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources