About
Pricing
Community
Teams
Start Free Trial
Log in
Tom Knowlton
asked on
1/3/2013
take results of select and map it to an insert statement
This is the selection I want:
I want insert into table AgentFormList using the SELECT shown above. Not sure about the syntax.
Microsoft SQL Server
5
1
Last Comment
Tom Knowlton
8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Steve Wales
1/3/2013
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Tom Knowlton
1/3/2013
ASKER
Yep!
use PP
INSERT INTO [PP].[dbo].[AgentFormList]
([AgentFormListSID]
,[FormTypeID]
,[Sequence]
,[FormName]
,[ColistFlag]
,[AcceptanceReqd]
,[DuplicatesAllowed]
,[ExternalLink]
,[TaskCode]
,[AMFID]
,[AMFSID]
,[ClientID]
,[ClientSID]
,[ProductID]
,[Active])
select
1
,[afl].[FormTypeID]
,[afl].[Sequence]
,[afl].[FormName]
,[afl].[ColistFlag]
,[afl].[AcceptanceReqd]
,[afl].[DuplicatesAllowed]
,[afl].[ExternalLink]
,[afl].[TaskCode]
,[AmFClient].[AMFirmID]
,[AmFClient].[AMFirmSID]
,[AmFClient].[MerchantID]
,[AmFClient].[MerchantSID]
,1
,1
from agentformlist afl --18*24
cross join (select
distinct
Asset.AMFirmID,
Asset.AMFirmSID,
Pool.MerchantID,
Pool.MerchantSID
from Asset
join Pool on
Pool.PoolID = Asset.PoolID
AND Pool.PoolSID = Asset.PoolSID
) AS AmFClient
Tom Knowlton
1/3/2013
ASKER
Thanks!
TempDBA
1/3/2013
You can use insert into select combination as expert sjwales suggested above
(No points plz)
INSERT INTO TABLENAME (COL1, COL2, COL3, ......, COLN)
SELECT af1.col1,
af2.col2,
.
.
afn.coln,
AmFClient.col1,
AmFClient.col2,
.
.
AmFClient.colm
FROM bla bla bla...
NOTE:- n+m = N
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Tom Knowlton
1/3/2013
ASKER
TempDBA:
Thanks!
use PP
INSERT INTO [PP].[dbo].[AgentFormList]
([AgentFormListSID]
,[FormTypeID]
,[Sequence]
,[FormName]
,[ColistFlag]
,[AcceptanceReqd]
,[DuplicatesAllowed]
,[ExternalLink]
,[TaskCode]
,[AMFID]
,[AMFSID]
,[ClientID]
,[ClientSID]
,[ProductID]
,[Active])
select
1
,[afl].[FormTypeID]
,[afl].[Sequence]
,[afl].[FormName]
,[afl].[ColistFlag]
,[afl].[AcceptanceReqd]
,[afl].[DuplicatesAllowed]
,[afl].[ExternalLink]
,[afl].[TaskCode]
,[AmFClient].[AMFirmID]
,[AmFClient].[AMFirmSID]
,[AmFClient].[MerchantID]
,[AmFClient].[MerchantSID]
,1
,1
from agentformlist afl --18*24
cross join (select
distinct
Asset.AMFirmID,
Asset.AMFirmSID,
Pool.MerchantID,
Pool.MerchantSID
from Asset
join Pool on
Pool.PoolID = Asset.PoolID
AND Pool.PoolSID = Asset.PoolSID
) AS AmFClient