Multi-row INSERTs

Database
Fan

Multi-row INSERTs

In some SQL dialects support multi-row INSERTs without using SELECT. For example:

Insert into CALL_DETAILS (id, start_time, comments)
VALUES (123, 12:45, “Busy signal”),
(124, 12:51, “Answering machine”),
(125, 12:58, “Hung up”);

It doesn't look like Teradata (we're using V2R.06.01) supports this.

Can someone confirm this for me?

Any alternatives (using queries rather than utilities - we only need to insert a few dozen rows).

Many thanks!

- Steve Gruverman

3 REPLIES
Junior Contributor

Re: Multi-row INSERTs

Hi Steve,
you're correct, Teradata doesn't support that syntax.

But you might submit a multistatement-request (MSR), BTEQ it's:

Insert into CALL_DETAILS (id, start_time, comments)
VALUES (123, 12:45, “Busy signal”)
;Insert into CALL_DETAILS (id, start_time, comments)
VALUES (124, 12:51, “Answering machine”)
;Insert into CALL_DETAILS (id, start_time, comments)
VALUES (125, 12:58, “Hung up”)
;

Those inserts form a MSR, because a new statement starts on the same line after the terminating semicolon.

In SQL Assistant it's running a query using F9 instead of F5.

Dieter
Fan

Re: Multi-row INSERTs

Dieter,

Thank you! It would have taken me a while to discover that. I'm not sure that our DBAs will like it any better than individual INSERTs, but its worth a try.

- Steve
Enthusiast

Re: Multi-row INSERTs

I read about a super fast way to IMPORT using Dot Net.

http://bit.do/tera14

Q: Will this be incorporated into Teradata SQL Assistant?

Thanks for any leads. I really appreciate it!

Jim