|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Q: How to copy a row in a table...Hi!
I want to copy a row in a table, and insert it into the same table but with the difference i want to modify four fields. Why you might ask. I want to be able to copy a order and specify a new date and ordernumber. Is it possible?, How? Examples, links, directions. Anything would be most appreciated. Have a nice weekend all of you Regards Martin Arvidsson Yes, it is very possible.
I can give the answer if you can post DDL for your table. Perayu Show quote "Visual Systems AB (Martin Arvidsson)" <martin.arvids***@vsab.net> wrote in message news:OUuBSnsuFHA.908@tk2msftngp13.phx.gbl... > Hi! > > I want to copy a row in a table, and insert it into the same table but > with > the difference i want to modify four fields. Why you might ask. > > I want to be able to copy a order and specify a new date and ordernumber. > > Is it possible?, How? Examples, links, directions. Anything would be most > appreciated. > > Have a nice weekend all of you > > Regards > Martin Arvidsson > > You can use INSERT...SELECT to copy a row:
INSERT INTO Orders(OrderNumber, OrderDate, OrderData) SELECT @NewOrderNumber, @NewOrderDate, OrderData FROM Orders WHERE OrderNumber = @OldOrderNumber -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Visual Systems AB (Martin Arvidsson)" <martin.arvids***@vsab.net> wrote in message news:OUuBSnsuFHA.908@tk2msftngp13.phx.gbl... > Hi! > > I want to copy a row in a table, and insert it into the same table but > with > the difference i want to modify four fields. Why you might ask. > > I want to be able to copy a order and specify a new date and ordernumber. > > Is it possible?, How? Examples, links, directions. Anything would be most > appreciated. > > Have a nice weekend all of you > > Regards > Martin Arvidsson > > It sure helped!
I was in to that, but wasn't sure :D Thanx a lot Nice with guruz out there /Martin Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> skrev i meddelandet news:u8dl1rsuFHA.128@TK2MSFTNGP09.phx.gbl... > You can use INSERT...SELECT to copy a row: > > INSERT INTO Orders(OrderNumber, OrderDate, OrderData) > SELECT @NewOrderNumber, @NewOrderDate, OrderData > FROM Orders > WHERE OrderNumber = @OldOrderNumber > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Visual Systems AB (Martin Arvidsson)" <martin.arvids***@vsab.net> wrote in > message news:OUuBSnsuFHA.908@tk2msftngp13.phx.gbl... > > Hi! > > > > I want to copy a row in a table, and insert it into the same table but > > with > > the difference i want to modify four fields. Why you might ask. > > > > I want to be able to copy a order and specify a new date and ordernumber. > > > > Is it possible?, How? Examples, links, directions. Anything would be most > > appreciated. > > > > Have a nice weekend all of you > > > > Regards > > Martin Arvidsson > > > > > > Hmm...
Just one thought... If i want to modify the first field, and leave the rest as they are, do i have to specify each field with the select command or is there a simple way? Regards Martin Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> skrev i meddelandet news:u8dl1rsuFHA.128@TK2MSFTNGP09.phx.gbl... > You can use INSERT...SELECT to copy a row: > > INSERT INTO Orders(OrderNumber, OrderDate, OrderData) > SELECT @NewOrderNumber, @NewOrderDate, OrderData > FROM Orders > WHERE OrderNumber = @OldOrderNumber > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Visual Systems AB (Martin Arvidsson)" <martin.arvids***@vsab.net> wrote in > message news:OUuBSnsuFHA.908@tk2msftngp13.phx.gbl... > > Hi! > > > > I want to copy a row in a table, and insert it into the same table but > > with > > the difference i want to modify four fields. Why you might ask. > > > > I want to be able to copy a order and specify a new date and ordernumber. > > > > Is it possible?, How? Examples, links, directions. Anything would be most > > appreciated. > > > > Have a nice weekend all of you > > > > Regards > > Martin Arvidsson > > > > > > Yes, you'll need to specify the columns names and values. If you don't like
to do all that typing, you can script a SELECT and/or INSERT statement from the Query Analyzer object browser. Just right-click on the table and select the desired scripting option and modify the generated script as needed. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Visual Systems AB (Martin Arvidsson)" <martin.arvids***@vsab.net> wrote in message news:uwnfVXdvFHA.2076@TK2MSFTNGP14.phx.gbl... > Hmm... > > Just one thought... > > If i want to modify the first field, and leave the rest as they are, do i > have to > specify each field with the select command or is there a simple way? > > Regards > Martin > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> skrev i meddelandet > news:u8dl1rsuFHA.128@TK2MSFTNGP09.phx.gbl... >> You can use INSERT...SELECT to copy a row: >> >> INSERT INTO Orders(OrderNumber, OrderDate, OrderData) >> SELECT @NewOrderNumber, @NewOrderDate, OrderData >> FROM Orders >> WHERE OrderNumber = @OldOrderNumber >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Visual Systems AB (Martin Arvidsson)" <martin.arvids***@vsab.net> wrote > in >> message news:OUuBSnsuFHA.908@tk2msftngp13.phx.gbl... >> > Hi! >> > >> > I want to copy a row in a table, and insert it into the same table but >> > with >> > the difference i want to modify four fields. Why you might ask. >> > >> > I want to be able to copy a order and specify a new date and > ordernumber. >> > >> > Is it possible?, How? Examples, links, directions. Anything would be > most >> > appreciated. >> > >> > Have a nice weekend all of you >> > >> > Regards >> > Martin Arvidsson >> > >> > >> >> > > Let's assume you want to insert a new order using existing order id 422 as a
template. insert into Orders select getdate() as OrderDate, CustomerNum, . . . etc, from Orders where OrderID = 422 -- The additional below assumes that you are also wanting to carry over the same order detail rows from the template order to the new order. -- Get the new identity value just inserted, so we can use it below. select @ID = scope_identity() insert into OrderDetails select @ID, ProductID, ... etc. from OrderDetails where OrderID = 422 Show quote "Visual Systems AB (Martin Arvidsson)" <martin.arvids***@vsab.net> wrote in message news:OUuBSnsuFHA.908@tk2msftngp13.phx.gbl... > Hi! > > I want to copy a row in a table, and insert it into the same table but > with > the difference i want to modify four fields. Why you might ask. > > I want to be able to copy a order and specify a new date and ordernumber. > > Is it possible?, How? Examples, links, directions. Anything would be most > appreciated. > > Have a nice weekend all of you > > Regards > Martin Arvidsson > >  |
|||||||||||||||||||||||
Other interesting topics