quote:

---

Originally posted by programer

I tried to use SQL this works:

INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event1');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event2');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event3');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event4');
INSERT INTO tbl_BetSlipEvents (Event)
VALUES('event5');


INSERT INTO tbl_BetSlipSystem (BetSlipEventId)
Select id from
(Select id,row_number() over(order by id) as Number from tbl_BetSlipEvents) as a
where a.Number=1 or a.Number=3

But If I used trigger not works (inserted 5 rows):

ALTER TRIGGER BetSlipEventsTrigger
ON dbo.tbl_BetSlipEvents
AFTER INSERT

AS

Insert into tbl_BetSlipSystem(BetSlipEventId)
Select Id from
(Select row_number() over(order by id) as Number, Id from inserted) as a
Where a.Number=1


Where is the problem?

---

quote:

---

Originally posted by SwePeso

ALTER TRIGGER	dbo.BetSlipEventsTrigger
ON		dbo.tbl_BetSlipEvents
AFTER		INSERT,
		UPDATE,
		DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
	SELECT	ID,
		ROW_NUMBER() OVER (ORDER BY ID) AS rn
	FROM	dbo.tbl_BetSlipEvents
)
MERGE	dbo.tbl_BetSlipSystem AS tgt
USING	(
		SELECT	ID
		FROM	cteSource
		WHERE	rn IN (1, 3)
	) AS src
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					BetSlipEventID
				)
			VALUES	(
					src.ID
				)
WHEN	NOT MATCHED BY	SOURCE
		THEN	DELETE;

---

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

---

ALTER TRIGGER	dbo.BetSlipEventsTrigger
ON		dbo.tbl_BetSlipEvents
AFTER		INSERT,
		UPDATE,
		DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
	SELECT	ID,
		ROW_NUMBER() OVER (ORDER BY ID) AS rn
	FROM	dbo.tbl_BetSlipEvents
)
MERGE	dbo.tbl_BetSlipSystem AS tgt
USING	(
		SELECT	ID
		FROM	cteSource
		WHERE	rn IN (1, 3)
	) AS src ON src.ID = tgt.BetSlipEventID
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					BetSlipEventID
				)
			VALUES	(
					src.ID
				)
WHEN	NOT MATCHED BY	SOURCE
		THEN	DELETE;

quote:

---

Originally posted by SwePeso

Sorry, my bad.

ALTER TRIGGER	dbo.BetSlipEventsTrigger
ON		dbo.tbl_BetSlipEvents
AFTER		INSERT,
		UPDATE,
		DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
	SELECT	ID,
		ROW_NUMBER() OVER (ORDER BY ID) AS rn
	FROM	dbo.tbl_BetSlipEvents
)
MERGE	dbo.tbl_BetSlipSystem AS tgt
USING	(
		SELECT	ID
		FROM	cteSource
		WHERE	rn IN (1, 3)
	) AS src [red]ON src.ID = tgt.BetSlipEventID

WHEN NOT MATCHED BY TARGET
THEN INSERT (
BetSlipEventID
)
VALUES (
src.ID
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;[/code]

---

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

---

quote:

---

Originally posted by SwePeso

See edited response 09/17/2013 : 13:41:22

---

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

---

quote:

---

Originally posted by SwePeso

Only if you insert them at ONCE! In ONE batch.

---

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

---

quote:

---

Originally posted by SwePeso

INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), 'event3'), ('event4'), ('event5');

---

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

---

quote:

---

Originally posted by programer

Yes I tried and not works.

CREATE TABLE [dbo].[tbl_BetSlipEvents](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Event] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_BetSlipEvents] PRIMARY KEY CLUSTERED
(

CREATE TABLE [dbo].[tbl_BetSlipSystem](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BetSlipEventId] [int] NULL,
CONSTRAINT [PK_tbl_BetSlipSystem] PRIMARY KEY CLUSTERED



Trigger:
USE [test_events]
GO
/****** Object: Trigger [dbo].[BetSlipEventsTrigger] Script Date: 09/18/2013 00:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BetSlipEventsTrigger]
ON [dbo].[tbl_BetSlipEvents]
AFTER INSERT,
UPDATE,
DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
SELECT ID,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM dbo.tbl_BetSlipEvents
)
MERGE dbo.tbl_BetSlipSystem AS tgt
USING (
SELECT ID
FROM cteSource
WHERE rn IN (1, 3)
) AS src ON src.ID = tgt.BetSlipEventID
WHEN NOT MATCHED BY TARGET
THEN INSERT (
BetSlipEventID
)
VALUES (
src.ID
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;



Insert:
INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), ('event3'), ('event4'), ('event5');


I got:
tbl_BetSlipEvents:
ID Event
128 event1
129 event2
130 event3
131 event4
132 event5
133 event1
134 event2
135 event3
136 event4
137 event5



tbl_BetSlipSystem:
64 128
65 130



I need to get:
64 133
65 135

This is because I inserted 5 new values and my row is 1 and 3.

Check and you will see where is my problem.

quote:

---

Originally posted by SwePeso

INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), 'event3'), ('event4'), ('event5');

---

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

---

---

quote:

---

Originally posted by programer

Works now. Inserted was missing

quote:

---

Originally posted by programer

Yes I tried and not works.

CREATE TABLE [dbo].[tbl_BetSlipEvents](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Event] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_BetSlipEvents] PRIMARY KEY CLUSTERED
(

CREATE TABLE [dbo].[tbl_BetSlipSystem](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BetSlipEventId] [int] NULL,
CONSTRAINT [PK_tbl_BetSlipSystem] PRIMARY KEY CLUSTERED



Trigger:
USE [test_events]
GO
/****** Object: Trigger [dbo].[BetSlipEventsTrigger] Script Date: 09/18/2013 00:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BetSlipEventsTrigger]
ON [dbo].[tbl_BetSlipEvents]
AFTER INSERT,
UPDATE,
DELETE
AS

SET NOCOUNT ON;

WITH cteSource(ID, rn)
AS (
SELECT ID,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM dbo.tbl_BetSlipEvents
)
MERGE dbo.tbl_BetSlipSystem AS tgt
USING (
SELECT ID
FROM cteSource
WHERE rn IN (1, 3)
) AS src ON src.ID = tgt.BetSlipEventID
WHEN NOT MATCHED BY TARGET
THEN INSERT (
BetSlipEventID
)
VALUES (
src.ID
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;



Insert:
INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), ('event3'), ('event4'), ('event5');


I got:
tbl_BetSlipEvents:
ID Event
128 event1
129 event2
130 event3
131 event4
132 event5
133 event1
134 event2
135 event3
136 event4
137 event5



tbl_BetSlipSystem:
64 128
65 130



I need to get:
64 133
65 135

This is because I inserted 5 new values and my row is 1 and 3.

Check and you will see where is my problem.

quote:

---

Originally posted by SwePeso

INSERT tbl_BetSlipEvents (Event)
VALUES ('event1'), ('event2'), 'event3'), ('event4'), ('event5');

---

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

---

---

---

quote:

---

Originally posted by programer

Hi again,

your code works perfectly.

I want to add insert and different where=1,3 but not works.

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by programer

Hi again,

your code works perfectly.

I want to add insert and different where=1,3 but not works.

---

How does it not work? Do you get an error, results you did not expect or other?

Can you post your sample data and expected output?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

---

quote:

---

Originally posted by programer

Not get any error.
Insert only:
WITH cteSource(ID,BetSlipDetailId, rn)
AS (
SELECT ID,BetSlipDetailId,
ROW_NUMBER() OVER (ORDER BY ID) AS rn
FROM inserted
)
MERGE dbo.tbl_BetSlipSystem AS tgt
USING (
SELECT ID,
BetSlipDetailId
FROM cteSource
WHERE rn IN (1, 2, 3)
) AS src ON src.ID = tgt.BetSlipEventID
WHEN NOT MATCHED BY TARGET
THEN INSERT (BetSlipDetailID,
BetSlipEventID,
SystemBet
)
VALUES (
src.BetSlipDetailId,
src.ID,
'1,3'
);



Code:
WITH cteSource2(ID,BetSlipDetailId, rn)
Like it does not exist.

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by programer

Hi again,

your code works perfectly.

I want to add insert and different where=1,3 but not works.

---

How does it not work? Do you get an error, results you did not expect or other?

Can you post your sample data and expected output?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

---

---