Feeds:
Posts
Comments

Archive for January, 2011

 

#Create table JOIN_1

CREATE TABLE JOIN_1

(

ID
INT ,

CNT1
INT

)

GO

 

#Create table JOIN_2

CREATE
TABLE JOIN_2

(

ID
INT ,

CNT2
INT

)

GO

 

#Now populate above tables

INSERT
INTO JOIN_1

SELECT
1,101

UNION

SELECT
2,201

UNION

SELECT
3,301

UNION

SELECT
4,401

UNION

SELECT
,501

GO

 

INSERT
INTO JOIN_2

SELECT
1,1001

UNION

SELECT
4,4001

UNION

SELECT
,5001

GO

 

 

#Update tables with null values

UPDATE
JOIN_1

SET
ID =NULL

WHERE
ID=0

 

UPDATE
JOIN_2

SET
ID =NULL

WHERE
ID=0

 

 

 

#Check values in both the tables

SELECT
* FROM JOIN_1

ID                            CNT1

NULL                     501

1                              101

2                              201

3                              301

4                              401

 

SELECT
* FROM JOIN_2

ID                            CNT2

NULL                     5001

1                              1001

4                              4001

 

 

#In case of INNER JOIN null values are ignored

SELECT
* FROM

JOIN_1
INNER JOIN 
JOIN_2

ON
JOIN_1.ID=JOIN_2.ID

 

ID                            CNT1                      ID                            CNT2

1                              101                         1                              1001

4                              401                         4                              4001

 

 

#In case of LEFT JOIN null values are not ignored

SELECT
* FROM

JOIN_1
LEFT JOIN 
JOIN_2

ON
JOIN_1.ID=JOIN_2.ID

ID                            CNT1                      ID                            CNT2

NULL                     501                         NULL                     NULL

1                              101                         1                              1001

2                              201                         NULL                     NULL

3                              301                         NULL                     NULL

4                              401                         4                              4001

 

 

 

#In case of RIGHT JOIN null values are not ignored

SELECT
* FROM

JOIN_1
RIGHT JOIN 
JOIN_2

ON
JOIN_1.ID=JOIN_2.ID

ID                            CNT1                      ID                            CNT2

NULL                     NULL                     NULL                     5001

1                              101                         1                              1001

4                              401                         4                              4001

 

 

#In case of FULL OUTER JOIN  null values are not ignored

SELECT
* FROM

JOIN_1
FULL OUTER JOIN 
JOIN_2

ON
JOIN_1.ID=JOIN_2.ID

ID                            CNT1                      ID                            CNT2

NULL                     501                         NULL                     NULL

1                              101                         1                              1001

2                              201                         NULL                     NULL

3                              301                         NULL                     NULL

4                              401                         4                              4001

NULL                     NULL                     NULL                     5001

 

 

 

Read Full Post »

Consider two tables TblA and TblB having similar schema

 

1. Find all records from TblA table that are not in TblB table

 

SELECT
* FROM dbo.TblA

EXCEPT

SELECT
* FROM dbo.TblB

 

 

2. Find all records from TblB table that are not in TblA table

 

SELECT
* FROM dbo.TblB

EXCEPT

SELECT
* FROM dbo.TblA

 

 

3. To get above Differences together run below

 

SELECT  
‘TableName_TblA’ AS
TableName, *  FROM

(

SELECT
* FROM dbo.TblA

EXCEPT

SELECT
* FROM dbo.TblB

)
X

UNION ALL

SELECT
‘TableName_TblB’  AS
TableName, * FROM

(

SELECT
* FROM dbo.TblB

EXCEPT

SELECT
*FROM dbo.TblA

)
X

 

Read Full Post »

USE
DATABASE_NAME

GO

 

 

–Create scripts for All Procs

 

SELECT SM.definition

 

FROM sys.sql_modules
SM

 

INNER JOIN sys.Objects SO

 

ON SM.Object_id = SO.Object_id

 

WHERE SO.type = ‘p’

 

 

 

–Create scripts for All Views

 

SELECT SM.definition

 

FROM sys.sql_modules
SM

 

INNER JOIN sys.Objects SO

 

ON SM.Object_id = SO.Object_id

 

WHERE SO.type = ‘v’

 

 

 

–Create scripts for All Functions

 

SELECT SM.definition

 

FROM sys.sql_modules
SM

 

INNER JOIN sys.Objects SO

 

ON SM.Object_id = SO.Object_id

 

WHERE SO.type = ‘FN’

Read Full Post »


1.  Create a new variable FullPath of
string data type to hold file name.

 

2. Drag a new “Foreach File Enumerator“task
in BIDS.

 

3. Click on “Foreach File Enumerator
and go to Collection tab and browse your source folder and leave *.* for
files

 

4. Go to Variable Mappings tab now and map
variable FullPath like below

 

5. Drag one “Execute SQL Task” and “File
System task
” inside “ForeachFile Enumerator“container.


6. First execute SQL task is just dummy so that we can apply Precedence
constraint


7. After connecting both execute SQL task, apply Precedence constraint like
below

 

UPPER(SUBSTRING(@[FullPath],FINDSTRING(@[FullPath],”.”,1)+1,LEN(@[FullPath])))==”CSV”
||UPPER(SUBSTRING(@[FullPath],FINDSTRING(@[FullPath],”.”,1)+1,LEN(@[FullPath])))==”DOCX”

 

8. I have used above to identify all files in C:\Test
Folder with “csv” and “docx” extension and move to a different folder.

 

Above can be modified to include more or
different file types

Read Full Post »

 

ALTER DATABASE  Database_Name  COLLATE Latin1_General_CS_AS

Read Full Post »

%d bloggers like this: