Insert Dependant on Another Field

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

Insert Dependant on Another Field

Author

Topic

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-02-01 : 14:23:19

I have this query (see below) that returns all of the groups and their contact people. I need to insert them into another table called statelst.

The problem is in the statelst table I need the people from the query who's "Position" is 1 - have their information put in the [Contact1], [BusPhone1],[HomePhone1],[Internet1] fields and the same with contact 2 their position would be 2 and their fields would be [Contact2], [BusPhone2],[HomePhone2],[Internet2].

Is there a way to do this using one query to select and then insert into the statelst table but also check position field for inserting the contact people's information. I'm guessing there would need to be a group on GroupID because there could be more than one contact for each group.

CREATE TABLE [dbo].[statelst](
        [GroupID] [int] IDENTITY(1,1) NOT NULL
	[GroupNum] [nvarchar](15) NULL,
	[State] [nvarchar](20) NULL,
	[City] [nvarchar](50) NULL,
	[Day] [nvarchar](30) NULL,
	[Time] [nvarchar](20) NULL,
	[Place] [nvarchar](249) NULL,
	[Place2] [nvarchar](249) NULL,
	[GrpName] [nvarchar](30) NULL,
	[Suburb] [nvarchar](50) NULL,
	[GroupName] [nvarchar](15) NULL,
	[Intergroup] [nvarchar](3) NULL,
	[MeetingChng] [datetime] NULL,
	[AddressGrp] [nvarchar](45) NULL,
	[MailCity] [nvarchar](20) NULL,
	[Zip] [nvarchar](10) NULL,
	[aadcID] [nvarchar](5) NULL,
	[Region] [nvarchar](3) NULL,
	[Continent] [nvarchar](30) NULL,
	[Started] [datetime] NULL,
	[Country] [nvarchar](25) NULL,
	[LastComm] [datetime] NULL,
	[CommType] [nvarchar](3) NULL,
	[AltPhone] [nvarchar](15) NULL,
	[PhoneType] [nvarchar](10) NULL,
	[GrpDate] [nvarchar](10) NULL,
	[GrpCode] [nvarchar](5) NULL,
	[Contact1] [nvarchar](20) NULL,
	[BusPhone1] [nvarchar](27) NULL,
	[HomePhone1] [nvarchar](27) NULL,
	[Internet1] [nvarchar](3) NULL,
	[Contact2] [nvarchar](20) NULL,
	[BusPhone2] [nvarchar](27) NULL,
	[HomePhone2] [nvarchar](27) NULL,
	[Internet2] [nvarchar](3) NULL,
	[Contacts] [nvarchar](100) NULL

SELECT     groups.GroupID, groups.GroupNum, groups.State, groups.City, groups.Time, groups.Place, groups.Place2, groups.GrpName, groups.Suburb, groups.GroupName, 
                      groups.Intergroup, groups.MeetingChange, groups.AddressGrp, groups.MailCity, groups.Zip, groups.Region, groups.Continent, groups.Started, groups.Country, 
                      groups.LastComm, groups.CommType, groups.AltPhone, groups.PhoneType, groups.Language, groups.Modified, groups.GrpStatus, days.Day, grpcon.Position, 
                      members.FirstName, members.MemberID, members.BusPhone, members.HomePhone, members.Email
FROM         days INNER JOIN
                      groups ON days.DaysID = groups.DaysID LEFT OUTER JOIN
                      members INNER JOIN
                      grpcon ON members.MemberID = grpcon.MemberID ON groups.GroupID = grpcon.GroupID

This is kinda hard to explain but basically I want to insert all the fields into the new table but is the "Position" column = '1' then FirstName, BusPhone, HomePhone, Email goes into these columns [Contact1], [BusPhone1],[HomePhone1],[Internet1]. But if "Position" = '2' then they go into these columns [Contact2], [BusPhone2],[HomePhone2],[Internet2].

Hope that helps!

Thanks,
Stacy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-02 : 01:44:22

you can make select like this and then use this to insert relevant fields into statelist table.


SELECT     groups.GroupID, groups.GroupNum, groups.State, groups.City, groups.Time, groups.Place, groups.Place2, groups.GrpName, groups.Suburb, groups.GroupName, 
                      groups.Intergroup, groups.MeetingChange, groups.AddressGrp, groups.MailCity, groups.Zip, groups.Region, groups.Continent, groups.Started, groups.Country, 
                      groups.LastComm, groups.CommType, groups.AltPhone, groups.PhoneType, groups.Language, groups.Modified, groups.GrpStatus, days.Day, m.*
FROM         days 
INNER JOIN   groups 
ON days.DaysID = groups.DaysID 
LEFT OUTER JOIN (
                 SELECT GroupID,
                        MAX(CASE WHEN grpcon.Position = 1 THEN members.FirstName END) AS Contact1,
                        MAX(CASE WHEN grpcon.Position = 1 THEN members.BusPhone END) AS BusPhone1,
                        MAX(CASE WHEN grpcon.Position = 1 THEN members.HomePhone END) AS HomePhone1,
                        MAX(CASE WHEN grpcon.Position = 1 THEN members.Email END) AS Internet1, 
                        MAX(CASE WHEN grpcon.Position = 2 THEN members.FirstName END) AS Contact2,
                        MAX(CASE WHEN grpcon.Position = 2 THEN members.BusPhone END) AS BusPhone2,
                        MAX(CASE WHEN grpcon.Position = 2 THEN members.HomePhone END) AS HomePhone2,
                        MAX(CASE WHEN grpcon.Position = 2 THEN members.Email END) AS Internet2 
                 FROM   members 
                 INNER JOIN grpcon 
                 ON members.MemberID = grpcon.MemberID 
                 GROUP BY grpcon.GroupID
               )m
ON groups.GroupID = m.GroupID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources