SQL: Script traceability

A small snippet for automaticly in SQL Server get date and user name into columns like CREATED_DATE, CREATED_BY, LATEST_UPDATED_BY etc:

BEGIN TRAN
UPDATE [table]
SET 
LATEST_CHANGED_DATE = getDate(), 
LATEST_CHANGED_BY = SYSTEM_USER
WHERE [critera] = 'True'
COMMIT

 

In table:


LATEST_CHANGED_DATE       LATEST_CHANGED_BY
2008-10-24 10:37:26.470   Stefan

SQL: Multiple insert

I do some SQL scriptning for various testing purposes sometimes and I sure miss array functionality in SQL. There are however some workarounds...

One is to create temporary tables and fill it with data. It works excellent if you combine INSERT with SELECT FROM [TABLE] but what if you want to fill your table with hard-coded data? Here is one possible solution:

--Example of multiple insert

--Create temp table
CREATE TABLE #fpp([RID] int NOT NULL IDENTITY (1, 1) PRIMARY KEY, [MY_DATA] varchar(30))
go 

--Insert multiple data using union
insert into #fpp ([MY_DATA])
select 
(select 'data1') union
(select 'data2') union
(select 'data3') union
(select 'date4')
go

select * from #fpp

--Clean up
drop table #fpp