SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding Missing Keys/Rows to a Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BobRoberts
Posting Yak Master

USA
102 Posts

Posted - 11/26/2012 :  17:31:02  Show Profile  Reply with Quote
I direly need your assistance on this one. Not sure what to call this one, so here is the description:

If the CustID key is in TEST_CUSTOMERS table but not in TEST_COMBINED
table, then I want to add a row with that key, and also put the 
CustDate in both the Date1 and Date2 columns.  Notice than the 
purpose of the query is to add CustID keys that are in TEST_CUSTOMERS 
TABLE but missing from TEST_COMBINED.  Any existing row in 
TEST_COMBINED is left alone. 


TEST_CUSTOMERS table:

CustID--CustDate---------------
AAAAAA--2012-09-05 00:00:00.000
BBBBBB--2012-09-05 00:00:00.000
DDDDDD--2012-09-05 00:00:00.000



TEST_COMBINED table:

CustID--Date1-------------------Date2-----------------
BBBBBB--2012-12-18 00:00:00.000|2012-12-18 00:00:00.000
CCCCCC--2012-12-18 00:00:00.000|2012-12-18 00:00:00.000
EEEEEE--2012-12-18 00:00:00.000|2012-12-18 00:00:00.000


What I want to see:

TEST_COMBINED table:

CustID--Date1-------------------Date2-----------------
AAAAAA--2012-09-05 00:00:00.000|2012-09-05 00:00:00.000
BBBBBB--2012-12-18 00:00:00.000|2012-12-18 00:00:00.000
CCCCCC--2012-12-18 00:00:00.000|2012-12-18 00:00:00.000
DDDDDD--2012-09-05 00:00:00.000|2012-09-05 00:00:00.000
EEEEEE--2012-12-18 00:00:00.000|2012-12-18 00:00:00.000


Here are scripts to create the data:

USE [Northwind]
GO
/****** Object:  Table [dbo].[TEST_COMBINED]    Script Date: 11/26/2012 16:18:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TEST_COMBINED](
	[CustID] [nvarchar](6) NULL,
	[Date1] [datetime] NULL,
	[Date2] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TEST_COMBINED] ([CustID], [Date1], [Date2]) VALUES (N'BBBBBB', CAST(0x0000A12B00000000 AS DateTime), CAST

(0x0000A12B00000000 AS DateTime))
INSERT [dbo].[TEST_COMBINED] ([CustID], [Date1], [Date2]) VALUES (N'CCCCCC', CAST(0x0000A12B00000000 AS DateTime), CAST

(0x0000A12B00000000 AS DateTime))
INSERT [dbo].[TEST_COMBINED] ([CustID], [Date1], [Date2]) VALUES (N'EEEEEE', CAST(0x0000A12B00000000 AS DateTime), CAST

(0x0000A12B00000000 AS DateTime))
/****** Object:  Table [dbo].[TEST_CUSTOMERS]    Script Date: 11/26/2012 16:18:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TEST_CUSTOMERS](
	[CustID] [nvarchar](6) NULL,
	[CustDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TEST_CUSTOMERS] ([CustID], [CustDate]) VALUES (N'AAAAAA', CAST(0x0000A0C300000000 AS DateTime))
INSERT [dbo].[TEST_CUSTOMERS] ([CustID], [CustDate]) VALUES (N'BBBBBB', CAST(0x0000A0C300000000 AS DateTime))
INSERT [dbo].[TEST_CUSTOMERS] ([CustID], [CustDate]) VALUES (N'DDDDDD', CAST(0x0000A0C300000000 AS DateTime))

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/26/2012 :  17:41:33  Show Profile  Visit chadmat's Homepage  Reply with Quote
INSERT INTO TEST_COMBINED
SELECT CustID, CustDate, CustDate
FROM TEST_CUSTOMERS
WHERE CustID NOT IN (SELECT CustID FROM TEST_COMBINED)

-Chad
Go to Top of Page

BobRoberts
Posting Yak Master

USA
102 Posts

Posted - 11/27/2012 :  10:41:31  Show Profile  Reply with Quote
Thanks - I was kind of going in that direction - but I got there a lot quicker with your help.


quote:
Originally posted by chadmat

INSERT INTO TEST_COMBINED
SELECT CustID, CustDate, CustDate
FROM TEST_CUSTOMERS
WHERE CustID NOT IN (SELECT CustID FROM TEST_COMBINED)

-Chad

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000