Nối giá trị trong các dòng của một cột thành một chuỗi cách nhau bởi dấu phẩy và ngược lại

Vấn đề: Trong nhiều tình huống làm việc với SQL, bạn muốn ghép giá trị trong một cột của table thành một chuỗi cách nhau bởi dấu phẩy cho mục đích nào đó, hoặc ngược lại.

Giải quyết: Chúng ta sẽ tạo bảng, insert một số dòng vào bảng, sau đó thực hiện theo yêu cầu đặt ra như trên.

Đọc thêm »

Đồng bộ dữ liệu giữa hai bảng trong SQL Server

Trên thực tế có nhiều tình huống các ứng dụng cần đồng bộ dữ liệu giữa các table với nhau điển hình như:

– Ứng dụng với CSDL phân tán.
– Ứng dụng Data Warehouse cần nhận biết dữ liệu update, insert để làm mới dữ liệu.

Cách truyền thống để so sánh sự thay đổi dữ liệu từ bảng nguồn so với bảng đích là so sánh giá trị trên từng field của từng dòng trong table, kỹ thuật này rất thủ công và cũng rất chậm và tốn nhiều thời gian cũng như tài nguyên hệ thống.

Đọc thêm »

SQL Server: Sử dụng OPENXML trong SQL Server

Ứng dụng XML trong SQL khá đa dạng, trong bài này chúng ta sẽ giải quyết tình huống như sau:
Bạn viết một ứng dụng trên .Net, trên 1 form nào đó của bạn người dùng sẽ nhập một số thông tin, những thông tin đó sẽ được lưu vào một một hoặc nhiều table trên SQL Server (ví dụ: form "Hóa đơn bán hàng" dữ liệu sẽ lưu vào table Invoice và InvoiceDetail), giả sử như bạn muốn chuyển tất cả dữ liệu người dùng đã nhập vào form thành một chuỗi XML và gửi nó đến server, sau đó SQL Server sẽ đọc, bóc tách xử lý và update dữ liệu từ chuỗi XML này vào các table tương ứng trong database.
Đồng thời bạn muốn gói gọn các hành động insert, update và delete vào một stored procedure duy nhất để dễ quản lý mà không muốn viết riêng từng thủ tục cho từng hành động một vừa mất thời gian lại phức tạp trong việc bảo trì code.
Chưa nói đến lợi hại của kỹ thuật này, trước hết chúng ta hãy giải quyết bài toán trên bằng cách sử dụng hàm OPENXML trong SQL Server.

Đọc thêm »

Running total (tạm dịch Tổng lũy kế theo chiều dọc)

Running total là kỹ thuật tính toán tổng lũy kế theo chiều dọc của một table, như minh họa sau:

Ứng dụng của Running total rất phổ biến, như tính toán tổng lũy kế doanh thu theo từng ngày, từng nhân viên, từng mặt hàng, tồn kho theo từng lần nhập xuất chi tiết theo hàng hóa và theo ngày, lũy kế tài khoản theo mỗi nghiệp vụ kinh tế phát sinh….
Nhiều lập trình viên thực hiện tính running total đơn giản bằng cách lặp qua từng dòng và dùng câu lệnh update để tính giá trị, nhưng với số dòng rất lớn thì có thể ảnh hưởng đến hiệu suất thực thi.

Ở đây chúng ta sẽ dùng một câu lệnh update duy nhất để tính runining total cho toàn bảng.
Trước hết, hãy tạo và insert một số dòng vào bảng để test:

Đọc thêm »

Chạy backup database từ file bat

Trong môi trường phát triển một ứng dụng mới, database của bạn sẽ thường xuyên phải chỉnh sửa cấu trúc, dữ liệu. 
Hoặc hàng ngày bạn phải lặp đi lặp lại nhiệm vụ backup database của công ty hoặc của khách hàng.
Phiên bản SQL bạn sử dụng lại là phiên bản SQL 2008 Express không có SQL Agent để tự động chạy Job thực hiện việc bakup database. Bạn sẽ thực hiện công việc tự động này bằng cách sử dụng Task Scheduler có sẵn của Window.
Bạn không muốn mở SQL Management Studio hoặc ứng dụng của bạn lên chỉ để thực hiện một chức năng duy nhất là backup một database nào đó của công ty.
Tất cả những gì bạn muốn là double click lên một file .bat và nó sẽ thực hiện chạy một câu lệnh sql bất kỳ cho bạn, cụ thể trong bài này là thực hiện một Procedure làm công việc backup database.

Chúng ta sẽ sử dụng sqlcmd để thực hiện công việc trên.

Đọc thêm »

Script đơn giản để tạo chuỗi các giá trị tìm kiếm

Giả sử chúng ta có một bảng có cột chứa các giá trị tìm kiếm và chúng ta muốn kết nối các giá trị trong cột này thành một chuỗi giá trị tìm kiếm được phân cách bởi dấu ‘,’. Chúng ta có thể sử dụng phương pháp truyền thống là CURSOR hoặc vòng lặp WHILE. Tuy nhiên chúng ta có một cách đơn giản hơn nhiều là sử dụng phương pháp khá tệ là CURSOR để quét qua toàn bộ bảng.

Đọc thêm »

Lấy ngày đầu tháng, ngày cuối tháng

Vấn đề : Làm sao lấy ngày đầu tháng, ngày cuối tháng tháng trước, tháng sau của tháng hiện tại?
Giải pháp: Sử dụng các hàm  ngày tháng. Như ví dụ sau:

DECLARE @mydate DATETIME

SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
‘Ngày cuối tháng trước’
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
‘Ngày đầu tháng hiện tại’ AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, ‘hôm nay’ AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
‘Ngày cuối tháng này’
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
‘Ngày đầu tháng tiếp theo’
GO

Xóa các dòng trùng nhau

Vấn đề:Trong rất nhiều trường hợp, chúng ta gặp một vấn đề nhỏ nhưng không nhỏ đó là các dòng trùng nhau trong một bảng. Vậy làm thế nào để xóa các dòng trùng nhau này.

Giải pháp:
Tạo bảng và insert vào bảng theo ví dụ sau:

if OBJECT_ID(‘MailList’) is not null drop table MailList
go
create table MailList
(
id int identity not null,
mail_add varchar(100) not null,
      Name Nvarchar(50) default () not null,
)
go
insert MailList (mail_add, Name)
      values
      (‘mailA@gmail.com’, ‘Nguyen van A’),
      (‘mailB@gmail.com’, ‘Nguyen van B’),
      (‘mailA@gmail.com’, ‘Nguyen Van A’)
     

   
Cột mail_add có hai mail trùng nhau đều là mailA. Để xóa bớt một dòng, dùng câu lệnh sau:

delete from MailList
      where id not in (select MAX(ID) from MailList group by mail_add)