Adding info to another table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding info to another table

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-01-31 : 15:35:02

I have the same fields in two tables but one table has new data that needs to be put into the existing table?

Here's the table structure:


CREATE TABLE [dbo].[iClaimsPivotold](
	[sort] [varchar](1) NOT NULL,
	[reg] [varchar](3) NOT NULL,
	[Nov 10] [decimal](6, 1) NULL,
	[Nov 10 rank] [bigint] NULL,
	[Nov 10 icnt] [int] NULL,
	[Nov 10 tcnt] [int] NULL,
	[Dec 10] [decimal](6, 1) NULL,
	[Dec 10 rank] [bigint] NULL,
	[Dec 10 icnt] [int] NULL,
	[Dec 10 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

I want to add these columns that are in TestPivot table (below) and the data into the iclaimsPivotOld table.

Columns from the testpivot table I want to add to the iclaimsPivotOld table are:

sort,
reg,
[jan 11] [decimal](6, 1) NULL,
[jan 11 rank] [bigint] NULL,
[jan 11 icnt] [int] NULL,
[jan 11 tcnt] [int] NULL,
area,
dist,
doc,
rpt

I tried this but the data didn't go in the correct way:

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 test

What am I doing wrong?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 17:49:53

Can you be more specific about "the data didn't go in the correct way"

You don't get an error message ? The columns you are attempting to insert into don't exist in the table structure presented.

Poor planning on your part does not constitute an emergency on my part.

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-01-31 : 18:25:04

Maybe you want to first add the missing columns (DDL) then later migrate data using insert/update statement (DML) ??

Nathan Skerl

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-01-31 : 18:33:45

Oop's sorry about that...

When I used the insert statement below my data did not line up correctly. The Jan data should have been inserted on the same line as the 1 Nat then Dec data.

I shorten the names of the columns and only have two columns showing hope you can still get the effect of it. Should I use an update. Should I put the Jan columns in the iclaimsPivotOld table then do an update from the testPivot Table into the iclaimsPivotOld table?


sort  reg  Dec 10  DecRank  Decicnt Jan11  JanRank  Jicnt
1     NAT   256     54        456                
2     BOS   652     22        546  
1     NAT                             254    564      555
2     BOS                             245    445      536

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-01-31 : 18:34:44

Thanks Nathans...If I add the missing columns how would I write this, I would use an update instead right?

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 test

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 19:33:40

What you want is an UPDATE, not an INSERT

Update iclaimspivotold
SET [Jan11] = b.[Jan 11 rank]
FROM test a inner join iclaimspivot b
on a.sort = b.sort and a.region = b.region

For example

This would UPDATE the rows in the table which match on the specified columns.

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-01-31 : 21:34:45

Thanks Dataguru would I need to add the other Jan columns too in the test table? like this?

Update iclaimspivotold
SET [Jan 11] = b.[Jan 11]
set [jan 11 rank] = b.[jan 11 rank]
set [jan icnt = b.[jan icnt]
set [jan tcnt = b.[jan tcnt]
FROM test a inner join iclaimspivot b
on a.sort = b.sort and a.region = b.region

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-01 : 05:41:49

Update iclaimspivotold
SET [Jan 11] = b.[Jan 11]
, [jan 11 rank] = b.[jan 11 rank]
, [jan icnt = b.[jan icnt]
, [jan tcnt = b.[jan tcnt]
FROM test a inner join iclaimspivot b
on a.sort = b.sort and a.region = b.region

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-01 : 10:52:36

Tried this but nothing is going into the database.

Update iclaimspivotold
SET [Jan 11] = b.[Jan 11]
, [jan 11 rank] = b.[jan 11 rank]
, [jan 11 icnt] = b.[jan 11 icnt]
, [jan 11 tcnt] = b.[jan 11 tcnt]
FROM ribinfo a inner join iclaimspivotold b
on a.sort = b.sort and a.region = b.region

When I look the months of Jan 11 have null

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-01 : 17:04:16

does this come back with records?
SELECT a.sort,b.sort as Pivotsort, a.reg, b.reg as PivotReg,
[Jan 11] = b.[Jan 11]
, [jan 11 rank] = b.[jan 11 rank]
, [jan 11 icnt] = b.[jan 11 icnt]
, [jan 11 tcnt] = b.[jan 11 tcnt]
FROM ribinfo a LEFTjoin iclaimspivotold b
on a.sort = b.sort and a.region = b.region

If the pivot and value columns are null than the join is incorrect. Make sure that there are valid links between the data. I was basing the join on your sample data posted

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-01 : 20:08:15

I learned another lesson today...I must add primary keys to my table!!!! Ugh that's what I was missing now what you wrote works out fine!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources