Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

two values to one field

company name is firstname lastname  separated with a space
orders.shipfirstname orders.shiplastname
I would like to add those two values to that field


create table spreadsheet6(
[dateordered] [datetime] NULL,
[blank1] [varchar](50) NULL,
[orderid] [int] NULL,
[internalsku] [varchar](20) NULL,
[quantity] [int] NULL,
[blank2] [varchar](50) NULL,
[deliveryservicecode][int] NULL,
[blank3] [varchar](50) NULL,
[companyname] [varchar](100) NULL,
[shipaddress1] [varchar](100) NULL,
[shipaddress2] [varchar](100) NULL,
[blank4] [varchar](50) NULL,
[blank5] [varchar](50) NULL,
[shipzip] [varchar](15) NULL,
[blank6] [varchar](50) NULL,
[contactname] [varchar](100) NULL,
[shipphone1] [varchar](50) NULL
)

declare @x int
set @x = 7000145

while @x < 7000180
BEGIN
INSERT INTO spreadsheet6
SELECT  o.dateordered,0,o.OrderID,p.internalsku,i.Quantity,0,12,0,o.shiplastname,o.shipaddress1,o.shipaddress2,0,0,o.shipzip,0,
o.shiplastname,shipphone1
from products p
inner join orderitems i on  p.productid = i.productid
inner join orders o on o.orderid = i.orderid
where i.orderid = @x
SET @x = @x +1
END
select * from spreadsheet6
0
rgb192
Asked:
rgb192
  • 2
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:

declare @x int
set @x = 7000145

while @x < 7000180
BEGIN
INSERT INTO spreadsheet6
SELECT  o.dateordered,0,o.OrderID,p.internalsku,i.Quantity,0,12,0,o.shiplastname+' ' + ISNULL(o.shipLastName,'') ,o.shipaddress1,o.shipaddress2,0,0,o.shipzip,0,
o.shiplastname,shipphone1
from products p
inner join orderitems i on  p.productid = i.productid
inner join orders o on o.orderid = i.orderid
where i.orderid = @x
SET @x = @x +1
END
select * from spreadsheet6
0
 
rgb192Author Commented:
declare @x int
set @x = 7000145

while @x < 7000180
BEGIN
INSERT INTO spreadsheet6
SELECT  o.dateordered,0,o.OrderID,p.internalsku,i.Quantity,0,12,0,o.shiplastname+' ' + ISNULL(o.shipLastName,'') ,o.shipaddress1,o.shipaddress2,0,0,o.shipzip,0,
o.shiplastname,shipphone1
from products p
inner join orderitems i on  p.productid = i.productid
inner join orders o on o.orderid = i.orderid
where i.orderid = @x
SET @x = @x +1
END
select * from spreadsheet6


I see shiplastname
I dont see where shipfirstname is
0
 
zadeveloperCommented:

INSERT INTO spreadsheet6 ([dateordered], [orderid], [internalsku], [quantity], [deliveryservicecode],
      [companyname], [shipaddress1], [shipaddress2], [shipzip], [contactname], [shipphone1])
SELECT top 7000180  o.dateordered,o.OrderID,p.internalsku,i.Quantity,12, o.shipfirstname + ' ' + o.shiplastname,o.shipaddress1,o.shipaddress2,o.shipzip,
o.shiplastname,shipphone1
from 
        products p
        inner join orderitems i on  p.productid = i.productid
        inner join orders o on o.orderid = i.orderid
order by
        o.orderid

Open in new window

0
 
zadeveloperCommented:
or if you prefer the while loop

declare @x int
set @x = 7000145

while @x < 7000180
BEGIN
INSERT INTO spreadsheet6
SELECT  o.dateordered,0,o.OrderID,p.internalsku,i.Quantity,0,12,0,o.shipfirstname +' ' + ISNULL(o.shipLastName,'') ,o.shipaddress1,o.shipaddress2,0,0,o.shipzip,0,
o.shiplastname,shipphone1
from products p
inner join orderitems i on  p.productid = i.productid
inner join orders o on o.orderid = i.orderid
where i.orderid = @x
SET @x = @x +1
END
select * from spreadsheet6

Open in new window

0
 
rgb192Author Commented:
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now