3

I'm trying to drop and recreate a table with a new schema in SQL Server, and then insert some test data into it to validate that it's working correctly. I have the following DDL:

CREATE TABLE [dbo].[Product](
    [ID] int IDENTITY(1,1) PRIMARY KEY,
    [Name] VARCHAR(100) NOT NULL,
    [Description] VARCHAR(200) NULL,
    [Modified] DATETIME NOT NULL DEFAULT(GETUTCDATE()),
    [ModifiedBy] VARCHAR(32) NOT NULL
);

and the following insert statement:

INSERT INTO [dbo].[Product]([Name], [Description], [Modified], [ModifiedBy])
VALUES('Test', 'Description', GETUTCDATE(), 'Me');

Whenever I attempt to insert the aforementioned row, I get an error:

Msg 207, Level 16, State 1, Line 38 Invalid column name 'Name'.

Msg 207, Level 16, State 1, Line 38 Invalid column name 'Description'.

I know the table is there since I can SELECT * FROM Product and get an empty result set with the correct columns and no errors... I just can't insert into it for some reason. Any help would be greatly appreciated!

Benjamin U.
  • 580
  • 1
  • 3
  • 16
  • 1
    Please use fully qualified names - most likely it is table from different schema/database – Lukasz Szozda Feb 15 '19 at 23:13
  • 3
    If you are trying to drop and recreate it and the original table didn't have these columns you will need to divide it into batches. You can't have the `DROP`, `CREATE` and `INSERT` referencing the new columns all in the same batch – Martin Smith Feb 18 '19 at 18:15

4 Answers4

3

As per Martin's answer, I did some more research and he is correct. The schema manipulation needs to be done in separate batches (I simply added a GO after dropping my old table and after creating my new one with a different schema). A more detailed answer can be found here: "Invalid column name" error when calling insert after table created

Benjamin U.
  • 580
  • 1
  • 3
  • 16
1

Maybe try refreshing the cache. Easy way is to press Ctrl + shift + R

Or 

Edit > IntelliSense > Refresh Local Cache

If doesnt work, then use qualified names [dbo].table or username.tablename etc

More here: http://msdn.microsoft.com/en-us/library/ms174205.aspx

Gauravsa
  • 6,330
  • 2
  • 21
  • 30
  • An upvote because this actually helped me get a correct error message referencing the correct line (refreshing the cache). – Joe Dyndale May 06 '20 at 22:29
0

For me, your query looks good, and i test it successfully.

Try adding schema name and put all column name inside brackets:

CREATE TABLE [dbo].[Product](
    [ID] int IDENTITY(1,1) PRIMARY KEY,
    [Name] VARCHAR(100) NOT NULL,
    [Description] VARCHAR(200) NULL,
    [Modified] DATETIME NOT NULL DEFAULT(GETUTCDATE()),
    [ModifiedBy] VARCHAR(32) NOT NULL
);

INSERT INTO [dbo].[Product]([Name], [Description], [Modified], [ModifiedBy])
VALUES('Test', 'Description', GETUTCDATE(), 'Me');
Hadi
  • 36,233
  • 13
  • 65
  • 124
-1

Don't do anything just try to insert (' ')single quotes instead of (") quotes.. or if you insert data with ('')single quotes try for (") double quotes it's really helping you.

Shubham Bhavsar
  • 1