INSERT with many rows.

[Bruno Mermer.]

Hello, beauty?

Kara, I'm trying to insert multiple records in the database with only
one INSERT, but it is giving the following error Msg 102, Level 15,
State 1, Line 2
Incorrect syntax near ','. Have googled but can not find a solution or
where the error might be. Just below the SQL I'm using.


INSERT INTO estados (sigla, nome) VALUES

('AC', 'Acre'),

('AL', 'Alagoas'),

('AM', 'Amazonas')


Regards.
Bruno Leonardo Mermer.

[Fred.]

As I understand it, you can only insert one row using the VALUES
clause and you need three separte insert statements to do the job.

Fred/

[Prasad Naik]

Hello Bruno,

 

Please specify SqL server version which you using ... Below Mention INSERT statement working in SQL server 2008...

 

~Prasad

--
You received this message because you are subscribed to the Google Groups "SQL Server" group.
To post to this group, send email to sql-s...@googlegroups.com.
To unsubscribe from this group, send email to sql-server+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sql-server?hl=en.

--
प्रसाद गो. नाईक
मो. नं. ९९६०६३५११८
जय हिंद, जय महाराष्ट्र

[Fábio Pinheiro]

Cara, do jeito que voce quer nao dá.  só em postgres e ainda 8.3 ou superior.

a solucao, se vc nao tem os dados em outra tabela é fazer um insert por vez.

 

só por curiosidade.  qual o problema em fazer vários inserts ??   é só coloca-los todos dentro de uma unica transacao.

 

2011/4/12 Fred. <ghrno-...@yahoo.com>

--
You received this message because you are subscribed to the Google Groups "SQL Server" group.
To post to this group, send email to sql-s...@googlegroups.com.
To unsubscribe from this group, send email to sql-server+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sql-server?hl=en.

--
Fábio Pinheiro
msn: fpin...@bol.com.br
ICQ: 10930673

[new DBA]

try this


USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

[Greg Grater]

Bruno -

To insert multiple records at one time, they must be in the form of a result
set from another query. Here, I've used a 'UNION ALL' clause to create a
multi-record result set. Hope this helps...

CREATE TABLE estados (sigla VARCHAR(MAX), nome VARCHAR(MAX))

INSERT INTO estados (sigla, nome)

SELECT 'AC' AS sigla, 'Acre' AS nome UNION ALL
SELECT 'AL', 'Alagoas' UNION ALL
SELECT 'AM', 'Amazonas'

DROP TABLE estados

Greg Grater

Fred/

--