INSERT dentro de clausula WITH

Question

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.

Answer 1

Deleted

Answer 2

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.

Answer 3

Deleted

Answer 4

Mesmo com suas modificações o VS ainda acusa erro de sintaxe

Answer 5

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]

Answer 6

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

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 7

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]) 
)

Answer 8

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

Answer 9

Deleted