locked
Make create table command dynamic. RRS feed

  • Question

  • Hi everyone!

      I need to run the second statement (create a table) for each row from the first one.

    --

    SELECT * FROM dbo.#PAPEIS AS A ORDER BY A.Volume DESC;
    GO

    CodNeg       Volume
    ------------ ---------------------------------------
    PETR4        323099096439.00
    VALE3        226365972770.00
    ITUB4        161138547243.00
    BBDC4        142442591724.00
    BBAS3        125503630770.00
    ABEV3        87651104964.00
    B3SA3        83606368629.00
    IBOV11       76857131123.00
    PETR3        67777934830.00
    MGLU3        67240714820.00
    ITSA4        63946253382.00
    SUZB3        57383323251.00
    JBSS3        57301529954.00
    BRFS3        47149446141.00
    LREN3        40966613536.00
    RENT3        40881707068.00
    IRBR3        39720083984.00
    GGBR4        38585660463.00
    CSNA3        37666710308.00
    VVAR3        37461812344.00

    SELECT
    A.[Data], A.CodNeg, a.PreUlt
    INTO dbo.#PETR4
    FROM dbo.CotaHist AS A
    WHERE A.CodNeg='PETR4'
    GO

    Hope I was clear enough.


    Doria

    Saturday, November 23, 2019 2:11 AM

Answers

  • Why not just create a single set of temp tables where you add the value of CodNeg as a key?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, November 23, 2019 1:14 PM

All replies

  • Why do think you need to do this? This can be done with dynamic SQL, but often the desire to create tables dynamically comes from a misconception earlier in the process. So please explain what your actual problem is, and we may be able to find an overall better solution for you.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, November 23, 2019 11:04 AM
  • Hi Erland!

      Thanks for your answer. Well, I split the logic for simplicity; I load a mass of data, separate the top 20 by volume, and then calculate the percentage change in prices to finally correlate them. I'll post the all code:

    USE [tempdb];
    GO
    SET NOCOUNT ON;
    
    
    IF OBJECT_ID('dbo.DataBulk', 'U') IS NOT NULL DROP TABLE dbo.DataBulk;
    GO
    IF OBJECT_ID('dbo.CotaHist', 'U') IS NOT NULL DROP TABLE dbo.CotaHist;
    GO
    
    
    CREATE TABLE dbo.DataBulk
    (
      [rowid] INT IDENTITY(1,1) PRIMARY KEY,
      [raw] VARCHAR(1024) NOT NULL
    );
    GO
    
    CREATE TABLE dbo.CotaHist
    (
      TipReg INT NULL,
      [Data] DATE NULL,
      CodBDI CHAR(02) NULL,
      CodNeg CHAR(12) NULL,
      TPMerc INT NULL,
      NomRes CHAR(12) NULL,
      Especi CHAR(10) NULL,
      PrazoT CHAR(03) NULL,
      ModRef CHAR(04) NULL,
      PreAbe DECIMAL(11,2) NULL,
      PreMax DECIMAL(11,2) NULL,
      PreMin DECIMAL(11,2) NULL,
      PreMed DECIMAL(11,2) NULL,
      PreUlt DECIMAL(11,2) NULL,
      PreOfC DECIMAL(11,2) NULL,
      PreOfV DECIMAL(11,2) NULL,
      TotNeg INT NULL,
      QuaTot BIGINT NULL,
      VolTot DECIMAL(16,2) NULL,
      PreExe DECIMAL(11,2) NULL,
      IndOPC INT NULL,
      DatVen INT NULL,
      FatCot INT NULL,
      PtoExe DECIMAL(10,2) NULL,
      CodISI CHAR(12) NULL,
      DisMes INT NULL
    );
    GO
    
    
    BULK INSERT dbo.DataBulk
      FROM 'C:\downloads\CotaHist\CotaHist_2019.txt'
        WITH
          (
    	    FIRSTROW = 2,
    		LASTROW = 686753,
            FORMATFILE='C:\downloads\CotaHist\DataBulk.fmt'
          );
    GO
    
    
    INSERT dbo.CotaHist (TipReg, [Data], CodBDI, CodNeg, TPMerc, NomRes, Especi, PrazoT, ModRef, PreAbe, PreMax, PreMin, PreMed, PreUlt, PreOfC, PreOfV, TotNeg, QuaTot, VolTot, PreExe, IndOPC, DatVen, FatCot, PtoExe, CodISI, DisMes)
      SELECT
        TipReg = SUBSTRING([raw], 001, 002),
    	[Data] = SUBSTRING([raw], 003, 008),
    	CodBDI = SUBSTRING([raw], 011, 002),
    	CodNeg = SUBSTRING([raw], 013, 012),
    	TPMerc = SUBSTRING([raw], 025, 003),
    	NomRes = SUBSTRING([raw], 028, 012),
    	Especi = SUBSTRING([raw], 040, 010),
    	PrazoT = SUBSTRING([raw], 050, 003),
    	ModRef = SUBSTRING([raw], 053, 004),
    	PreAbe = SUBSTRING([raw], 057, 011),
    	PreMax = SUBSTRING([raw], 070, 011),
    	PreMin = SUBSTRING([raw], 083, 011),
    	PreMed = SUBSTRING([raw], 096, 011),
    	PreUlt = SUBSTRING([raw], 109, 011),
    	PreOfC = SUBSTRING([raw], 122, 011),
    	PreOfV = SUBSTRING([raw], 135, 011),
    	TotNeg = SUBSTRING([raw], 148, 005),
    	QuaTot = SUBSTRING([raw], 153, 018),
    	VolTot = SUBSTRING([raw], 171, 016),
    	PreExe = SUBSTRING([raw], 189, 011),
    	IndOPC = SUBSTRING([raw], 202, 001),
    	DatVen = SUBSTRING([raw], 203, 008),
    	FatCot = SUBSTRING([raw], 211, 007),
    	PtoExe = SUBSTRING([raw], 218, 007),
    	CodISI = SUBSTRING([raw], 231, 012),
    	DisMes = SUBSTRING([raw], 243, 003)
        -- TPMerc = (CASE WHEN Length([raw] < 29 THEN NULL ELSE SubString([raw], 29 , 6) END)
      FROM dbo.DataBulk
      ORDER BY [rowid];
    GO
    
    
    IF OBJECT_ID('dbo.#PAPEIS', 'U') IS NOT NULL DROP TABLE dbo.#PAPEIS;
    GO
    SELECT TOP 20
    	A.CodNeg, SUM(A.VolTot) AS Volume
    	INTO dbo.#PAPEIS
    	FROM dbo.CotaHist AS A
    	WHERE A.CodBDI='02' -- LOTE PADRÃO
    	GROUP BY A.CodNeg
    	ORDER BY Volume DESC;
    GO
    SELECT * FROM dbo.#PAPEIS AS A ORDER BY A.Volume DESC;
    GO
    
    
    IF OBJECT_ID('dbo.#PETR4', 'U') IS NOT NULL DROP TABLE dbo.#PETR4;
    GO
    SELECT
    	A.[Data], A.CodNeg, a.PreUlt
    	INTO dbo.#PETR4
    	FROM dbo.CotaHist AS A
    	WHERE A.CodNeg='PETR4'
    GO
    --SELECT * FROM dbo.#PETR4 AS A ORDER BY A.[Data] DESC;
    --GO
    
    
    IF OBJECT_ID('dbo.#PETR4_ratio', 'U') IS NOT NULL DROP TABLE dbo.#PETR4_ratio;
    GO
    WITH CTE AS
    (
    SELECT
    	rownum = ROW_NUMBER() OVER (ORDER BY A.[Data] DESC),
    	A.[Data],
    	A.CodNeg,
    	a.PreUlt
    	FROM dbo.#PETR4 AS A
    )
    SELECT
    	CTE.[Data],
    	CTE.CodNeg,
    	[prev].PreUlt PreviousValue,
    	CTE.PreUlt,
    	[next].PreUlt NextValue,
    	CAST((((CTE.PreUlt/[next].PreUlt)-1)*100) AS DECIMAL(5,2)) AS [%]
    	INTO dbo.#PETR4_ratio
    	FROM CTE
    		LEFT JOIN CTE AS [prev] ON [prev].rownum = CTE.rownum - 1
    		LEFT JOIN CTE AS [next] ON [next].rownum = CTE.rownum + 1;
    GO
    SELECT * FROM dbo.#PETR4_ratio AS A ORDER BY A.[Data] DESC;
    GO
    
    
    IF OBJECT_ID('dbo.#VALE3', 'U') IS NOT NULL DROP TABLE dbo.#VALE3;
    GO
    SELECT
    	A.[Data], A.CodNeg, a.PreUlt
    	INTO dbo.#VALE3
    	FROM dbo.CotaHist AS A
    	WHERE A.CodNeg='VALE3'
    GO
    --SELECT * FROM dbo.#VALE3 AS A ORDER BY A.[Data] DESC;
    --GO
    
    
    IF OBJECT_ID('dbo.#VALE3_ratio', 'U') IS NOT NULL DROP TABLE dbo.#VALE3_ratio;
    GO
    WITH CTE AS
    (
    SELECT
    	rownum = ROW_NUMBER() OVER (ORDER BY A.[Data] DESC),
    	A.[Data],
    	A.CodNeg,
    	a.PreUlt
    	FROM dbo.#VALE3 AS A
    )
    SELECT
    	CTE.[Data],
    	CTE.CodNeg,
    	[prev].PreUlt PreviousValue,
    	CTE.PreUlt,
    	[next].PreUlt NextValue,
    	CAST((((CTE.PreUlt/[next].PreUlt)-1)*100) AS DECIMAL(5,2)) AS [%]
    	INTO dbo.#VALE3_ratio
    	FROM CTE
    		LEFT JOIN CTE AS [prev] ON [prev].rownum = CTE.rownum - 1
    		LEFT JOIN CTE AS [next] ON [next].rownum = CTE.rownum + 1;
    GO
    SELECT * FROM dbo.#VALE3_ratio AS A ORDER BY A.[Data] DESC;
    GO
    
    
    SELECT
    	--(AVG(A.[%] * B.[%]) - (AVG(A.[%]) * AVG(B.[%]))) AS Numerator,
    	--(STDEVP(A.[%]) * STDEVP(B.[%])) AS Denominator,
    	(AVG(A.[%] * B.[%]) - (AVG(A.[%]) * AVG(B.[%]))) / (STDEVP(A.[%]) * STDEVP(B.[%])) AS Correlation
    	FROM dbo.#PETR4_ratio AS A
    		INNER JOIN dbo.#VALE3_ratio AS B ON A.[Data]=B.[Data];
    GO
    


    Doria

    Saturday, November 23, 2019 12:07 PM
  • On time, finally I need to correlate the top 20 papers, A x B, A x C, A x D, .... A x Z. Then B x C, B x D, B x E, ... B x Z, and goes on.... 20 x 19 = 380 total combinations.


    Doria

    Saturday, November 23, 2019 12:39 PM
  • Why not just create a single set of temp tables where you add the value of CodNeg as a key?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, November 23, 2019 1:14 PM
  • Good idea. I will try that!

    Doria


    • Edited by dydoria Saturday, November 23, 2019 1:42 PM
    Saturday, November 23, 2019 1:42 PM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 25, 2019 2:39 AM