copy or replicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

copy or replicate records

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-06 : 23:29:36

Guys,

I need to get the information from table1 and place it to table2
any idea guys. when i tried to do this it triple the records.

TABLE1
ItemNumber---------Price---PO-------Code-Qty
P300-1710-DROID-U--302.05--9100962--506--1
P300-1710-DROID-U--302.05--9100962--553--4
P300-1710-DROID-U--302.05--9100962--505--2

TABLE2
Itemnumber---------ESN-----------------Code-COO
P300-1710-DROID-U--268435458109959871--506--MALAYSIA
P300-1710-DROID-U--268435458111803411--553--MALAYSIA
P300-1710-DROID-U--268435458115318904--553--MALAYSIA
P300-1710-DROID-U--268435458116412601--553--MALAYSIA
P300-1710-DROID-U--268435459401812963--553--MALAYSIA
P300-1710-DROID-U--268435458114918871--505--MALAYSIA
P300-1710-DROID-U--268435459402546544--505--MALAYSIA

RESULT
ItemNumber---------Price---PO-------Code-Qty--ESN-----------------COO
P300-1710-DROID-U--302.05--9100962--506--1----268435458109959871--MALAYSIA
P300-1710-DROID-U--302.05--9100962--553--4----26843545811180341--MALAYSIA
P300-1710-DROID-U--302.05--9100962--553--4----268435458115318904--MALAYSIA
P300-1710-DROID-U--302.05--9100962--553--4----268435458116412601--MALAYSIA
P300-1710-DROID-U--302.05--9100962--553--4----268435459401812963--MALAYSIA
P300-1710-DROID-U--302.05--9100962--505--2----2268435458114918871--MALAYSIA
P300-1710-DROID-U--302.05--9100962--505--2----2268435459402546544--MALAYSIA

Select t2.itemnumber, t2.esn, t2.coo, t1.price, t1.po
from table2 t2 left outer join table1 t1
on t2.itemnumber = t1.itemnumber
and t2.code = t1.code

Thank you and regards,

Jov

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 23:35:14

if you want to add them to table2 you should be adding extra columns and doing an update


alter table table2 add Price decimal(10,2),PO int,Qty int

then do update

update t2.Price=t1.price,
t2.PO = t1.PO,
t2.Qty = t1.Qty
from table2 t2 
inner join table1 t1
on t2.itemnumber = t1.itemnumber
and t2.code = t1.code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-07 : 01:00:10

I used only temporary table. i think i dont need to alter or update.
can you check my sample result. it should be like this the result.

thanks for your reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 01:22:01

then its a matter of simple join as you gave

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-07 : 01:33:10

quote:
Originally posted by Villanuev

I used only temporary table. i think i dont need to alter or update.
can you check my sample result. it should be like this the result.

thanks for your reply.

I dont understand your question.. do u want to insert into table2 or you want to select?

Karthik
http://karthik4identity.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-07 : 01:48:33

When i tried this script, i encounter duplicate records.
eX. P300-1710-DROID-U--302.05--9100962--553 if this records from table
when i look up at table 2 i have 4 so, all 4 records will be getting.
is there any wrong with the scripts.

Select t1.itemnumber, t1.price, t1.PO, t1.code
t2.esn, t2.coo
from TABLE1 t1
Left Outer Join t1.itemnumber=t2.itemnumber
on t1.code=t2.code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 02:01:45

but thats wht your output also shows rite? or do you want only one out of them? then show what output you're expecting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-07 : 02:23:25

Thanks my friend. I think i have problem with the data itself.
there are some problem even i group already. price and po.

thanks again.

JOV

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-07 : 05:01:53

I Post a new sets of scenario if this is possible.
especially with the Code 505.

what about the qty? can be this one use to count per Itemnumber from table2.
any idea guys. thanks.

TABLE1
ItemNumber---------Price----PO-------Code-Qty
P300-1710-DROID-U---302.05--9100925--505--2
P300-1710-DROID-U---302.05--9100962--505--3
P300-1710-DROID-U---302.05--9100962--506--1
P300-1710-DROID-U---302.05--9100962--509--1

TABLE2
ItemNumber----------ESN-----------------Code
P300-1710-DROID-U--268435458113631047--505
P300-1710-DROID-U--268435458113698229--505
P300-1710-DROID-U--268435458113849947--505
P300-1710-DROID-U--268435458114918871--505
P300-1710-DROID-U--268435458115367374--505
P300-1710-DROID-U--268435458115624102--506
P300-1710-DROID-U--268435458113626085--509

RESULT
ItemNumber----------ESN-----------------Price--Code--PO-----
P300-1710-DROID-U--268435458113631047--302.05--505--9100925
P300-1710-DROID-U--268435458113698229--302.05--505--9100925
P300-1710-DROID-U--268435458113849947--302.05--505--9100962
P300-1710-DROID-U--268435458114918871--302.05--505--9100962
P300-1710-DROID-U--268435458115367374--302.05--505--9100962
P300-1710-DROID-U--268435458115624102--302.05--506--9100962
P300-1710-DROID-U--268435458113626085--302.05--509--9100962

regards,

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 05:30:05

[code]
;with cte(ItemNumber,Price,PO,Code,Totqty)
as
(
select t1.ItemNumber,t1.Price,t1.PO,t1.Code,t2.Totqty
from table1 t1
cross apply (select sum(qty) as totqty
from table1
where itemnumber = t1.itemnumber
and code = t1.code
and po <= t1.po)t2
)

select p.itemnumber,p.esn,q.price,q.code,q.po
from (select row_number() over(partition by itemnumber,code order by esn) as seq,* from table2)p
cross apply (select top 1 Price,PO,Code
from CTE
where itemnumber= p.itemnumber
and code=p.code
and Totqty >= p.seq
order by Totqty)q
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-07 : 05:49:29

Select t2.itemnumber, t2.esn, t1.price, t1.po
from TABLE2 t2 join
(select itemnumber,price,po,code,sum(QTY) as test from
TABLE1 group by itemnumber,price,po,code) t1
on t2.itemnumber = t1.itemnumber
and t2.code = t1.code

Karthik
http://karthik4identity.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 07:15:46

quote:
Originally posted by karthik_padbanaban

Select t2.itemnumber, t2.esn, t1.price, t1.po
from TABLE2 t2 join
(select itemnumber,price,po,code,sum(QTY) as test from
TABLE1 group by itemnumber,price,po,code) t1
on t2.itemnumber = t1.itemnumber
and t2.code = t1.code

Karthik
http://karthik4identity.blogspot.com/

how does this give desired output?
this would cause lots of additional rows in output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-07 : 23:15:22

Thank you guys for the reply.
Specially to you visakh16..I got you idea..Its running now. thank you very much for your help.

Btw, I need to add another table. where i need to get the month.
I insert this codes after the first cross apply. but i got an error
when i use the left outer join.

the error is incorrect syntax near the keyword left.

),

--
TransferPrice (Itemid, Customerref, SalesPrice, POMonth)
AS
(
Select
Itemid,
Customerref,
SalesPrice,
Datename(Month,ConfirmedDlv) as POMonth
FROM TABLE3 s
WHERE substring(salesid,1,2)='PS'
)

cross apply (Select top 1 Price, ReceivingPO, DefectCode
From CTE
Where itemnumber= p.itemnumber
and Defectcode=p.Defectcode
and ReturnQty >= p.seq
Left Outer Join TransferPrice as t3
On ItemNumber=t3.Itemid
and p.ReceivingPO=t3.customerref
Order by ReturnQty)q

regards,

jov

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-08 : 01:02:44

I made some adjustment with my data. Solved already.
again. thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 02:30:10

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources