April 15, 2003 at 4:29 am
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
April 15, 2003 at 4:32 am
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]
April 15, 2003 at 4:36 am
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
April 15, 2003 at 5:24 am
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]
April 15, 2003 at 6:49 am
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
April 15, 2003 at 6:51 am
Sorry also this is for frequent use, I plan to have it in a stored procedure...
April 15, 2003 at 7:03 am
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]
April 15, 2003 at 7:09 am
by using AS in the select statement, are you not specifing the columns then ??
Thanks for all your help Frank
John
April 15, 2003 at 8:05 am
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]
April 15, 2003 at 8:09 am
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
April 15, 2003 at 8:22 am
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]
April 15, 2003 at 8:36 am
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
April 15, 2003 at 8:41 am
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