want empty fields instead of 0's
I put 0 for the blank (empty) columns and called them blank1.... blank2.. blank3...
I would like them to be empty
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]
[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,
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 would like them to be empty
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]
[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,
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
Jarrod
replace the 0's with ''
In your SELECT clause, simply replace the zeroes with NULL
zadeveloper said:
>>replace the 0's with ''
'' and NULL are not the same thing :)
>>replace the 0's with ''
'' and NULL are not the same thing :)
no they arn't, but both should work
try
DECLARE @i int;
SET @i = 1;
WHILE (@i <= 6)
BEGIN
EXEC ('UPDATE sreadsheet6 SET blank'+CAST(@i AS VARCHAR)+' = '''' WHERE blank'+CAST(@i AS VARCHAR)+' = 0')
SET @i = @i + 1;
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try this : should be faster
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
)
INSERT INTO spreadsheet6
SELECT
top 7000180
o.dateordered,null,o.OrderID,p.internalsku,i.Quantity,null,12,null,o.shiplastname,o.shipaddress1,o.shipaddress2,null,null,o.shipzip,null,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
Or like this
while @x < 7000180
BEGIN
INSERT INTO spreadsheet6
SELECT o.dateordered,NULL,o.Order
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
while @x < 7000180
BEGIN
INSERT INTO spreadsheet6
SELECT o.dateordered,NULL,o.Order
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 agree with matthewspatrick
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
)
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.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
ASKER
insert is best