Insert Into

[Mayfield2268]

I have this sql insert statement below. I use a select statement to grab the values. The only problem is that I want to use @New_ID as a value to insert. Where it says SELECT New_ID, can I replace it with SELECT @New_ID?

Thanks


CREATE PROCEDURE dbo.DeletePersonMerge
@New_ID varchar (100),
@Deleted_New_ID varchar (100)

AS

INSERT INTO tblPersonInterests (New_ID, IC_ID, Mod_Date, Last_Changer)
SELECT New_ID, IC_ID, Mod_Date, Last_Changer FROM tblPersonInterests WHERE
New_ID = @Deleted_New_ID AND IC_ID NOT IN (SELECT ic_id FROM tblPersonInterests
WHERE New_ID = @New_ID)

 

[wyrd]

Am I correct in assuming that youre trying to build a dynamic SQL statement? If its not working as intended, try using the EXEC() command;

EXEC(SELECT * FROM + @some_table + WHERE stuff=1)

From http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409

"Using EXECUTE with a Character String
Use the string concatenation operator (+) to create large strings for dynamic execution. Each string expression can be a mixture of Unicode and non-Unicode data types.

Although each [N] tsql_string or @string_variable must be less than 8,000 bytes, the concatenation is performed logically in the SQL Server parser and never materializes in memory. For example, this statement never produces the expected 16,000 concatenated character string:

EXEC(name_of_8000_char_string + another_name_of_8000_char_string)

Statement(s) inside the EXECUTE statement are not compiled until the EXECUTE statement is executed.

Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this example, the database context is master:

USE master EXEC ("USE pubs") SELECT * FROM authors"

 

[LindaC]

The following will return the parameter you pass it as New_ID, and the remaining information from your database. Is that what you want to do?

SELECT @New_ID AS New_ID, IC_ID, Mod_Date, Last_Changer FROM ......