Merging two tables based on two clumns

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

SQL Server 2005 Forums

Transact-SQL (2005)

Merging two tables based on two clumns

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-07-12 : 14:18:07

i am trying to combine data in two tables and here is my situation

i have two tables state1 and state2 (both are similar in structure)

CREATE TABLE [dbo].[STATE1](
[CODE] [varchar](2) NOT NULL,
[ID] [int] NOT NULL,
[BEG] [smalldatetime] NOT NULL,
[END] [smalldatetime] NOT NULL,
[DATE] [smalldatetime] NOT NULL,
CONSTRAINT [PK_LCD_STATE] PRIMARY KEY CLUSTERED
(
[STATE] ASC,
[ID] ASC,
[BEG] ASC,
[DATE] ASC
) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO STATE1 SELECT '1', 28563, '2009-02-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE1 SELECT '1', 29584, '2009-10-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE1 SELECT '1', 29971, '2009-10-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE1 SELECT '1', 30135, '2009-08-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE1 SELECT '1', 30288, '2009-08-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE1 SELECT '1', 30300, '2009-11-15 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE1 SELECT '2', 30304, '2009-09-15 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE1 SELECT '2', 30306, '2009-09-15 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE1 SELECT '2', 30312, '2009-09-15 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE1 SELECT '2', 30316, '2009-08-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'

----
----

INSERT INTO STATE2 SELECT '1', 28563, '2009-02-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE2 SELECT '1', 29584, '2009-10-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE2 SELECT '1', 29971, '2009-10-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE2 SELECT '1', 30135, '2009-08-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE2 SELECT '1', 30288, '2009-08-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE2 SELECT '1', 30300, '2009-11-15 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE2 SELECT '1', 30314, '2009-09-15 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE2 SELECT '2', 30306, '2009-09-15 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE2 SELECT '2', 30312, '2009-09-15 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE2 SELECT '2', 30316, '2009-08-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'
INSERT INTO STATE2 SELECT '2', 30317, '2009-08-16 00:00:00', '2045-12-31 00:00:00', '2009-01-01 00:00:00'

1. i want to update STATE1 by inserting new rows (STATE, ID) from STATE2 and not in STATE1 and set BEG date to a default date like '07/01/2011' and END should be '12/31/2045' and DATE should be same as BEG date. e.g state 1 and ID 30314 in STATE2

2. I want to terminate the END date to a default date like '06/30/2011' in STATE1 if the (STATE, ID) do not have an entry in STATE2
e.g STATE 2 and ID 30304 in STATE1

I appreciate your help

Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-12 : 15:06:01


Insert Into State1 
Select State, Id, '7/1/2011', '12/31/2045', '7/1/2011' 
From State2 A 
Left Join State1 B 
On A.State = B.State
and A.Id = B.Id
Where B.State is null


Update A 
Set End = '6/30/2011'
From State1 A
Left Join State2 B
On A.State = B.State
and A.Id = B.Id
Where B.State is null

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources