• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

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] 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
1 Solution
 
zadeveloperCommented:
replace the 0's with ''
0
 
Patrick MatthewsCommented:
In your SELECT clause, simply replace the zeroes with NULL
0
 
Patrick MatthewsCommented:
zadeveloper said:
>>replace the 0's with ''

'' and NULL are not the same thing :)
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
zadeveloperCommented:
no they arn't, but both should work
0
 
tigin44Commented:
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

Open in new window

0
 
Patrick MatthewsCommented:
rgb192,

Even better is to specify your fields for the INSERT:

INSERT INTO spreadsheet6 ([dateordered], [orderid], [internalsku], [quantity], [deliveryservicecode],
      [companyname], [shipaddress1], [shipaddress2], [shipzip], [contactname], [shipphone1])
SELECT  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
where i.orderid = @x


The fields omitted from the list will simply go in as NULL.

Patrick
0
 
zadeveloperCommented:
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

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Or like this


while @x < 7000180
BEGIN
INSERT INTO spreadsheet6
SELECT  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
where i.orderid = @x
SET @x = @x +1
END
select * from spreadsheet6
0
 
zadeveloperCommented:
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

Open in new window

0
 
rgb192Author Commented:
insert is best
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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