Cách Insert và tạo bảng dữ liệu "động" trong SQL Server

Như chúng ta đã biết đối với các hệ thống Logging thì dữ liệu luôn được cập nhật hàng tháng, hàng ngày vì vậy đòi hỏi CSDL phải tương thích có nghĩa là Log trong tháng nào thì hệ thống sẽ Insert vào tháng đó tương ứng tráng việc Insert nhiều trong cùng 1 bảng dẫn đến việc Select chậm khi trả về kết quả.

Vì vậy đối với Log của mỗi tháng chúng ta sẽ tạo ra các bảng vd:

Tháng 1 năm 2010 hệ thống sẽ tự động tạo ra bảng tbl_Month_1_2010
Tháng 2 năm 2010 hệ thống sẽ tự động tạo ra bảng tbl_Month_2_2010
...
Tháng 12 năm 2010 hệ thống sẽ tự động tạo ra bảng tbl_Month_12_2010

Như vậy làm sao để hệ thống có thể tự tạo ra các bảng tương ứng với năm và tháng như vậy ?

Giải pháp của tôi là sử dụng SQL Job trong SQL Server, cái này bắt buộc bạn phải có Full quyền Administrator trên Server Database

Các bước như sau:

B1: Trong SQL Server bạn kéo xuống dưới có phần SQL Server Agent bạn phải Start nó lên nếu nó đang Stop

Bạn click chuột phải vào Jobs chọn New Job

B2: Tại đây bạn được yêu cầu nhập Name: Tên của Job ở đây tôi chọn là CreateTableLogging và owner là sa

B3: Sau khi ấn OK bạn chọn tiếp phần Steps và bạn chọn New Step, tại đây bạn được yêu cầu nhập Step Name tôi chọn là StepCreateTable và Type là Transact-SQL script (T-SQL) có nghĩa là chạy câu lệnh SQL.

Bạn chọn tiếp Database là tên Database bạn muốn thực thi
Phần Command bạn gõ câu lệnh sau để tạo bảng động

DECLARE @strCreateTable AS VARCHAR(1000)
SET @strCreateTable = 'CREATE TABLE tbl_Month_'
SET @strCreateTable += Cast(MONTH(GETDATE()) as varchar)
SET @strCreateTable += '_'
SET @strCreateTable += Cast(YEAR(GETDATE()) as varchar)
SET @strCreateTable +='('
SET @strCreateTable +='ID int PRIMARY KEY IDENTITY,'
SET @strCreateTable +='Name nvarchar(50)'
SET @strCreateTable +=')'
--PRINT(@strCreateTable)
EXEC(@strCreateTable)

Sau đó nhấn OK

B4: Tiếp đến bạn chọn phần Schedules (để đặt lịch chạy cho Job) ban chọn New Schedule ở đây bạn được yêu cầu nhập Schedule Name cho Job tôi chọn là ScheduleCreateTable, Phần tần suất (Frequency) tôi chọn Occurs chạy vào ngày 15 hàng tháng (Monthly) lúc 12h đêm.

B5: sau đó bạn ấn OK để hoàn tất việc đặt lịch chạy cho Job và OK để hoàn tất Job
và đây là kết quả

B6: Để chắc là Job của bạn có chạy đúng hay không thì bạn Click chuột phải vào Job vừa tạo chọn Start Job at Step...

B7: Nếu nó báo thế này thì có nghĩa là Job của bạn đã chạy chính xác

B8: Kiểm tra

Như vậy mới hoàn thành xong phần tạo bảng bước tiếp chúng ta phải insert vào bảng đó thế nào

DECLARE @strValue AS NVARCHAR(50)
DECLARE @strSQL AS VARCHAR(1000)
SET @strValue = 'Justin___'
SET @strValue += Cast(GETDATE() as varchar)

SET @strSQL = 'INSERT INTO tbl_Month_'
SET @strSQL += Cast(MONTH(GETDATE()) as varchar)
SET @strSQL += '_'
SET @strSQL += Cast(YEAR(GETDATE()) as varchar)
SET @strSQL += '([Name]) VALUES('''
SET @strSQL += @strValue
SET @strSQL += ''')'
--PRINT(@strSQL)
EXEC(@strSQL)

Trong bảng tbl_Month_xx_xxxx  này tôi có 2 trường là ID (tự tăng) và Name
sau đó tôi insert test các giá trị mà gán trong biến @strValue là "Justin___" + ngày hiện tại
sau khi insert xong dữ liệu của tôi sẽ là thế này

ID   Name
6    Justin___Dec 12 2009  9:25AM
7    Justin___Dec 12 2009  9:25AM
8    Justin___Dec 12 2009  9:25AM
9    Justin___Dec 12 2009  9:25AM