sql insert
declare @x int
set @x = 7000145
INSERT INTO spreadsheet
SELECT
(select dateordered from orders where orderid = @x),
(select orderid from orders where orderid = @x),
(select internalsku from products where productid IN (select productid from orderitems left join orders on orderitems.orderid = orders.orderid where orderitems.orderid = @x)),
(select quantity from orderitems left join orders on orderitems.orderid = orders.orderid where orderitems.orderid = @x),
(select shipfirstname, shiplastname from orders where orderid = @x),
(select shipaddress1 from orders where orderid = @x),
(select shipaddress2 from orders where orderid = @x),
(select shipzip from orders where orderid = @x),
(select shipstate, shipcountry from orders where orderid = @x),
(select shipfirstname, shiplastname from orders where orderid = @x),
(select shipphone1 from orders where orderid = @x)
select * from spreadsheet
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
this is how I created table spreadsheet
create table spreadsheet(
[id] [int] IDENTITY(1,1) NOT NULL,
[dateordered] [datetime] NULL,
[orderid] [int] NULL,
[internalsku] [varchar](20) NULL,
[quantity] [int] NULL,
[deliveryservicecode] [int] NULL,
[ShipLastName] [varchar](100) NULL,
[ShipAddress1] [varchar](100) NULL,
[ShipAddress2] [varchar](100) NULL,
[ShipZip] [varchar](15) NULL,
[ShipLastName] [varchar](100) NULL,
[ShipPhone1] [varchar](50) NOT NULL,
) ON [PRIMARY]
set @x = 7000145
INSERT INTO spreadsheet
SELECT
(select dateordered from orders where orderid = @x),
(select orderid from orders where orderid = @x),
(select internalsku from products where productid IN (select productid from orderitems left join orders on orderitems.orderid = orders.orderid where orderitems.orderid = @x)),
(select quantity from orderitems left join orders on orderitems.orderid = orders.orderid where orderitems.orderid = @x),
(select shipfirstname, shiplastname from orders where orderid = @x),
(select shipaddress1 from orders where orderid = @x),
(select shipaddress2 from orders where orderid = @x),
(select shipzip from orders where orderid = @x),
(select shipstate, shipcountry from orders where orderid = @x),
(select shipfirstname, shiplastname from orders where orderid = @x),
(select shipphone1 from orders where orderid = @x)
select * from spreadsheet
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
this is how I created table spreadsheet
create table spreadsheet(
[id] [int] IDENTITY(1,1) NOT NULL,
[dateordered] [datetime] NULL,
[orderid] [int] NULL,
[internalsku] [varchar](20) NULL,
[quantity] [int] NULL,
[deliveryservicecode] [int] NULL,
[ShipLastName] [varchar](100) NULL,
[ShipAddress1] [varchar](100) NULL,
[ShipAddress2] [varchar](100) NULL,
[ShipZip] [varchar](15) NULL,
[ShipLastName] [varchar](100) NULL,
[ShipPhone1] [varchar](50) NOT NULL,
) ON [PRIMARY]
INSERT INTO spreadsheet
SELECT o.dateordered, o.OrderID ,p.internalsku , i.Quantity, (o.shipfirstname+' '+ o.shiplastname),o.shipaddr
(o.shipstate +' '+ o.shipcountry ), shipfirstname+' '+ 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 and p.productid = 149880
SELECT o.dateordered, o.OrderID ,p.internalsku , i.Quantity, (o.shipfirstname+' '+ o.shiplastname),o.shipaddr
(o.shipstate +' '+ o.shipcountry ), shipfirstname+' '+ 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 and p.productid = 149880
ASKER
0 rows returned
p.productid = 149880
does not work because productid is not equal to 149880
p.productid = 149880
does not work because productid is not equal to 149880
ASKER
select productid from orderitems left join orders on orderitems.orderid = orders.orderid where orderitems.orderid = '7000145'
149890
149891
there are 2 productid
so 2 internalsku
in
(select internalsku from products where productid IN (select productid from orderitems left join orders on orderitems.orderid = orders.orderid where orderitems.orderid = @x)),
149890
149891
there are 2 productid
so 2 internalsku
in
(select internalsku from products where productid IN (select productid from orderitems left join orders on orderitems.orderid = orders.orderid where orderitems.orderid = @x)),
eliminate that condition from the where clause
INSERT INTO spreadsheet
SELECT o.dateordered, o.OrderID ,p.internalsku , i.Quantity, (o.shipfirstname+' '+ o.shiplastname),o.shipaddr
(o.shipstate +' '+ o.shipcountry ), shipfirstname+' '+ 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
INSERT INTO spreadsheet
SELECT o.dateordered, o.OrderID ,p.internalsku , i.Quantity, (o.shipfirstname+' '+ o.shiplastname),o.shipaddr
(o.shipstate +' '+ o.shipcountry ), shipfirstname+' '+ 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
ASKER
I inserted 12 to the 6 column which is an int
declare @x int
set @x = 7000145
INSERT INTO spreadsheet
SELECT o.dateordered, o.OrderID ,p.internalsku , i.Quantity, 12,(o.shipfirstname+' '+ o.shiplastname),o.shipaddr
(o.shipstate +' '+ o.shipcountry ), shipfirstname+' '+ 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
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'spreadsheet' can only be specified when a column list is used and IDENTITY_INSERT is ON.
could i create the table without the identity column
this is how I created table spreadsheet
create table spreadsheet(
[id] [int] IDENTITY(1,1) NOT NULL,
[dateordered] [datetime] NULL,
[orderid] [int] NULL,
[internalsku] [varchar](20) NULL,
[quantity] [int] NULL,
[deliveryservicecode] [int] NULL,
[ShipLastName] [varchar](100) NULL,
[ShipAddress1] [varchar](100) NULL,
[ShipAddress2] [varchar](100) NULL,
[ShipZip] [varchar](15) NULL,
[ShipLastName] [varchar](100) NULL,
[ShipPhone1] [varchar](50) NOT NULL,
) ON [PRIMARY]
declare @x int
set @x = 7000145
INSERT INTO spreadsheet
SELECT o.dateordered, o.OrderID ,p.internalsku , i.Quantity, 12,(o.shipfirstname+' '+ o.shiplastname),o.shipaddr
(o.shipstate +' '+ o.shipcountry ), shipfirstname+' '+ 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
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'spreadsheet' can only be specified when a column list is used and IDENTITY_INSERT is ON.
could i create the table without the identity column
this is how I created table spreadsheet
create table spreadsheet(
[id] [int] IDENTITY(1,1) NOT NULL,
[dateordered] [datetime] NULL,
[orderid] [int] NULL,
[internalsku] [varchar](20) NULL,
[quantity] [int] NULL,
[deliveryservicecode] [int] NULL,
[ShipLastName] [varchar](100) NULL,
[ShipAddress1] [varchar](100) NULL,
[ShipAddress2] [varchar](100) NULL,
[ShipZip] [varchar](15) NULL,
[ShipLastName] [varchar](100) NULL,
[ShipPhone1] [varchar](50) NOT NULL,
) ON [PRIMARY]
ASKER
correction...
this is how i created table
create table spreadsheet(
[id] [int] IDENTITY(1,1) NOT NULL,
[dateordered] [datetime] NULL,
[orderid] [int] NULL,
[internalsku] [varchar](20) NULL,
[quantity] [int] NULL,
[deliveryservicecode] [int] NULL,
[companyname] [varchar](100) NULL,
[shipaddress1] [varchar](100) NULL,
[shipaddress2] [varchar](100) NULL,
[shipzip] [varchar](15) NULL,
[contactname] [varchar](100) NULL,
[shipphone1] [varchar](50) NOT NULL,
) ON [PRIMARY]
this is how i created table
create table spreadsheet(
[id] [int] IDENTITY(1,1) NOT NULL,
[dateordered] [datetime] NULL,
[orderid] [int] NULL,
[internalsku] [varchar](20) NULL,
[quantity] [int] NULL,
[deliveryservicecode] [int] NULL,
[companyname] [varchar](100) NULL,
[shipaddress1] [varchar](100) NULL,
[shipaddress2] [varchar](100) NULL,
[shipzip] [varchar](15) NULL,
[contactname] [varchar](100) NULL,
[shipphone1] [varchar](50) NOT NULL,
) ON [PRIMARY]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
https://www.experts-exchange.com/questions/25021667/Subquery-returned-more-than-1-value.html