Usuário com melhor resposta
INSERT dentro de clausula WITH

Pergunta
-
Eu basicamente aprendi SQL com PostgreSQL, estou pela primeira vez fazendo uso do SQL Server e não consegui descobrir como fazer isso nele.
Digamos que eu tenha duas tabelas. A primeira é uma tabela genérica de transações (vendas, locações, reservas, etc.)
CREATE TABLE [dbo].[Transactions] ( [id] BIGINT NOT NULL, [client] INT NOT NULL, [functionary] INT NOT NULL, [dateTime] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, [value] MONEY NOT NULL, [typeTransaction] TINYINT NOT NULL, CONSTRAINT [PK_Transactions] PRIMARY KEY ([id]), CONSTRAINT [FK_Transactions_ToClients] FOREIGN KEY ([client]) REFERENCES [Clients]([person]), CONSTRAINT [FK_Transactions_ToFunctionaries] FOREIGN KEY ([functionary]) REFERENCES [Functionaries]([person]), CONSTRAINT [CK_Transactions_value] CHECK ([value] >= 0), CONSTRAINT [CK_Transactions_typeTransaction] CHECK ([typeTransaction] > 0 AND [typeTransaction] < 5) )
A segunda tabela é uma "tabela filha" chamada [Sales]. Ou seja, [Sales] "extende" a tabela [Transaction], simulando uma herança ([Sales] herda de [Transactions]).
CREATE TABLE [dbo].[Sales] ( [transact] BIGINT NOT NULL, [status] TINYINT NOT NULL DEFAULT 1, CONSTRAINT [PK_Sales] PRIMARY KEY ([transact]), CONSTRAINT [CK_Sales_status] CHECK ([status] > 0 AND [status] < 4), CONSTRAINT [FK_Sales_ToTransactions] FOREIGN KEY ([transact]) REFERENCES [Transactions]([id]) )
Estou tentando criar uma store procedure para fazer inserções de [Sales], ou seja, a SP deve receber como parâmetro os valores de [Transactions]. Deve então primeiro inserir esses valores na tabela [Transactions] e depois pegar o identificador e fazer uma inserção em [Sales].
Em PostgreSQL eu resolvia dessa forma:
WITH trans AS ( INSERT INTO public."Transactions" ( client, functionary, dateTime, value ) VALUES ( spp_client, spp_functionary,spp_dateTime,spp_value ) RETURNING id ) INSERT INTO public."Sales"( transact ) SELECT t.id FROM trans AS t;
Tentei fazer o mesmo em SQL Server mas estou recebendo erros de sintaxe do editor do VS.
CREATE PROCEDURE [dbo].[insertSale] @spp_client AS int, @spp_functionary AS int, @spp_dateTime AS datetime, @spp_value AS money AS BEGIN WITH trans AS ( INSERT INTO [dbo].[Transactions] ( [client], [functionary], [dateTime], [value] ) VALUES ( @spp_client, @spp_functionary,@spp_dateTime,@spp_value ) OUTPUT INSERTED.id ) INSERT INTO [dbo].[Sales]( [transaction] ) SELECT t.id FROM trans AS t; END
Estou recebendo erro de sintaxe no INSERT dentro do WITH e no OUTPUT.
Respostas
-
Deleted
- Marcado como Resposta Matheus S. Silva quinta-feira, 7 de julho de 2016 00:05
-
Deleted
- Marcado como Resposta Matheus S. Silva terça-feira, 5 de julho de 2016 19:13
Todas as Respostas
-
Deleted
- Marcado como Resposta Matheus S. Silva terça-feira, 5 de julho de 2016 19:13
-
Sua observação é válida, realmente eu havia esquecido de declarar o id como identity. Mas não é esse o fato que está ocasionando o erro de sintaxe no meu caso.
Seu exemplo parece dar certo, porém fiquei curioso pelo motivo desse erro de sintaxe. -
Deleted
- Marcado como Resposta Matheus S. Silva terça-feira, 5 de julho de 2016 19:13
- Não Marcado como Resposta Matheus S. Silva terça-feira, 5 de julho de 2016 19:13
-
-
Matheus,
Porque você esta criando um CTE para realizar o Insert?
Não seria mais fácil utilizar diretamente o comando Insert?
Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
-
Boa tarde,
Matheus, acho que você poderia utilizar a função Scope_Identity() para obter o ID da linha inserida:
https://msdn.microsoft.com/pt-br/library/ms190315(v=sql.110).aspx
Assinatura: http://www.imoveisemexposicao.com.br
-
Matheus,
Porque você esta criando um CTE para realizar o Insert?
Não seria mais fácil utilizar diretamente o comando Insert?
Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
N verdade eu já modifiquei o código seguindo a dica do José Diz:
CREATE TYPE [dbo].[ArrayOfIdsType] AS TABLE ( Id INT ); GO CREATE PROCEDURE [dbo].[insertSale] @spp_client AS int, @spp_functionary AS int, @spp_dateTime AS datetime, @spp_value AS money, @spp_products AS [dbo].[ArrayOfIdsType] READONLY AS BEGIN DECLARE @transid AS BIGINT; BEGIN TRY BEGIN TRANSACTION INSERT INTO [dbo].[Transactions] ( [client], [functionary], [dateTime], [value] ) VALUES ( @spp_client, @spp_functionary,@spp_dateTime,@spp_value ); SET @transid = SCOPE_IDENTITY(); INSERT INTO [dbo].[Sales]( [transact] ) VALUES (@transid); INSERT INTO [dbo].[TransactionsProducts] VALUES (@transid, (SELECT Id FROM @spp_products)); COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION THROW END CATCH END
Mas ainda preciso testar essa procedure para saber se vai se comportar como eu espero. Em especial no ultimo insert, onde a ideia é inserir vários valores na tabela [dbo].[TransactionsProducts].
CREATE TABLE [dbo].[TransactionsProducts] ( [transact] BIGINT NOT NULL, [product] INT NOT NULL, CONSTRAINT [PK_TransactionsProducts] PRIMARY KEY ([transaction], [product]), CONSTRAINT [FK_TransactionsProducts_ToTransactions] FOREIGN KEY ([transaction]) REFERENCES [Transactions]([id]), CONSTRAINT [FK_TransactionsProducts_ToProduct] FOREIGN KEY ([product]) REFERENCES [Products]([id]) )
-
Experimente deixar esse Insert da seguinte forma:
INSERT INTO [dbo].[TransactionsProducts] SELECT @transid, Id FROM @spp_products;
Espero que ajude
Assinatura: http://www.imoveisemexposicao.com.br
-
Deleted
- Marcado como Resposta Matheus S. Silva quinta-feira, 7 de julho de 2016 00:05