[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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]
0
rgb192
Asked:
rgb192
  • 5
  • 4
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
INSERT INTO spreadsheet
SELECT  o.dateordered, o.OrderID ,p.internalsku , i.Quantity, (o.shipfirstname+' '+ o.shiplastname),o.shipaddress1,o.shipaddress2, o.shipzip,
(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
0
 
rgb192Author Commented:
0 rows returned


p.productid = 149880
does not work because productid is not equal to 149880

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
rgb192Author Commented:
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)),
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
eliminate that condition from the where clause

INSERT INTO spreadsheet
SELECT  o.dateordered, o.OrderID ,p.internalsku , i.Quantity, (o.shipfirstname+' '+ o.shiplastname),o.shipaddress1,o.shipaddress2, o.shipzip,
(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  
0
 
rgb192Author Commented:
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.shipaddress1,o.shipaddress2, o.shipzip,
(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]
0
 
rgb192Author Commented:
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]
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
its not the issue with the Identity column, you seems like trying to insert one more column which is not in the table definition  (the countryname )
0
 
rgb192Author Commented:
thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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