SELECT INTO, but specifiying a value as well.

[aBill]

I need to select all of the rows in a given table that match a criteria, and them add them back onto the same table while specifying a third value.

It's a three column table (a, b, c) and all three make the key.

I need to select a, b, where c=1 and append them to the same table while making c=2 for each.

The actual new value for C is being passed in via a stored procedure input parameter. Can anyone help me out on this one? I could just bring the tbal into my application, loop through it and insert the new rows, but i thought that there might be a better pure SQL/DTS way to go about it.

Thanks in Advance.

 

[gmmastros]

Something like this should do the trick.

Insert Into <Table> (a,b,c)
Select a,b,@NewValueForC From <table> Where c = @OldValueOfC

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[aBill]

You would think so, wouldn't you. I have tried it, and SQL is screaming... giving me an "ADO Error: Syntax error or access violation" error.

Here is the actualy code.

SELECT document_groups_members.group_id, document_groups_members.document_id, @job_id
FROM document_groups_members INNER JOIN
documents ON document_groups_members.document_id = documents.document_id
WHERE (documents.default_job_document = 'Y') AND IS NULL(document_group_members.job_id)

 

[JamesLean]

WHERE documents.default_job_document = 'Y'
  AND document_group_members.job_id IS NULL

--James

 

[aBill]

yeah, thanks, noticed that one. The SQL analyser hashed my code pretty good hopping between it and VS.NET.

I'll post back if it's still messed up.