2

I'm very very new to programming. I'm having the hardest time with a seemingly simple insert statement that I'm using a query to run. The following is exactly what I am putting in:

INSERT INTO [EMPLOYEE TABLE] 
  (list of column names surrounded by '' and separated by ,.)
VALUES 
  (list of data that I want in each column)

I can't get rid of syntax complaints at the bracketed employee table. I've tried multiple combinations. I'm also trying to get my first column to generate an automatic number. When it's time to input something in that column, what should I be inserting there? Should it be blank?

Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
winkyloCC
  • 31
  • 1
  • 2
  • 7

4 Answers4

1

I'm guessing the syntax highlighter is highlighting the wrong portion of your statement. The square brackets around the table name are fine. You don't need '''s surrounding your column names:

insert into [employee table] (firstName, lastName, isActive)
values ('Justin', 'Niessner', 1)
Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
  • Why the 1 after the last name? – winkyloCC Nov 15 '10 at 21:14
  • @user507502: that's the value for the "isActive" column, which - guessed from its name - is most likely a BIT column - therefore values `0` and `1` are valid - no quotes around those needed – marc_s Nov 15 '10 at 21:21
1
  1. Check that the appropriate database is selected in the "Available Databases" dropdown
  2. Specify the schema the table exists in - IE dbo

Otherwise, specify three part naming notation:

INSERT INTO database_name.schema_name.table_name
  ...

IE:

INSERT INTO database_name.dbo.[EMPLOYEE TABLE] 
  (csv list of columns)
VALUES
  (csv list of values)

The column names don't need to be enclosed in single quotes, but the values that are string based do. I recommend not naming columns (or tables) to use spaces, because they require using hard brackets notation.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • I think you might want to change the text in parentheses after VALUES. It isn't a list of columns, but rather a list of values. – DOK Nov 15 '10 at 21:08
  • That's what I meant. For an employee table I want names and addresses as values. – winkyloCC Nov 15 '10 at 21:17
0

Example Insert statement based on the following table

Create Table dbo.Test
(
FirstName varchar(max),
LastName varchar(max)
)

Insert Into dbo.Test (FirstName, LastName)
Values ('John', 'Doe')

Or you can use select to insert data

Insert Into dbo.Test (FirstName, LastName)
Select FirstName,
       LastName
From dbo.Employees
codingbadger
  • 42,678
  • 13
  • 95
  • 110
  • varchar (max) is abad choice for data this small. It is a poor practice to use this for fields which are not long portions of text as there are indexing problems with it. – HLGEM Nov 16 '10 at 21:28
  • @HLGEM - the data type are for example purposes only. Maybe I should have made that clear in my answer. The OP didnt provide any sample DDL after all. – codingbadger Nov 16 '10 at 22:39
0

For the automatic table numbering, ensure that you assign the IDENTITY property to the column.

CREATE TABLE [EMPLOYEE TABLE]
(ID int IDENTITY (1, 1)...  <<other columns>>

Then you want to exclude the column from the INSERT statement. The value will be assigned when you don't include information for that column in the INSERT.

bobs
  • 21,844
  • 12
  • 67
  • 78
  • Can I use that same statement as an alter statement on a table thats already been created? – winkyloCC Nov 16 '10 at 00:55
  • Unfortunately, you can't take advantage of the auto-numbering of the IDENTITY property with an UPDATE statement. In fact, you'll get an error if you try to change the value in an IDENTITY column. It's not impossible to alter the data, it's just going to be a lot of work. – bobs Nov 16 '10 at 01:10
  • I tried the above statement just as it looks but got a syntax error between the ) and the ... – winkyloCC Nov 16 '10 at 01:41