Stuck trying to copy data into another table...

[nc297]

I have a table called ribinfo I want to copy this info into iclaimsPivotold. How can I write a stored procedure to do this?

here's the Ribinfo info and below the iclaimsPivotold create table info:

CREATE TABLE [dbo].[RibInfo](
[sort] [varchar](1) NOT NULL,
[reg] [varchar](3) NOT NULL,
[Jan 11] [decimal](6, 1) NULL,
[Jan 11 rank] [bigint] NULL,
[Jan 11 icnt] [int] NULL,
[Jan 11 tcnt] [int] NULL,
[region] [varchar](1) NOT NULL,
[area] [varchar](2) NOT NULL,
[dist] [varchar](3) NULL,
[doc] [varchar](3) NOT NULL,
[rpt] [varchar](1) NOT NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[iClaimsPivotold](
[sort] [varchar](1) NOT NULL,
[reg] [varchar](3) NOT NULL,
[Dec 10] [decimal](6, 1) NULL,
[Dec 10 rank] [bigint] NULL,
[Dec 10 icnt] [int] NULL,
[Dec 10 tcnt] [int] NULL,
[Jan 11] [decimal](6, 1) NULL,
[Jan 11 rank] [bigint] NULL,
[Jan 11 icnt] [int] NULL,
[Jan 11 tcnt] [int] NULL,
[region] [varchar](1) NOT NULL,
[area] [varchar](2) NOT NULL,
[dist] [varchar](3) NULL,
[doc] [varchar](3) NOT NULL,
[rpt] [varchar](1) NOT NULL
) ON [PRIMARY]

 

[bborissov]

INSERT INTO [iClaimsPivotold]
 ([sort],
  [reg],
  [Jan 11],
  [Jan 11 rank],
  [Jan 11 icnt],
  [Jan 11 tcnt],
  [region],
  [area],
  [dist],
  [doc],
  [rpt])

SELECT [sort],
       [reg],
       [Jan 11],
       [Jan 11 rank],
       [Jan 11 icnt],
       [Jan 11 tcnt],
       [region],
       [area],
       [dist],
       [doc],
       [rpt]
FROM [dbo].[RibInfo]

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[nc297]

Thanks the info went into the Database but not in the right order.

It put the Jan data at the bottom of the table instead across in it's column next to the others?

It went in like this. I want the Jan info to be next to the Dec info

Dec10 DecRank DecIcnt DecTcnt Jan11 JanRank JanIcnt JanTcnt
34 45 678 543
74 54 598 456


56 54 885 896
48 22 489 654

 

[bborissov]

Then you should join JAN and DEC tables and put the result in the Pivot table.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[djj55]

You could also use an UPDATE command if they have the same rows.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[nc297]

Thanks djj55 and bborissov

How would I join them and pivot info or use the update?

 

[bborissov]

INSERT INTO [iClaimsPivotold]
 ([sort],
  [reg],
  [Dec 10]
  [Dec 10 rank],
  [Dec 10 icnt],
  [Dec 10 tcnt],
  [Jan 11],
  [Jan 11 rank],
  [Jan 11 icnt],
  [Jan 11 tcnt],
  [region],
  [area],
  [dist],
  [doc],
  [rpt])

SELECT ISNULL(DecTable.[sort],JanTable.[sort]),
       ISNULL(DecTable.[reg],JanTable.[reg]),
       MAX(DecTable.[Dec 10]),
       MAX(DecTable.[Dec 10 rank]),
       MAX(DecTable.[Dec 10 icnt]),
       MAX(DecTable.[Dec 10 tcnt]),
       MAX(JanTable.[Jan 11]),
       MAX(JanTable.[Jan 11 rank]),
       MAX(JanTable.[Jan 11 icnt]),
       MAX(JanTable.[Jan 11 tcnt]),
       ISNULL(DecTable.[region],JanTable.[region]),
       ISNULL(DecTable.[area],JanTable.[area]),
       ISNULL(DecTable.[dist],JanTable.[dist]),
       ISNULL(DecTable.[doc],JanTable.[doc]),
       ISNULL(DecTable.[rpt],JanTable.[rpt])
FROM [dbo].[RibInfo] JanTable
FULL JOIN XXXXXX DecTable ON
          DecTable.[sort] = JanTable.[sort]
       AND DecTable.[reg],JanTable.[reg]
       AND DecTable.[region],JanTable.[region]
       AND DecTable.[area],JanTable.[area]
       AND DecTable.[dist],JanTable.[dist]
       AND DecTable.[doc],JanTable.[doc]
       AND DecTable.[rpt],JanTable.[rpt]
GROUP BY ISNULL(DecTable.[sort],JanTable.[sort]),
         ISNULL(DecTable.[reg],JanTable.[reg]),
         ISNULL(DecTable.[region],JanTable.[region]),
         ISNULL(DecTable.[area],JanTable.[area]),
         ISNULL(DecTable.[dist],JanTable.[dist]),
         ISNULL(DecTable.[doc],JanTable.[doc]),
         ISNULL(DecTable.[rpt],JanTable.[rpt])

NOT TESTED!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[nc297]

Thanks so much I will test it in the morning. I realize I didn't put primary keys onto my table so when I did I was able to insert the info into a new table.

 

[nc297]

Thanks again for your reply Borissov when I added the primary keys things worked out.