SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to insert top 1 and top 3 on other table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

programer
Posting Yak Master

Slovenia
215 Posts

Posted - 09/15/2013 :  07:03:17  Show Profile  Reply with Quote
Hi,

In the first table table1 I Inserted 3 rows.
Id, values
1, test1
2, test2
3, test3

in the second table2 I want to insert only row 1 and 3

I tried:
ALTER TRIGGER BetSlipEventsTrigger
ON dbo.tbl_BetSlipEvents
AFTER INSERT

AS

BEGIN

INSERT TOP (1) INTO tbl_BetSlipSystem ( BetSlipEventId )SELECT TOP 1 ID
FROM INSERTED

INSERT TOP (3) INTO tbl_BetSlipSystem ( BetSlipEventId )SELECT TOP 3 ID
FROM INSERTED


END

Not works. This code inserted 3 rows.

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
159 Posts

Posted - 09/16/2013 :  00:38:33  Show Profile  Reply with Quote
INSERT INTO tbl_BetSlipSystem SELECT ID,BetSlipEventId
FROM INSERTED
WHERE ID in (1,3)

veeranjaneyulu
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/17/2013 :  11:49:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000