October 30, 2018 at 3:26 pm
gjoelson 29755 - Tuesday, October 30, 2018 10:26 AMHello ,
Can someone point me in the right direction here. I'm having a hard time figuring this out.I have a field - [cust_po] [varchar](20) NULL, and get a Msg 207 Invalid column name. when trying to insert. I'm using an "insert into" select , the source field is an alpha numeric and has values such as this ... SSS-BLAN-2014
I tried changing the target field data type to all kinds including float etc. but no luck.hopefully its something simple
Thanks
Greg J
Here is an idea. post the complete error message you are getting.
October 30, 2018 at 3:29 pm
Lynn Pettis - Tuesday, October 30, 2018 3:26 PMgjoelson 29755 - Tuesday, October 30, 2018 10:26 AMHello ,
Can someone point me in the right direction here. I'm having a hard time figuring this out.I have a field - [cust_po] [varchar](20) NULL, and get a Msg 207 Invalid column name. when trying to insert. I'm using an "insert into" select , the source field is an alpha numeric and has values such as this ... SSS-BLAN-2014
I tried changing the target field data type to all kinds including float etc. but no luck.hopefully its something simple
Thanks
Greg JHere is an idea. post the complete error message you are getting.
Lynn, I did but here it is again - tks
Msg 207, Level 16, State 1, Line 7
Invalid column name 'cust_po'.
October 30, 2018 at 3:31 pm
gjoelson 29755 - Tuesday, October 30, 2018 3:29 PMLynn Pettis - Tuesday, October 30, 2018 3:26 PMgjoelson 29755 - Tuesday, October 30, 2018 10:26 AMHello ,
Can someone point me in the right direction here. I'm having a hard time figuring this out.I have a field - [cust_po] [varchar](20) NULL, and get a Msg 207 Invalid column name. when trying to insert. I'm using an "insert into" select , the source field is an alpha numeric and has values such as this ... SSS-BLAN-2014
I tried changing the target field data type to all kinds including float etc. but no luck.hopefully its something simple
Thanks
Greg JHere is an idea. post the complete error message you are getting.
Lynn, I did but here it is again - tks
Msg 207, Level 16, State 1, Line 7
Invalid column name 'cust_po'.
Okay, now post the code that is failing.
October 30, 2018 at 3:33 pm
Lynn Pettis - Tuesday, October 30, 2018 3:31 PMgjoelson 29755 - Tuesday, October 30, 2018 3:29 PMLynn Pettis - Tuesday, October 30, 2018 3:26 PMgjoelson 29755 - Tuesday, October 30, 2018 10:26 AMHello ,
Can someone point me in the right direction here. I'm having a hard time figuring this out.I have a field - [cust_po] [varchar](20) NULL, and get a Msg 207 Invalid column name. when trying to insert. I'm using an "insert into" select , the source field is an alpha numeric and has values such as this ... SSS-BLAN-2014
I tried changing the target field data type to all kinds including float etc. but no luck.hopefully its something simple
Thanks
Greg JHere is an idea. post the complete error message you are getting.
Lynn, I did but here it is again - tks
Msg 207, Level 16, State 1, Line 7
Invalid column name 'cust_po'.Okay, now post the code that is failing.
Insert into [SSS].[dbo].[CustomerOrders]
October 30, 2018 at 3:49 pm
I don't see anything that would cause the error you are getting. The only thing I noticed was a missing table alias in the WHERE clause for [co_line] but if that was a problem you would have received a different error message.
Without the tables I can't run the query. Can you post the CREATE TABLE statements for the tables involved? Don't worry about the database names as all three tables have different names.
October 30, 2018 at 4:02 pm
Lynn Pettis - Tuesday, October 30, 2018 3:48 PMI don't see anything that would cause the error you are getting. The only thing I noticed was a missing table alias in the WHERE clause for [co_line] but if that was a problem you would have received a different error message.Without the tables I can't run the query. Can you post the CREATE TABLE statements for the tables involved? Don't worry about the database names as all three tables have different names.
The Source I have attached an excel file because I cant display the datatype schema. - hope that helps.
October 30, 2018 at 4:42 pm
gjoelson 29755 - Tuesday, October 30, 2018 4:02 PMLynn Pettis - Tuesday, October 30, 2018 3:48 PMI don't see anything that would cause the error you are getting. The only thing I noticed was a missing table alias in the WHERE clause for [co_line] but if that was a problem you would have received a different error message.Without the tables I can't run the query. Can you post the CREATE TABLE statements for the tables involved? Don't worry about the database names as all three tables have different names.
This is the target ......CREATE TABLE [dbo].[CustomerOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[co_num] [varchar](20) NULL,
[cust_po] [nvarchar](22) NULL,
[co_line] [int] NULL,
[co_release] [int] NULL,
[item] [char](25) NULL,
[description] [nvarchar](55) NULL,
[u_m] [char](10) NULL,
[qty_ordered] float NULL,
[qty_ready] float NULL,
[qty_shipped] float NULL,
[CreateDate] [datetime] NULL,
[due_date] [datetime] NULL,
[ship_date] [datetime] NULL,
[cust_item] [char](25) NULL,
[release_date] [datetime] NULL,
[whse] [int] NULL,
[cust_seq] [int] NULL,
[ship_code] [int] NULL,
[customerid] [int] NULL,
[addr##2] [varchar](55) NULL,
[co_cust_num] [int] NULL,
[ship_site] [char](25) NULL,
[CreatedBy] [nvarchar](55) NULL,
[UpdatedBy] [nvarchar](55) NULL,
PRIMARY KEY CLUSTERED
(The Source I have attached an excel file because I cant display the datatype schema. - hope that helps.
Let me get this straight, you could post the table catalog, column name, and data type in a spread sheet but you couldn't post two CREATE TABLE statements which have basically the same information. Without the tables, I can't even run the code.
October 31, 2018 at 8:01 am
gjoelson 29755 - Tuesday, October 30, 2018 3:19 PMdrew.allen - Tuesday, October 30, 2018 2:47 PMgjoelson 29755 - Tuesday, October 30, 2018 2:13 PMLynn Pettis - Tuesday, October 30, 2018 1:27 PMLooks to me that cust_po may not be the column name in the table. Of course we don't know that as we can't see what you can see.Lynn,
so the [cust_po] is in the destination table below is a sample of some of the columns and specifically the Cust_po column. also I have screenshot the query and attached the files.CREATE TABLE [dbo].[CustomerOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[co_num] [varchar](20) NULL,
[cust_po] [varchar](20) NULL,
[co_line] [int] NULL,
[co_release] [int] NULL,
[item] [char](25) NULL, ........Thanks
You reference TWO fields name cust_po: one in the target table (which you've checked) and one in EES_App.dbo.co (which you haven't checked). I'm guessing that it's not a valid column in the second table.
Drew
Drew, yup it is valid in the source table , I'm using some joins and it exists in the [CO] on the join
- FROM [EES_app].[dbo].[coitem] a
left join [EES_App].[dbo].[co] B on a.co_num =b.co_num
Just because it's in the join, doesn't mean that it's in the table. The fact that you were able to get rid of the error by quoting the field name corroborates that assumption. Try running just the SELECT portion of the script.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 31, 2018 at 9:12 am
drew.allen - Wednesday, October 31, 2018 8:01 AMgjoelson 29755 - Tuesday, October 30, 2018 3:19 PMdrew.allen - Tuesday, October 30, 2018 2:47 PMgjoelson 29755 - Tuesday, October 30, 2018 2:13 PMLynn Pettis - Tuesday, October 30, 2018 1:27 PMLooks to me that cust_po may not be the column name in the table. Of course we don't know that as we can't see what you can see.Lynn,
so the [cust_po] is in the destination table below is a sample of some of the columns and specifically the Cust_po column. also I have screenshot the query and attached the files.CREATE TABLE [dbo].[CustomerOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[co_num] [varchar](20) NULL,
[cust_po] [varchar](20) NULL,
[co_line] [int] NULL,
[co_release] [int] NULL,
[item] [char](25) NULL, ........Thanks
You reference TWO fields name cust_po: one in the target table (which you've checked) and one in EES_App.dbo.co (which you haven't checked). I'm guessing that it's not a valid column in the second table.
Drew
Drew, yup it is valid in the source table , I'm using some joins and it exists in the [CO] on the join
- FROM [EES_app].[dbo].[coitem] a
left join [EES_App].[dbo].[co] B on a.co_num =b.co_numJust because it's in the join, doesn't mean that it's in the table. The fact that you were able to get rid of the error by quoting the field name corroborates that assumption. Try running just the SELECT portion of the script.
Drew
I just checked and cust_po is not used in the join, so it's actually not in the join unless it's somewhere that you didn't show.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 31, 2018 at 12:29 pm
drew.allen - Wednesday, October 31, 2018 9:12 AMdrew.allen - Wednesday, October 31, 2018 8:01 AMgjoelson 29755 - Tuesday, October 30, 2018 3:19 PMdrew.allen - Tuesday, October 30, 2018 2:47 PMgjoelson 29755 - Tuesday, October 30, 2018 2:13 PMLynn Pettis - Tuesday, October 30, 2018 1:27 PMLooks to me that cust_po may not be the column name in the table. Of course we don't know that as we can't see what you can see.Lynn,
so the [cust_po] is in the destination table below is a sample of some of the columns and specifically the Cust_po column. also I have screenshot the query and attached the files.CREATE TABLE [dbo].[CustomerOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[co_num] [varchar](20) NULL,
[cust_po] [varchar](20) NULL,
[co_line] [int] NULL,
[co_release] [int] NULL,
[item] [char](25) NULL, ........Thanks
You reference TWO fields name cust_po: one in the target table (which you've checked) and one in EES_App.dbo.co (which you haven't checked). I'm guessing that it's not a valid column in the second table.
Drew
Drew, yup it is valid in the source table , I'm using some joins and it exists in the [CO] on the join
- FROM [EES_app].[dbo].[coitem] a
left join [EES_App].[dbo].[co] B on a.co_num =b.co_numJust because it's in the join, doesn't mean that it's in the table. The fact that you were able to get rid of the error by quoting the field name corroborates that assumption. Try running just the SELECT portion of the script.
Drew
I just checked and cust_po is not used in the join, so it's actually not in the join unless it's somewhere that you didn't show.
Drew
Drew, correct I haven't used it explicitly in the join, because both tables don't have that column in common.
from
[EeS_app].[dbo].[coitem] as [a]
left outer join [EES_app].[dbo].[co] as
on [a].[co_num] = .[co_num]
do reference It in the select as b.[cust_po]
BTW the select portion works fine.
Greg-
October 31, 2018 at 12:30 pm
Lynn Pettis - Tuesday, October 30, 2018 4:42 PMgjoelson 29755 - Tuesday, October 30, 2018 4:02 PMLynn Pettis - Tuesday, October 30, 2018 3:48 PMI don't see anything that would cause the error you are getting. The only thing I noticed was a missing table alias in the WHERE clause for [co_line] but if that was a problem you would have received a different error message.Without the tables I can't run the query. Can you post the CREATE TABLE statements for the tables involved? Don't worry about the database names as all three tables have different names.
This is the target ......CREATE TABLE [dbo].[CustomerOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[co_num] [varchar](20) NULL,
[cust_po] [nvarchar](22) NULL,
[co_line] [int] NULL,
[co_release] [int] NULL,
[item] [char](25) NULL,
[description] [nvarchar](55) NULL,
[u_m] [char](10) NULL,
[qty_ordered] float NULL,
[qty_ready] float NULL,
[qty_shipped] float NULL,
[CreateDate] [datetime] NULL,
[due_date] [datetime] NULL,
[ship_date] [datetime] NULL,
[cust_item] [char](25) NULL,
[release_date] [datetime] NULL,
[whse] [int] NULL,
[cust_seq] [int] NULL,
[ship_code] [int] NULL,
[customerid] [int] NULL,
[addr##2] [varchar](55) NULL,
[co_cust_num] [int] NULL,
[ship_site] [char](25) NULL,
[CreatedBy] [nvarchar](55) NULL,
[UpdatedBy] [nvarchar](55) NULL,
PRIMARY KEY CLUSTERED
(The Source I have attached an excel file because I cant display the datatype schema. - hope that helps.
Let me get this straight, you could post the table catalog, column name, and data type in a spread sheet but you couldn't post two CREATE TABLE statements which have basically the same information. Without the tables, I can't even run the code.
Correct, don't ask - its to do with read only permissions on that particular database.
October 31, 2018 at 5:18 pm
Here is you query written as "insert into select"USE [SSSUTIL]
GO
INSERT INTO [dbo].[CustomerOrders]
([co_num]
,[cust_po]
,[co_line]
)
SELECT 38322, 'B123', 2;
GO
It doesn't matter if it's written as insert/select or insert/values, you still need to put single quotes around your strings.
November 1, 2018 at 3:12 am
gjoelson 29755 - Wednesday, October 31, 2018 12:30 PMCorrect, don't ask - its to do with read only permissions on that particular database.
You can still generate a CREATE TABLE statement from management studio. No one's asking you to run them, just please give us the CREATE TABLE statements for all three of the tables in this query
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2018 at 2:50 pm
GilaMonster - Thursday, November 1, 2018 3:12 AMgjoelson 29755 - Wednesday, October 31, 2018 12:30 PMCorrect, don't ask - its to do with read only permissions on that particular database.
You can still generate a CREATE TABLE statement from management studio. No one's asking you to run them, just please give us the CREATE TABLE statements for all three of the tables in this query
Gail,
I get an Insufficient access rights error. but I was able to request it.
So this is the [CO] table , I'm surprised to see these kind of custom data types, this is possibly the reason ?
CREATE TABLE [dbo].[co](
[type] [dbo].[CoTypeType] NULL,
[co_num] [dbo].[CoNumType] NOT NULL,
[est_num] [dbo].[EstNumType] NULL,
[cust_num] [dbo].[CustNumType] NULL,
[cust_seq] [dbo].[CustSeqType] NULL,
[contact] [dbo].[ContactType] NULL,
[phone] [dbo].[PhoneType] NULL,
[cust_po] [dbo].[CustPoType] NULL,
[order_date] [dbo].[DateType] NOT NULL,
[taken_by] [dbo].[TakenByType] NULL,
[terms_code] [dbo].[TermsCodeType] NULL,
[ship_code] [dbo].[ShipCodeType] NULL,
[price] [dbo].[AmountType] NULL,
[weight] [dbo].[WeightType] NULL,
[qty_packages] [dbo].[PackagesType] NULL,
[freight] [dbo].[AmountType] NULL,
[misc_charges] [dbo].[AmountType] NULL,
[prepaid_amt] [dbo].[AmountType] NULL,
[sales_tax] [dbo].[AmountType] NULL,
[stat] [dbo].[CoStatusType] NULL,
[cost] [dbo].[AmountType] NULL,
[close_date] [dbo].[DateType] NULL,
[freight_t] [dbo].[AmountType] NULL,
CONSTRAINT [PK_co] PRIMARY KEY CLUSTERED
(
November 1, 2018 at 2:52 pm
Joe Torre - Wednesday, October 31, 2018 5:18 PMHere is you query written as "insert into select"USE [SSSUTIL]
GO
INSERT INTO [dbo].[CustomerOrders]
([co_num]
,[cust_po]
,[co_line]
)
SELECT 38322, 'B123', 2;
GO
It doesn't matter if it's written as insert/select or insert/values, you still need to put single quotes around your strings.
Joe, not sure how I would put quotes around all the values on an insert/select since I'm select directly from a table ?
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply