Fill a dynamic table

Hanen

Hello,

I have a dynamic table with id_client and periods, every month a column period related to the month+year is created in my table, I must fill this table with the id client and the amount per period, I've find how to creat the dynamic table but not how to insert records in my table, how can I do that

I'm working with a stored procedure.

IdClient	200901	200902	200903	200904
C001	35 €	42 €	19 €	22 €

Please any one can help me :thumbsup:
Thanks for your answers

Koshe

I'm working with a stored procedure.

- do you mean you want to fill this table from SQL? could you paste then SQL for creating your table then?

Hanen

I mean that I'm using SQL Server 2005 to create and insert into my table.

matttrax

And this table has to be accessed through the Navision client? If so that's a bad way to do it.

Hanen

Yes it will be accessed from navision, it's the only way I think because I'm going to use it in my report to be exported to MS Excel for statistics.

matttrax

And you can't create the table in Navision with the appropriate fields, and then fill it in from SQL?

This should also be moved to the SQL forum.

Hanen

I can't do it in vanision it's a dynamic table it means that my fields depends with the periods...........

Malajlo

I think you are designing in wrong way.
If it is one-time generation, then it is ok to add fields in table. But if you want to use data for reports, then think to extend Key with additional column - Period.

WTF?!?!? :-k :bug: ?

Name	DataType	Subtype	Length
TableRef	Record	Field	
TableRefLast	Record	Field	
TableRefLast.setrange(TableRefLast.TableNo,50000) ;

TableRefLast.findfirst ;
tableref := tablereflast ;
TableRef."No." := 2 ;
TableRef.FieldName := 'Drugi' ;
tableRef.insert ;

Hanen

I want to have an Excel sheet with column for CustomerId, periods from 200905 until 200909 and each month a column is added like 200910 at the end of the month filled with the amoujnt of each period per customer.

I'm using stored procedure, to create my dynamic table to be exported, everything is perfect but only the insert into didn't work, I can't fill it

Koshe

I can't do it in vanision it's a dynamic table it means that my fields depends with the periods...........

you can do it in navision. the key would be customer + period.
but if you're doing it in SQL it's your choice.
could you paste your stored procedure which inserts the records? Then it would be much easier to help you

Hanen

Thanks I did it this my procedure statement:

	DECLARE @weeks_ordered TABLE (num VARCHAR(7)) 
	DECLARE @weeks TABLE (wk VARCHAR(10)) 
	--DECLARE @DATE DATETIME 
	DECLARE @cols NVARCHAR(3000) 
	DECLARE @INT VARCHAR(1000) 
	DECLARE @col_pv VARCHAR(2000) 
	DECLARE @query VARCHAR(3000) 

        DECLARE @debut as smalldatetime
        DECLARE @fin   as smalldatetime

SET @debut='01/01/2009'
SET @fin='01/12/2009'

	/*****************Periods************************/
	  
	SET @INT = '200901'
	--SET @DATE = GETDATE() 
	  
	WHILE @INT <= cast(CONVERT(char(6),@fin, 112)as NVARCHAR(100))
	BEGIN 
	    SET @INT = cast(CONVERT(char(6),@debut, 112)as NVARCHAR(100))
		INSERT INTO @weeks VALUES (@INT) 
	   	set @debut=DATEADD(month,1,@debut)  
	END 

/*********************************************************/

	SELECT @col_pv = STUFF(( SELECT   
	                                '],[p'  + p.wk
	                        FROM  @weeks AS p 
	                        FOR XML PATH('') 
	                       ), 1, 12, '') + ']'  
   SELECT @cols = STUFF(( SELECT   
	                                '],[p'  + p.wk
	                        FROM  @weeks AS p 
	                        FOR XML PATH('') 
	                       ), 1, 12, '') + ']'
	
IF OBJECT_ID('tempdb.dbo.#detail') IS not null 
	        DROP TABLE #detail 
	  
	CREATE TABLE #detail 
	( 
	Client VARCHAR(20) 
	,Nom VARCHAR(50)
	,"Id Commercial" VARCHAR(100)
	,Agence Varchar (100) 
	,Montant DECIMAL 
	,Period varchar(100)
	) 
	  
  
	INSERT INTO #detail 
	SELECT 
		 hdr."Sell-to Customer No_"  Client
		,hdr."Bill-to Name" Nom
		,cast( commercial."Name" as varchar(100))  As "Id Commercial"
		,'Agence'=CASE
				WHEN 
				commercial.NomCommercialDistrib='' and commercial.NTiersCommercialDistrib=''
				THEN commercial.NomRegroupement
				WHEN 
				commercial.NTiersCommercialDistrib='' and commercial.NomCommercialDistrib<>''
				THEN commercial.NomRegroupement+'#'+ commercial.NomCommercialDistrib
				WHEN 
				commercial.NomCommercialDistrib='' and commercial.NTiersCommercialDistrib<>'' 
				THEN commercial.NomRegroupement+'/'+ commercial.NTiersCommercialDistrib

				else commercial.NomRegroupement+'/'+ commercial.NTiersCommercialDistrib+'#'+ commercial.NomCommercialDistrib
				END 
	    ,hdr.Montant Montant
	    ,'p'+cast(CONVERT(char(6),hdr."Shipment Date", 112)as NVARCHAR(100)) Period 
	
FROM 
	dbo."CORIOLIS_V1$Sales Invoice Header" hdr 
	INNER Join @weeks ord ON cast(CONVERT(char(6),hdr."Shipment Date", 112)as NVARCHAR(100)) = wk 
	,dbo."CORIOLIS_V1$Salesperson_Purchaser" commercial
	,dbo.CORIOLIS_V1$Subcontract subcont
	WHERE 
		subcont."Customer No_"=hdr."Sell-to Customer No_"
		and commercial.Code=subcont.Salesperson

	GROUP BY 
			 hdr."Sell-to Customer No_"
			,hdr."Bill-to Name" 
			,subcont."Salesperson"
			,commercial."Name"
			,commercial.NomRegroupement
			,commercial.NTiersCommercialDistrib
			,commercial.NomCommercialDistrib
			,hdr.Montant
			,cast(CONVERT(char(6),hdr."Shipment Date", 112)as NVARCHAR(100))
			,wk 
	
ORDER BY Client

	  
	SET @query = 
	' 
	Select  
	    Client
		, Nom
		, "Id Commercial"
		, Agence
		, '     + @cols+ ' 
	From 
	    #detail  
	PIVOT (sum(Montant) FOR Period IN (' + @col_pv + ')) as pv 
	Order By Client
	' 
	EXEC(@query)

thanks for everybody's help =D> :thumbsup:
Just one more question, anyone knows whow can we export Data to MS Excel 2007 from SQL Server 2005

Koshe

Just one more question, anyone knows whow can we export Data to MS Excel 2007 from SQL Server 2005

Right click on the database->Select Source (the database you want export data from)->Select "Microsoft Excel" as destination->Select the export method->Select the table you want export data from.
Thats about it...

Hanen

I want it to be done on the code, automatically on the stored procedure
I found how to do it but it works for a table not a dynamic temp table

Belias

Malajlo wrote:

I think you are designing in wrong way.
If it is one-time generation, then it is ok to add fields in table. But if you want to use data for reports, then think to extend Key with additional column - Period.

WTF?!?!? :-k :bug: ?

Name	DataType	Subtype	Length
TableRef	Record	Field	
TableRefLast	Record	Field	
TableRefLast.setrange(TableRefLast.TableNo,50000) ;

TableRefLast.findfirst ;
tableref := tablereflast ;
TableRef."No." := 2 ;
TableRef.FieldName := 'Drugi' ;
tableRef.insert ;

not sure tu understand this post...what are you trying to achieve?inserting a field from code?you can't do it

Malajlo

It can be done. I tried and it works.

Hanen

Really????
Can you tell me how?????