Friday, May 3

Cross update taking values from another column based on duplicates found -SQL

I have a table_A containing duplicate records in column ID,but has distinct E_ID for each duplicate ID.I have retrieved records using below mention query.

select ID,E_ID,Comments
from table_A
where ID in (SELECT ID
                FROM table_A t where ID !=' '
                GROUP BY ID 
                HAVING COUNT(distinct E_ID) > 1
               )
group by ID,E_ID,Comments order by ID
This query will give me output like this:
Now i want to UPDATE Comments column which will give me output like below shown table. Note:please look into comments sentences carefully,you see E_ID differences.
You could see that E_IDs are cross update in Comments column for each duplicate ID.
You can use an outer apply query to generate a list of duplicates. This will work even if there is more than one duplicate.To know more about Outer Apply and Cross Apply click here.
The query can be written like this

update  org
set     Comments = 'Duplicate of E_ID ' + stuff(dup.lst, 1, 2, '')
from    Table_A org
outer apply
        (
        select  ', ' + cast(dup.E_ID as varchar) as [text()]
        from    Table_A dup
        where   dup.ID = org.ID
                and dup.E_ID <> org.E_ID
        for xml path('')
        ) dup(lst) where ID!=' 'and ID is not null;

The first query shows with in the Table_A, Now if you have another table called Table_B  which has same Columns and if you want to check ID in Table_A and Table_B and want to update Table_A based on duplicate IDs found between Table_A and Table_B below query can used.
update  org
set     Comments = 'Duplicate of E_ID ' + stuff(dup.lst, 1, 2, '')
from    Table_A org
outer apply
        (
        select  ', ' + cast(dup.E_ID as varchar) as [text()]
        from    Table_B dup
        where   dup.ID = org.ID
                and dup.E_ID <> org.E_ID
        for xml path('')
        ) dup(lst) where ID!=' 'and ID is not null;
I have posted this question in stackoverflow . This can be helpful so i thought to put up on my blog.

Thursday, May 2

BULK INSERT-SQL


Bulk Insert

Imports a data file into a database table or view in a user-specified format. This usually helps when you have so many text files/Data files to migrate to database.

For Eg.
Consider, I have Text files such as Text_1,Text_2,Text_3. With data such as shown below.







Note:As field terminator you can use ' , ' or ' | '

Simple Syntax:

BULK INSERT 
[ database_name. [ schema_name ] . | schema_name. ] [ table_name | view_name ]
FROM 'data_file(Path of the file)' WITH(FIELDTERMINATOR ='|',ROWTERMINATOR = '\n')

Bulk Insert statement for Text_1.
BULK INSERT JOHN_TEST.dbo.Namelist FROM 'D:\jjn\Namelist\Text_1.TXT'
WITH(FIELDTERMINATOR ='|',ROWTERMINATOR = '\n')


Bulk Insert statement for Text_2.
BULK INSERT JOHN_TEST.dbo.Namelist FROM 'D:\jjn\Namelist\Text_2.TXT'
WITH(FIELDTERMINATOR ='|',ROWTERMINATOR = '\n')


Bulk Insert statement for Text_3.
BULK INSERT JOHN_TEST.dbo.Namelist FROM 'D:\jjn\Namelist\Text_3.TXT'
WITH(FIELDTERMINATOR ='|',ROWTERMINATOR = '\n')


Before executing this statements I should create a table called Namelist in my JOHN_TEST database

Create Table Namelist (ID_NO int, Name varchar(255), City varchar(255))

Now these Bulk Insert statements can be executed; keep in mind that your text files should be in same server that you are working on. i.e. if you are working in local sys. then place in local itself or if working in servers;place text files in servers, otherwise it will throw out error.

Output:

This is a way to migrate data from text files/Flat files at time to DB. This query can be used in stored procedures when needed.