Need this complicated query

Question

Need this complicated query

maxlezious

SSCommitted

Points: 1648
More actions
March 7, 2013 at 9:02 am

#275004

Hi,
I need a query which will have an input parameter @SerNum1, the query will copy all the ParamNum, ParamVal, Lock and Id of that serial number.
Now I will send in a new @SerNum2 and this new SerNum2 will be inserted into the same table, column and all that copied data of the first SerNum1 will be pasted next to the new SerNum2.
Please look at the attached PNG file for the structure of the table and cloumns.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

So, in essence, given @SerNum1 (let us call it A) and @SerNum2 (call it B) you want to insert @SerNum2 with all the values of @SerNum1. Is this correct?

maxlezious SSCommitted Points: 1648 More actions · Answer 2

maxlezious

SSCommitted

Points: 1648

March 7, 2013 at 9:49 am

#1594539

Thats Correct.

Thanks and Regards

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Start with this:

create table #Serials (

SerNum varchar(36),

ParamNum int,

ParamValue varchar(32),

Lock int,

Id int

);

insert into #Serials(

SerNum,

ParamNum,

ParamValue,

Lock,

Id

)

select

@SerNum2,

s.ParamNum,

s.ParamValue,

s.Lock,

s.Id

from

#Serials s

where

s.SerNum = @SerNum1;

maxlezious SSCommitted Points: 1648 More actions · Answer 4

Thanks for the quick response Lynn. I already have a table called tblConfig, I think I need a stored procedure or some sort of a cursor which will copy the values of @SerNum1 and insert a new @SerNum2 with all the copied values in the existing table tblConfig, column: SerNum

Thanks for your help again.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

harleen.suri (3/7/2013)
Thanks for the quick response Lynn. I already have a table called tblConfig, I think I need a stored procedure or some sort of a cursor which will copy the values of @SerNum1 and insert a new @SerNum2 with all the copied values in the existing table tblConfig, column: SerNum
Thanks for your help again.

No cursor, no loops. Just change my temp table name to your table name.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

As a stored proc:

create procedure dbo.CopySerial(

@pSourceSerNum varchar(36), -- or whatever data type it is

@pTargetSerNum varchar(36) -- again change to match your data types

)

as

insert into dbo.tblConfig(