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.
Author |
Topic |
maideen
Starting Member
10 Posts |
Posted - 2013-03-07 : 09:04:51
|
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 ProcedureUSE [SpectrumDB]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[usp_pivot_by_Date_Seats]--@WeekNo NumericASBEGINSET NOCOUNT ON;insert into tbl_Days_TEMPselect * from ( select Title,Qty,WeekName from dbo.vw_RPT_Pivot_Weekly_Seat) DataTablePIVOT (SUM(Qty) FOR [weekname] IN ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])) PivotTableENDerror is :Msg 213, Level 16, State 1, Procedure usp_pivot_by_Date_Seats, Line 7Insert 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 wrongMaideen |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-07 : 09:07:41
|
you wont have weekname column after you pivot on it. so remove column from temp table and try------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
maideen
Starting Member
10 Posts |
Posted - 2013-03-07 : 18:41:10
|
[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 MVPhttp://visakhm.blogspot.com/VisakhThank you. I have tried, but same error message Any other way to store the value in table?Pls help me. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-08 : 00:16:16
|
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) DataTablePIVOT (SUM(Qty) FOR [weekname] IN ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])) PivotTableEND ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|