CODEWITHSUNDEEP
sqlnetezzasquirrel-sql
user1408672
user1408672

Reputation:

Inserting multiple rows into a table using a single INSERT statement

I have no idea why I cannot insert values into my table using this syntax. I can insert one row but I cannot insert multiple rows. 

CREATE TABLE T1 (
             ID BIGINT NULL,
             CAT VARCHAR(255) NULL,
             M_ID BIGINT NULL,
             T_CAT  VARCHAR(255) NULL,
             NUM BIGINT NULL) 

    INSERT INTO T1
    VALUES
    (32,'Math',945,'Red',2),
    (6,'English',232,'Blue',2)

Upvotes: 1

Views: 44365

Answers (6)

user3389572
user3389572

Reputation: 381

Netezza supports bulk uploading in SQL. The non-standard EXTERNAL TABLE replaces the regular table with a CSV flat-file. This can be used inside an INSERT...SELECT... statement.

Notice the USING clause has no commas separating terms (I got bit by that). Also, a PIPE delimiter and ODBC are set because I use pyODBC. JAVA is another REMOTESOURCE. The skiprows 1 term discards the header.

INSERT INTO NetezzaTable
SELECT *
FROM EXTERNAL 'C:\\temp\\Your.csv'
USING (
delimiter '|'
REMOTESOURCE 'ODBC'
LOGDIR 'C:\\temp'
skiprows 1
maxerrors 0
fillrecord);

Upvotes: 0

A Null Pointer
A Null Pointer

Reputation: 2277

As most of the above answers have correctly explained that Netezza does not support the VALUES clause as it belongs to SQL-92 class which NZ is unfamiliar with, you could as well use the nzload tool shipped with Netezza clients to load your data at one shot.

The command to load data from a CSV file into a NZ table would be :

nzload -host <host> -u <username> -pw <password> -db <db_name> -t <table_name> -delim ',' -df <data_file>

Upvotes: 0

Toby
Toby

Reputation: 7554

This particular error is because you didn't terminate your CREATE TABLE statement with a semicolon. Netezza requires statements to be terminated, it doesn't intelligently parse multi-statement queries.

CREATE TABLE T1 (
         ID BIGINT NULL,
         CAT VARCHAR(255) NULL,
         M_ID BIGINT NULL,
         T_CAT  VARCHAR(255) NULL,
         NUM BIGINT NULL); 

INSERT INTO T1
VALUES
(32,'Math',945,'Red',2),
(6,'English',232,'Blue',2);

It's also probable that the next error you encounter is going to be Gordon's response, since NZ doesn't really support any of the advanced VALUES clause options.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I don't think that all databases support inserting multiple rows using the VALUES statement. You can use separate insert statements:

INSERT INTO T1 VALUES  (32,'Math',945,'Red',2);
INSERT INTO T1 VALUES  (6,'English',232,'Blue',2);

Or, you could use a SELECT syntax instead:

INSERT INTO T1
    select 32,'Math',945,'Red',2 union all
    select 6,'English',232,'Blue',2

(Note: I used SQL Server syntax for getting constants out. You might need to add something like "from dual".)

Finally, I fully agree with other posters that putting the list of columns after the table name is such good practice that you should do it automatically. Well, another good practice is to have an auto-incremented id column, and if you have one of those, you need the list of columns.

However, not having the list should not be causing your problem.

Upvotes: 1

Stefan
Stefan

Reputation: 114248

Your syntax is correct. However, inserting multiple rows is a SQL-92 feature. Seems like your database doesn't support it.

Upvotes: 4

juergen d
juergen d

Reputation: 204924

Try explicitly defining the column you want to insert into:

INSERT INTO T1 (ID, CAT, M_ID, T_CAT, NUM)
VALUES
(32,'Math',945,'Red',2),
(6,'English',232,'Blue',2)

See this working example

Upvotes: 1

Related Questions