exec sp_executesql N'
INSERT INTO [Position] (
	[Pos_ViceClassification_ID]
	, [Pos_Request_ID]
	, [T_and_L]
	, [Pos_Parent_ID]
	, [FTEE]
	, [Type]
	, [Organization]
	, [Pos_LU_Services_ID]
	, [Pos_Section_ID]
	, [Pos_LU_PositionsAndCodes_ID]
	, [Pos_LU_DutyStations_ID]
	)
SELECT
	''3''
	, @Pos_Request_ID
	, ''3''
	, ''3''
	, @FTEE
	, @Type
	, ''VA Boston Healthcare System''
	, LU_Services_ID
FROM
	LU_Services
WHERE
	ServiceName=@ServiceName and OrgCode=@OrgCode, ''3'', 
LU_PositionsAndCodes_ID FROM LU_PositionsAndCodes WHERE PositionTitle=@PositionTitle, 
LU_DutyStations_ID FROM LU_DutyStations WHERE DutyStation=@DutyStation',N'@Pos_ViceClassification_ID int,

@Pos_Request_ID int,@T_and_L nvarchar(1),@Pos_Parent_ID int,@FTEE decimal(1,0),@Pos_Section_ID int,

@Type nvarchar(4000),@ServiceName nvarchar(16),@OrgCode nvarchar(16),@PositionTitle nvarchar(16),
@DutyStation nvarchar(16)',@Pos_ViceClassification_ID=1,@Pos_Request_ID=NULL,@T_and_L=N'1',
@Pos_Parent_ID=1,@FTEE=1,@Pos_Section_ID=1,@Type=NULL,@ServiceName=N'Please select...',
@OrgCode=N'ABC',@PositionTitle=N'DEF',@DutyStation=N'GHI'

quote:

---

Originally posted by X002548

WHY are you using Dynamic SQL?

Brett

8-)

---

quote:

---

Originally posted by webfred

Your SELECT statement is complete bullsh.. - sorry.
Read about joining tables.

---

No, you're never too old to Yak'n'Roll if you're too young to die.

---

SELECT  A.Col1, A.Col2, B.ColX, B.ColY
FROM TableA AS A
     JOIN TableB AS B
        ON B.SomeIDColumn = A.SomeOtherIDColumn
       AND B.OptionalOtherColumn = A.CorrespondingColumnName

quote:

---

Originally posted by Kristen

IF you want to select data from two tables you need a join.

SELECT  A.Col1, A.Col2, B.ColX, B.ColY
FROM TableA AS A
     JOIN TableB AS B
        ON B.SomeIDColumn = A.SomeOtherIDColumn
       AND B.OptionalOtherColumn = A.CorrespondingColumnName

---

quote:

---

Originally posted by DaveBF

[quote]

It's what gets generated from by Visual Studio.

---

quote:

---

Originally posted by Kristen

"insert into mytable(field1,field2) select last from Person ..."


If you explain what you want, rather than your proposed solution, folk will make suggestions.

---

quote:

---

Originally posted by DaveBF

I'm not ready to give up. If I can figure out the SQL syntax, I can get the Visual Studio to generate it.
I found another post in this forum which was very similar to what I want to do. It contains this:
--------------------------------------------------------------------------------
Try this:

INSERT INTO Members ( memberID, memberareaID )
SELECT @memberID, memberareaID FROM MemberAreas WHERE areadescription = @areadescription
--------------------------------------------------
And this syntax seems to be ok.
But in the Select, what if the order is reversed? That is, what if the first item is the one that requires the secondary table lookup? This gives a syntax error:

INSERT INTO Members ( memberID, memberareaID )
SELECT memberareaID FROM MemberAreas WHERE areadescription = @areadescription, @memberID

It says, Incorrect syntax near ','.

and that is the problem I'm trying to solve.

---

InsertCommand="INSERT INTO PERSON (Name,City) SELECT @NAME, (SELECT CITY FROM CITYTABLE WHERE ZIP=@ZIP)"

quote:

---

Originally posted by Kristen
Don't forget to come back and tell Brett he was right ...

---