quote:

---

Originally posted by zion99

Plz. suggest me if there is a better approach for doing this

---

quote:

---

Originally posted by zion99

but unfortunately, the 2 columns need to be there for some data measures. By means of a better approach, i was asking if a simple SQL query can solve this issue or there is no way without cursors....

---

USE tempdb
GO

IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.my_t')) BEGIN
	DROP TABLE dbo.my_t
END

CREATE TABLE dbo.my_t
	(
		  col_1			INT		NOT NULL
		, col_2			INT		NOT NULL	
		, date_of_insert	DATETIME	NOT NULL	CONSTRAINT df_my_t_date_of_insert DEFAULT GETDATE()
		, CONSTRAINT pk_my_t		PRIMARY KEY CLUSTERED	(col_1, col_2)		WITH (FILLFACTOR = 100)
		, CONSTRAINT ix_my_t_col_2_u_nc	UNIQUE NONCLUSTERED	(col_1, date_of_insert) WITH (FILLFACTOR = 100)
	)
GO	

INSERT my_t	(col_1, col_2)
SELECT	124, 1
UNION ALL
SELECT	125, 0

WAITFOR DELAY '00:00:01'

INSERT my_t	(col_1, col_2)
SELECT	124, 2

SELECT	  col_1
	, col_2
	, new	=	CASE
				WHEN new_rc = 1 THEN
					1
				ELSE
					0
				END
	, latest=	CASE
				WHEN latest_rc = 1 THEN
					1
				ELSE
					0
				END
FROM	
	(
		SELECT	  col_1
			, col_2
			, date_of_insert
			, new_rc		= ROW_NUMBER() OVER	(PARTITION BY	col_1
									ORDER BY	date_of_insert	ASC)
			, latest_rc		= ROW_NUMBER() OVER	(PARTITION BY	col_1
									ORDER BY	date_of_insert	DESC)
		FROM	dbo.my_t
	) AS rcs

SELECT	  my_t.col_1
	, my_t.col_2
	, new	=	CASE
				WHEN last_first.first_date  = date_of_insert THEN
					1
				ELSE
					0
			END
	, latest=	CASE
				WHEN last_first.last_date = date_of_insert THEN
					1
				ELSE
					0
			END
FROM	dbo.my_t
LEFT OUTER JOIN 
		(
			SELECT	  col_1
				, last_date	= MAX(date_of_insert)
				, first_date	= MIN(date_of_insert)
			FROM	dbo.my_t
			GROUP BY col_1
		) AS last_first
ON	last_first.col_1	= my_t.col_1

quote:

---

Originally posted by pootle_flump

Still doesn't explain why you need to store them....You would need to explain what you mean by FEED really.

But if you are adamant you want to store this data then just tailor the above into an update query in an insert trigger.

---

quote:

---

Originally posted by zion99

quote:

---

Originally posted by pootle_flump

Still doesn't explain why you need to store them....You would need to explain what you mean by FEED really.

But if you are adamant you want to store this data then just tailor the above into an update query in an insert trigger.

---

i use SSIS to get the data from source to destination. I actually tried this out. All the rows which were inserted into dest. had the same date_of_insert; thats' the reason i wanted to avoid this column.

---