Ir al contenido principal

SQL Server - Tips

List All Tables of Database

 


USE [YourDBName]
GO 
SELECT *FROM sys.Tables
GO


Insert structure from one table to another


SELECT *
into new_table FROM origin_table where 1 =2 

How to create a counter

Use the function ROW_NUMBER ( ), where the syntaxis is 
 
ROW_NUMBER ( ) OVER ( FIELD order_by_clause )
 
Where FIELD  is the name of the column you want to use to start counting. Example:

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row, 
FROM Sales.vSalesPerson
 

Enumerate rows ... 1.2.3.... 

 
ROW_NUMBER() over ( ORDER BY [Job Stage]) as [pyramidPosition]
 

Reset the enumeration  ..... 1.2.3....1.2.3

 

ROW_NUMBER() over (partition BY idProject ORDER BY [Job Stage]) as [pyramidPosition]
 
http://sqlfiddle.com/#!6/501c0/2/0
 
 Update Table from Select
 

Update projects

set projects.tg4Real = [TG4 Date Real DD/MM/YYYY] ,
 projects.tg5Real = [TG5 Date Real DD/MM/YYYY] 
from
 projects p
inner join
 gtt...[Upload$] u
on p.projectName = [Project Name]
 
 

Multi user

ALTER DATABASE DATA_BASE SET MULTI_USER; 
 

Add user to a DB

Can you check it by Going To Security --> Login-->Right and Click Property and then goto user mapping tab. Then select the database
 
 

Convert String Dates to Date/Datetime 

From '12/10/25' to date 
CONVERT(DATE,CAST ( RTRIM( LTRIM([CREATION DATE])) as nvarchar), 103) 

From '16/01/2007 3:03:35:270AM' to datetime
From '23/06/2014 09:07:17 a. m.'  to datetime
CONVERT(datetime, ( RIGHT(LEFT([START TIME],5),2) + '/'+LEFT([START TIME],2)+'/'+ RIGHT(LEFT([START TIME],10),4)+  SUBSTRING([START TIME],11, 9 ) + REPLACE( SUBSTRING([START TIME],21, 4 ), '. ', '' ) ) , 120 ) as ODBC_datetime


 Add many columns in one

Function:  STUFF

select distinct t1.userId, t1.userName,
  STUFF((SELECT ', ' + t2.vpn 
         from Request t2
         where t1.userId = t2.userId
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,0,'') VPN,
        t1.managerName, t1.managerUserId, t1.ccName, t1.ccUserId
from Request t1
order by [userId]

Substract Dates

DATEDIFF( minute, [resto_rst_prov_date], crea_date))

Days with decimals

DATEDIFF( minute, [resto_rst_prov_date], crea_date))/(60*24.0)

Days with two decimals


 convert(decimal(12,2), (DATEDIFF( minute, [resto_rst_prov_date], crea_date))/(60*24.0)) 

 

 

 

 

 

 

 


Comentarios

Publicar un comentario

Entradas populares de este blog

Android - Basic Steps (Service)

Service Run in the main thread of the hosting application Android can kill the Service if need the resources Purpose Supporting inter-application method execution Performing background processing Start a Service Call Context.startService(Intent intent)\ To call from a Fragment use getActivity().getApplicationContext().startService( intentService); Executing the service After call startService(...)  In the Service is executed the method onStartCommand(...) If the method returns the constant START_NOT_STICKY then Android will not restart the service automatically if the the process is killedp Foreground To execute the service foreground call the method startForeground() Use this if the user is aware of the process Bind to a Service Call the method Context.bindService( Intent service ServiceConnection con int flags ) Send Toast from the Service On the method onStartCommand receive the message   ...
Publicar un comentario
Leer más

BI - SSIS ( Basics I )

SSIS - Basic Concepts Tasks Bulk Insert Task—Loads data into a table by using the BULK INSERT SQL command. Data Flow Task—This is the most important task that loads and transforms data into an OLE DB Destination. Execute Package Task—Enables you to execute a package from within a package, making your SSIS packages modular. Execute Process Task—Executes a program external to your package, like one to split your extract file into many files before processing the individual files. Execute SQL Task—Executes a SQL statement or stored procedure. File System Task—This task can handle directory operations like creating, renaming, or deleting a directory. It can also manage file operations like moving, copying, or deleting files. FTP Task—Sends or receives files from an FTP site. Script Task—Runs a set of VB.NET or C# coding inside a Visual Studio environment. Send Mail Task—Sends a mail message through SMTP. Analysis Services Processing Task—This task processes a SQL Server A...
Publicar un comentario
Leer más

TOGAF9

Kinds of Architectures Business Architecture / Business Process Architecture    Define the business strategy, governance, organization and key business processes Data Architecture    Describe the structure of an organization logical and physical data assets and data resources Application Architecture    Describe a blueprint for the individual application systems to be deploy, interactions and  their relationships to the core business processes of the organization Technology Architecture    Describe the logical software and hardware capabilities that are required to support the deployment of business data and application services. This includes middle-ware infrastructure, networks, communications, processing standards  Architecture Governance Increase transparency of accountability, and informed delegation of authority Controlled risk management Protection of the existing asset base through maximizing  re-us...
Publicar un comentario
Leer más