32

I'm new to SQL, (using SQL 2008 R2) and I am having trouble inserting multiple rows into a single column.

I have a table named Data and this is what I am trying

INSERT INTO Data ( Col1 ) VALUES
('Hello', 'World')

That code was taken from this question, but it, like many other examples I have found on the web uses 2 columns, I just want to use 1. What am I doing wrong?

Thanks

Community
  • 1
  • 1
Bali C
  • 30,582
  • 35
  • 123
  • 152
  • @BaliC...Are you wantint to insert "Hello" into one record and then "World" into another record...ord just enter "Hello World" into one record? – MikeTWebb Aug 03 '12 at 19:16
  • @MikeTWebb The first one, "Hello" into one and "World" into another. – Bali C Aug 03 '12 at 19:32

10 Answers10

43

To insert into only one column, use only one piece of data:

INSERT INTO Data ( Col1 ) VALUES
('Hello World');

Alternatively, to insert multiple records, separate the inserts:

INSERT INTO Data ( Col1 ) VALUES
('Hello'),
('World');
JYelton
  • 35,664
  • 27
  • 132
  • 191
42

to insert values for a particular column with other columns remain same:-

INSERT INTO `table_name`(col1,col2,col3)
   VALUES (1,'val1',0),(1,'val2',0),(1,'val3',0)
Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
user1633492
  • 415
  • 1
  • 4
  • 2
  • here...in a single query,col2 has added 3 different values as 3 different records with same values for col1 and col3 in all records – user1633492 Oct 31 '12 at 14:15
  • 2
    +1 for covering how to add multiple specific columns to multiple rows – sam-w Oct 31 '12 at 14:35
14

I believe this should work for inserting multiple rows:

INSERT INTO Data ( Col1 ) VALUES
('Hello'), ('World'),...
Holger Brandt
  • 4,324
  • 1
  • 20
  • 35
11

Another way to do this is with union:

INSERT INTO Data ( Col1 ) 
select 'hello'
union 
select 'world'
Void Ray
  • 9,849
  • 4
  • 33
  • 53
  • 1
    I'm not aware of any reason to use this method over the other answers below. I'd like to hear some rationale as to why this is the accepted answer. – Patrick Michaelsen Mar 06 '19 at 17:29
8

If your DBMS supports the notation, you need a separate set of parentheses for each row:

INSERT INTO Data(Col1) VALUES ('Hello'), ('World');

The cross-referenced question shows examples for inserting into two columns.

Alternatively, every SQL DBMS supports the notation using separate statements, one for each row to be inserted:

INSERT INTO Data (Col1) VALUES ('Hello');
INSERT INTO Data (Col1) VALUES ('World');
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
1
  INSERT INTO Data ( Col1 ) VALUES ('Hello'), ('World')
Vikram
  • 4,162
  • 8
  • 43
  • 65
1

You can also follow this method

UPDATE TABLE_NAME
SET column_name = value
WHERE id IN (id_numbers);

Example:

UPDATE report_card
SET result = true
WHERE id IN (1, 3, 5, 8, 9);
Sreekar Tammana
  • 11
  • 2
0

In that code you are inserting two column value. You can try this

   INSERT INTO Data ( Col1 ) VALUES ('Hello'),
   INSERT INTO Data ( Col1 ) VALUES ('World')
Biddut
  • 418
  • 1
  • 6
  • 17
0

Kindly ensure, the other columns are not constrained to accept Not null values, hence while creating columns in table just ignore "Not Null" syntax. eg

Create Table Table_Name(
            col1 DataType,
            col2 DataType);

You can then insert multiple row values in any of the columns you want to. For instance:

Insert Into TableName(columnname)
values
      (x),
      (y),
      (z);

and so on…

Hope this helps.

Obsidian
  • 3,719
  • 8
  • 17
  • 30
Amit Sharma
  • 53
  • 1
  • 7
-3
INSERT INTO hr.employees (location_id) VALUE (1000) WHERE first_name LIKE '%D%';

let me know if there is any problem in this statement.

thor
  • 21,418
  • 31
  • 87
  • 173
Praveen
  • 1