Cursor Use

  • I want to take the records from one table, and then split each record into 3 new records in a 2nd table...

    I am a newbie to SQL, and I presume I should use a cursor in a stored procedure...

    Thanks, John

  • Hi,

    there are plenty of ways to do this (T-SQL, ADO....).

    Could you provide more informations, e.g. table structures?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I have included the source and the dest ... I need to take the first fields and then the MillCompany & Code add a new record with the rest of the Mill data, and then take the RDC1Company and Code and create a new record with the beginning etc again...

    Source Table :

    CREATE TABLE [dbo].[tblAdjustedYTDb] (

    [Period] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [Prog_ID] [int] NOT NULL ,

    [EndMarket] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [LineNo] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [SKUCode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [SKUName] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [Obsolete] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [Dispenser] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [CDP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [HierLevel5Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [HierLevel3Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [SalesOrganisation] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [MillCompany] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [MillCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [RDC1Company] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [RDC1Code] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [RDC2Company] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [RDC2Code] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [MillProcess] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [RDC1Process] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [RDC2Process] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [VolumeMoved] [float] NULL ,

    [QTYMoved] [float] NULL ,

    [MillST] [float] NULL ,

    [MillPA] [float] NULL ,

    [MillIn] [float] NULL ,

    [MillHA] [float] NULL ,

    [MillOUT] [float] NULL ,

    [RDC1ST] [float] NULL ,

    [RDC1PA] [float] NULL ,

    [RDC1In] [float] NULL ,

    [RDC1HA] [float] NULL ,

    [RDC1OUT] [float] NULL ,

    [RDC2ST] [float] NULL ,

    [RDC2PA] [float] NULL ,

    [RDC2In] [float] NULL ,

    [RDC2HA] [float] NULL ,

    [RDC2OUT] [float] NULL ,

    [SACost] [float] NULL ,

    [DAC] [float] NULL ,

    [DAH] [float] NULL ,

    [DAP] [float] NULL ,

    [ODCost] [float] NULL ,

    [PFRDC1] [float] NULL ,

    [PFRDC2] [float] NULL ,

    [IRrdc1] [float] NULL ,

    [IRrdc2] [float] NULL ,

    [CDPCost] [float] NULL ,

    [Sector] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    GO

    Dest Table:

    CREATE TABLE [dbo].[kc_RTMb] (

    [FindMe] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [Period] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [EndMarket] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [Sector] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [Company] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [Code] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [VolumeMoved] [float] NULL ,

    [QTYMoved] [float] NULL ,

    [ST] [float] NULL ,

    [PA] [float] NULL ,

    [HA] [float] NULL ,

    [SA] [float] NULL ,

    [AD] [float] NULL ,

    [OD] [float] NULL ,

    [PF] [float] NULL ,

    [IR] [float] NULL ,

    [CD] [float] NULL) ON [PRIMARY]

    GO

  • Hi,

    sorry, it took a while to understand what you want to do.

    I assume you're redesigning? Or is this used for frequent import?

    Well, of course you can declare a cursor, but what about SELECT <fieldlist> INTO <dest_table>?

    First run: SELECT...MillCompany & Code INTO ...

    Second run: SELECT RDC1Comapny INTO ....

    Third run: SELECT RDC2Company INTO...

    You can run this within a stored procedure without a cursor.

    Take a look at the SELECT INTO syntax in BOL.

    Cheers,

    Frank

    Sorry, my mistake. With the above mentioned method you will need a cursor I think. Replace SELECT INTO with INSERT INTO and take a look at

    INSERT...SELECT syntax in BOL.

    HTH

    Edited by - a5xo3z1 on 04/15/2003 05:30:06 AM

    Edited by - a5xo3z1 on 04/15/2003 05:35:06 AM

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Already tried that but I get "Insert Error: Column name or number of supplied values does not match table definition." as I am using Sum's ... how do I get around this ... select statement is :

    INSERT INTO dbo.kc_RTMb

    SELECT Period, EndMarket AS MKT, Sector AS SEC, MillCompany AS CT, MillCode AS CO, SUM(VolumeMoved) AS M3, SUM(MillST) AS ST,

    SUM(MillPA) AS PA, SUM(MillIn + MillHA + MillOUT) AS HA, SUM(DAC + DAH + DAP) AS AD, SUM(SACost) AS SA, SUM(ODCost) AS OD,

    SUM(CDPCost) AS CD

    FROM dbo.tblAdjustedYTD

    GROUP BY Period, EndMarket, Sector, MillCompany, MillCode

  • Sorry also this is for frequent use, I plan to have it in a stored procedure...

  • I think we're moving closer to success.

    Taken from BOL

    ...The select list of the subquery must match the column list of the INSERT statement. If no column list is specified, the select list must match the columns in the table or view being inserted into.

    ...

    you are not providing a value for several field, so you

    a) provide a dummy value for missing fields or

    b) specify the fields to be used by INSERT INTO

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • by using AS in the select statement, are you not specifing the columns then ??

    Thanks for all your help Frank

    John

  • Hi John,

    well, now I'm a bit confused. I'm trying to run this query

    INSERT INTO tblhbest1 (UnternehmenID, Handelsobjekt) SELECT UnternehmenID, Handelsobjekt FROM view1

    and receive a Server: Msg 8623, Level 16, State 2, Line 5 error

    ?????

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I did it... Thanks Frank for the help... here it is ... the column names were the key...

    ALTER PROCEDURE dbo.CreateRTMdb AS

    TRUNCATE TABLE kc_RTMb

    INSERT INTO kc_RTMb(Period, MKT, SEC, CT, CO, M3, ST, PA, HA, AD, SA, OD, CD)

    SELECT Period, EndMarket AS MKT, Sector AS SEC, MillCompany AS CT, MillCode AS CO, sum(isnull(VolumeMoved,0)) AS M3, sum(isnull(MillST,0)) AS ST,

    sum(isnull(MillPA,0)) AS PA, sum(isnull(MillIn,0) + isnull(MillHA,0) + isnull(MillOUT,0)) AS HA, sum(isnull(DAC,0) + isnull(DAH,0) + isnull(DAP,0)) AS AD, sum(isnull(SACost,0)) AS SA, sum(isnull(ODCost,0)) AS OD,

    sum(isnull(CDPCost,0)) AS CD

    FROM dbo.tblAdjustedYTD

    GROUP BY Period, EndMarket, Sector, MillCompany, MillCode

    INSERT INTO dbo.kc_RTMb(Period, MKT, SEC, CT, CO, ST, PF, IR, PA, HA)

    SELECT Period, EndMarket AS MKT, Sector AS SEC, RDC1Company AS CT, RDC1Code AS CO, sum(isnull(RDC1ST,0)) AS ST,

    sum(isnull(PFRDC1,0)) AS PF, sum(isnull(IRrdc1,0)) AS IR, sum(isnull(RDC1PA,0)) AS PA, sum(isnull(RDC1In,0) + isnull(RDC1HA,0) + isnull(RDC1OUT,0)) AS HA

    FROM dbo.tblAdjustedYTD

    GROUP BY Period, EndMarket, Sector, RDC1Company, RDC1Code

    HAVING RDC1Code is not null

    INSERT INTO dbo.kc_RTMb(Period, MKT, SEC, CT, CO, ST, PF, IR, PA, HA)

    SELECT Period, EndMarket AS MKT, Sector AS SEC, RDC2Company AS CT, RDC2Code AS CO, sum(isnull(RDC2ST,0)) AS ST,

    sum(isnull(PFRDC2,0)) AS PF, sum(isnull(IRrdc2,0)) AS IR, sum(isnull(RDC2PA,0)) AS PA, sum(isnull(RDC2In,0) + isnull(RDC2HA,0) + isnull(RDC2OUT,0)) AS HA

    FROM dbo.tblAdjustedYTD

    GROUP BY Period, EndMarket, Sector, RDC2Company, RDC2Code

    HAVING RDC2Code is not null

    GO

  • Hi,

    finally we did it 🙂

    Now my question is why I receive this error

    Server: Msg 8623, Level 16, State 2, Line 5 error

    Translation from German to English (..Cannot create Execution plan).

    This seems to be a new topic.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Try to take the space away between the database name and the open brackets ( ... This was one problem I had...

    INSERT INTO tblhbest1(UnternehmenID, Handelsobjekt) SELECT UnternehmenID, Handelsobjekt FROM view1

  • Sorry, there is no space. I'll start a new topic on this

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply