CODEWITHSUNDEEP
sql-server
Pravin Damkondwar
Pravin Damkondwar

Reputation: 21

can we use aggregate functions in insert query with more than one column values?

My query is

insert into student 
values('pravin', 1990-08-06, 'hyderabad', select count(*) from student, 'male', 1990-5-3, 1989-4-6)

and I'm getting an error

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'male'.

And

insert into student 
values('pravin', 1990-08-06, select min(location) from student, 5645645646, 'male', 1990-5-3, 1989-4-6)

results in:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '5645645646'.

Table structure -

create table student
(
      StudentId int identity
      ,StudentName varchar(50)
      ,[Date Of Birth] datetime
      ,Location varchar(50)
      ,ContactNo varchar(50)
      ,Gender varchar(50)
      ,[Date Created] datetime
      ,[Date Modified] datetime
 )

SAMPLE INSERT STATEMENTS-

insert into student(StudentName,[Date Of Birth],Location,ContactNo,Gender,[Date Created],[Date Modified])
values('pravin',1990-08-06,'hyderabad',5645645646,'male',1990-5-3,1989-4-6)
 
insert into student(StudentName,[Date Of Birth],Location,ContactNo,Gender,[Date Created],[Date Modified]) 
values('John',1990-08-06,'chennai',5645645646,'male',1990-5-3,1989-4-6)

inse‌​rt into student(StudentName,[Date Of Birth],Location,ContactNo,Gender,[Date Created],[Date Modified]) 
values('Krish',1990-08-06,'banglore',5645645646,'male',1990-5-3,1989-4-6)

Upvotes: 1

Views: 7234

Answers (1)

marc_s
marc_s

Reputation: 754230

The INSERT command comes in two flavors:

(1) either you have all your values available, as literals or SQL Server variables - in that case, you can use the INSERT .. VALUES() approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
VALUES(Value1, Value2, @Variable3, @Variable4, ...., ValueN)

Note: I would recommend to always explicitly specify the list of column to insert data into - that way, you won't have any nasty surprises if suddenly your table has an extra column, or if your tables has an IDENTITY or computed column. Yes - it's a tiny bit more work - once - but then you have your INSERT statement as solid as it can be and you won't have to constantly fiddle around with it if your table changes.

(2) if you don't have all your values as literals and/or variables, but instead you want to rely on another table, multiple tables, or views, to provide the values, then you can use the INSERT ... SELECT ... approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
   SELECT
       SourceColumn1, SourceColumn2, @Variable3, @Variable4, ...., SourceColumnN
   FROM
       dbo.YourProvidingTableOrView

Here, you must define exactly as many items in the SELECT as your INSERT expects - and those can be columns from the table(s) (or view(s)), or those can be literals or variables. Again: explicitly provide the list of columns to insert into - see above.

You can use one or the other - but you cannot mix the two - you cannot use VALUES(...) and then have a SELECT query in the middle of your list of values - pick one of the two - stick with it.

UPDATE:

I tried to explain that you cannot use a SELECT in the middle of a list of VALUES() in an INSERT statement - you'll need to use the INSERT INTO ... SELECT .... style, so instead of this:

insert into student 
values('pravin', 1990-08-06, 'hyderabad', select count(*) from student, 'male', 1990-5-3, 1989-4-6)

you will need to use this:

INSERT INTO dbo.Student(provide-the-list-of-columns-here!)
   SELECT
      'pravin', '19900806', 'hyderabad', 
      COUNT(*) 
      'male', '19900503', '19890406'
  FROM 
      dbo.student

Notes:

  • always provide the list of columns you're inserting into in your INSERT statement
  • put your dates into single quotes!
  • also, I recommend to always use the ISO-8601 format - YYYYMMDD - which is the one format that works regardless of your language and dateformat settings

UPDATE #2:

You commented with this code:

insert into student(StudentName,[Date Of Birth],Location,ContactNo,Gender,[Date Created],[Date Modified]) 
values('pravin',1990-08-06,select max(location) from student,5645645646,'male',1990-5-3,1989-4-6) –

And again: no; you cannot do this! Sure it's not working - that what I've been trying to tell you all along!

If you want to use SELECT MAX(Location) .... in the middle of your INSERT, you MUST use the INSERT INTO ... SELECT ... style:

INSERT INTO dbo.student(StudentName, [Date Of Birth], Location, ContactNo, Gender, [Date Created], [Date Modified]) 
    SELECT
       'pravin', '19900806',
       MAX(location) 
       5645645646, 'male', '19900503', '19890406'
    FROM 
       dbo.student

And again: put ALL your dates into single quotes, and use the YYYYMMDD (no dashes!) format.

Upvotes: 5

Related Questions