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

sql table without identity column

this table has no identity column

create table spreadsheet3(
[dateordered] [datetime] NULL,
[orderid] [int] NULL,
[internalsku] [varchar](20) NULL,
[quantity] [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,
)




INSERT INTO spreadsheet3
SELECT  o.dateordered,o.OrderID,p.internalsku,i.Quantity,o.shiplastname,o.shipaddress1,o.shipaddress2,o.shipzip,
o.shipcountry,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 =7000145

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
0
rgb192
Asked:
rgb192
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
INSERT INTO spreadsheet3
SELECT  o.dateordered,o.OrderID,p.internalsku,i.Quantity,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 =7000145
0
 
chapmandewCommented:
Is there a trigger on the table?
0
 
tigin44Commented:
o.shipcountry column do not have a corresponding column inth destination table... that column should be removed.
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.

 
fhillyer1Commented:
you have o.shipcountry not define on the table, either remove it from the select or add it to the table definition
0
 
chapmandewCommented:
whoops....you have the country in your select list

ALWAYS SUPPLY A FIELD LIST IN YOUR INSERT STATEMENT.
0
 
tigin44Commented:
as
INSERT INTO spreadsheet3
SELECT  o.dateordered,
		o.OrderID,
		p.internalsku,
		i.Quantity,
		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 =7000145

Open in new window

0
 
rgb192Author Commented:
works
0
 
chapmandewCommented:
for the record, if this was a real sql table used in an oltp environment, your code would break as soon as a new field was added to the table.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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