SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Insert data to table using store procedure pivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maideen
Starting Member

Malaysia
10 Posts

Posted - 03/07/2013 :  09:04:51  Show Profile  Reply with Quote
Hi i need to insert data to table using store procedure with pivot command. Where execute store procedure without insert command, it is ok and show the data. It is my store Procedure

USE [SpectrumDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pivot_by_Date_Seats]
--@WeekNo Numeric
AS
BEGIN
SET NOCOUNT ON;

insert into tbl_Days_TEMP

select * from ( select Title,Qty,WeekName from dbo.vw_RPT_Pivot_Weekly_Seat) DataTable
PIVOT (SUM(Qty) FOR [weekname] IN ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])) PivotTable
END

error is :

Msg 213, Level 16, State 1, Procedure usp_pivot_by_Date_Seats, Line 7
Insert Error: Column name or number of supplied values
does not match table definition.

My table is

USE [SpectrumDB]
GO
/****** Object: Table [dbo].[tbl_Days_TEMP] Script Date: 03/07/2013 22:04:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Days_TEMP](
[Title] [nvarchar](500) NULL,
[WeekName] [nvarchar](50) NULL,
[Value1] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value1] DEFAULT ((0)),
[Value2] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value2] DEFAULT ((0)),
[Value3] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value3] DEFAULT ((0)),
[Value4] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value4] DEFAULT ((0)),
[Value5] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value5] DEFAULT ((0)),
[Value6] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value6] DEFAULT ((0)),
[Value7] [numeric](18, 0) NULL CONSTRAINT [DF_tbl_Days_TEMP_Value7] DEFAULT ((0)),
[Qty] [numeric](18, 0) NULL
) ON [PRIMARY]

Pls help me where i did wrong

Maideen

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 03/07/2013 :  09:07:41  Show Profile  Reply with Quote
you wont have weekname column after you pivot on it. so remove column from temp table and try

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

maideen
Starting Member

Malaysia
10 Posts

Posted - 03/07/2013 :  18:41:10  Show Profile  Reply with Quote
[quote]Originally posted by visakh16

you wont have weekname column after you pivot on it. so remove column from temp table and try

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


Visakh

Thank you. I have tried, but same error message

Any other way to store the value in table?

Pls help me.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 03/08/2013 :  00:16:16  Show Profile  Reply with Quote
try like this and it wont complain


....
insert into tbl_Days_TEMP (
[Title],
[Value1] ,
[Value2] ,
[Value3] ,
[Value4] ,
[Value5] ,
[Value6] ,
[Value7] 
)
select Title,
[Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday]
from ( select Title,Qty,WeekName from dbo.vw_RPT_Pivot_Weekly_Seat) DataTable
PIVOT (SUM(Qty) FOR [weekname] IN ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])) PivotTable
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000