Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

Archive for the ‘sql server’ Category

Importando arquivos .csv no SQL Server através do comando BULK INSERT

Posted by karuta em novembro 5, 2015

O uso de arquivos .csv para a integração entre diferentes sistemas representa uma prática bastante comum no ambiente corporativo, sendo que o próprio pacote Office (por meio do Excel) oferece suporte a este formato. O padrão CSV (sigla do inglês “Comma-separated values”) é empregado costumeiramente na representação de dados em um formato tabular. Arquivos deste tipo possuem a extensão .csv e, basicamente, nada mais são do que sequências de texto separadas por um símbolo especial (geralmente caracteres como vírgula ou ponto-e-vírgula são utilizados em tais representações).

No caso específico do SQL Server, é importante destacar que este SGBD também suporta a manipulação de arquivos no formato .csv. O comando BULK INSERT corresponde a um dos meios que possibilitam a compatibilidade com este padrão, fazendo uso para isto de uma técnica conhecida como “Bulk Copy”. Um dos benefícios de operações do tipo Bulk Copy é viabilizar a transferência, de um modo simplificado, de grandes volumes de informações para uma base relacional.

Sendo parte integrante da extensão Transact-SQL desde a versão 2008 do SQL Server, o comando BULK INSERT permite o carregamento de informações contidas em arquivos de texto para tabelas e views em bancos de dados. O objetivo deste post é demonstrar como a instrução BULK INSERT pode ser utilizada na importação de arquivos .csv, com isto acontecendo através de um exemplo envolvendo a carga de preços de produtos.

Exemplo de utilização do comando BULK INSERT

O exemplo abordado neste artigo fará uso de uma tabela chamada “TMP_Produtos”, a partir de uma base de dados gerada num servidor SQL Server 2014. NaListagem 1 está o script a ser utilizado para a criação desta estrutura.

A tabela TMP_Produtos será preenchida tomando por base um arquivo .csv (Produtos.csv), sendo que neste último constará uma lista de preços com produtos comercializados por uma companhia hipotética. Em um cenário real, essa estrutura poderia ser o ponto de partida para que um processo atualizasse um cadastro com informações consolidadas de produtos.

1
2
3
4
5
6
7
CREATE TABLE [dbo].[TMP_Produtos](
    [CodigoBarras] [char](13) NOT NULL,
    [NomeProduto] [varchar](50) NOT NULL,
    [Categoria] [varchar](20) NOT NULL,
    [PrecoVenda] [decimal](12,2) NOT NULL
)
GO

Listagem 1: Script para criação da tabela TMP_Produtos

Já na Listagem 2 está o conteúdo do arquivo .csv (Produtos.csv) que será importado, por meio do comando BULK INSERT, para a tabela TMP_Produtos. Este arquivo conta com diferentes colunas separadas por ponto-e-vírgula (“;”), sendo formado pelos seguintes campos:

  • Código de Barras;
  • Nome/Descrição do Produto;
  • Categoria do Produto;
  • Preço Unitário.
1
2
3
4
5
6
7
8
7890000000111;Iron Maiden - Powerslave;CDs;44.90
7890000000222;Metallica - Black Album;CDs;46.95
7890000000333;Invictus;DVDs;16.90
7890000000444;Uma Mente Brilhante;DVDs;32.75
7890000000555;Trilogia Senhor dos Anéis;Blu-Ray;129.90
7890000000660;Box Star Wars - 6 episódios;Blu-Ray;299.90
7890000000777;A Arte da Guerra;Livros;10.00
7890000000888;Transformando Suor em Ouro;Livros;24.90

Listagem 2: Arquivo Produtos.csv

O exemplo aqui abordado parte do pressuposto de que o arquivo Produtos.csv esteja localizado em um diretório chamado C:\Desenvolvimento\TesteBulkInsert. Para que seja possível a carga das informações, o comando BULK INSERT deverá ser utilizado conforme indicado na Listagem 3:

  • A instrução TRUNCATE irá inicializar a tabela TMP_Produtos, antes de se proceder com a carga de dados para tal estrutura.
  • No comando BULK INSERT é indicada inicialmente a tabela em que os dados deverão ser carregados (TMP_Produtos, neste caso);
  • A cláusula FROM do bloco formado pelo comando BULK INSERT especifica o caminho do arquivo .csv a ser importado;
  • Já na cláusula WITH são indicados diferentes parâmetros, com o objetivo de definir aspectos tais como características do arquivo, assim como outros comportamentos a serem considerados pelo comando BULK INSERT durante o processo de carga. Levando em consideração o exemplo apresentado, é possível notar o uso dos parâmetros FIELDTERMINATOR (determina o caracter utilizado na separação dos valores que representam as colunas de um arquivo .csv) e ROWTERMINATOR (especifica o caracter empregado na quebra de linhas em um arquivo .csv). Uma relação completa dos argumentos suportados pela cláusula WITH está disponível no link ao final deste post.

O resultado da execução das instruções que constam na Listagem 3 é apresentado na Imagem 1.

1
2
3
4
5
6
7
8
9
10
11
TRUNCATE TABLE dbo.TMP_Produtos
GO
BULK INSERT dbo.TMP_Produtos
FROM 'C:\Desenvolvimento\TesteBulkInsert\Produtos.csv'
WITH
(
     FIELDTERMINATOR = ';',
     ROWTERMINATOR = '\n'
)
GO

Listagem 3: Utilizando a instrução BULK INSERT

Imagem001
Imagem 1. Resultado da execução da instrução BULK INSERT

Uma simples consulta à tabela TMP_Produtos produzirá um resultado similar àquele descrito na Imagem 2.

Imagem002
Imagem 2. Tabela TMP_Produtos preenchida após carga de arquivo .csv

Encerro com isto mais um artigo a respeito de comandos T-SQL no SQL Server. Procurei demonstrar neste novo post como a instrução BULK INSERT pode ser extremamente útil na importação do conteúdo de arquivos .csv.

Espero que este conteúdo possa auxiliá-lo no seu dia-a-dia.

Até uma próxima oportunidade!

Links

BULK INSERT (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188365.aspx

 

Créditos:

Renato Groffe

Atua como consultor em atividades voltadas ao desenvolvimento de softwares há mais de 13 anos. Bacharel em Sistemas de Informação, com especialização em Engenharia de Software. Microsoft Certified Technology Specialist (Web, WCF, Distributed Applications, ADO.NET, Windows Forms), Microsoft Specialist (HTML5 with JavaScript and CSS3, Developing ASP.NET MVC 4 Web Applications), Oracle Certified Associate (PL/SQL), Sun Certified (SCJP, SCWCD), ITIL Foundation V2, Cobit 4.1 Foundation.

Anúncios

Posted in sql server | Leave a Comment »

JSON for SQL Server 2016

Posted by karuta em outubro 30, 2015

Problem

In previous tips, Introducing JSON for SQL Server 2016 and JSON Support in SQL Server 2016, you learned about the functionality available in CTP2: translating relational data into JSON string output, using FOR JSON AUTO and FOR JSON PATH. However, much more functionality is coming.

Solution

In CTP3, more elaborate functionality will be available, including the ability to translate JSON text into relational data, extract scalar values from JSON strings, and validate the format of JSON input. In this tip, I’ll demonstrate these three features briefly.

OPENJSON()

This rowset function will return relationally structured data from JSON input; the opposite of FOR JSON AUTO/PATH. A simple example:

DECLARE @json NVARCHAR(4000) = N'{
 "UserID" : 1,
 "UserName": "AaronBertrand",
 "Active": true,
 "SignupDate": "2015-10-01"
 }';

 SELECT * FROM OPENJSON(@json);

The output in this case is a rather generic list of key-value pairs, rather similar to the input:

key          value           type
----------   -------------   ----
UserID       1               2
UserName     AaronBertrand   1
Active       true            3
SignupDate   2015-10-01      1

However, you can also define more specific output structure using the WITH clause, for example (and adding a second row):

DECLARE @json NVARCHAR(4000) = N'[{
 "UserID" : 1,
 "UserName": "AaronBertrand",
 "Active": true,
 "SignupDate": "2015-10-01"
 },
 {
 "UserID" : 2,
 "UserName": "BobO''Neil",
 "Active": false,
 "SignupDate": "2014-12-13"
 }]';

 SELECT * FROM OPENJSON(@json)
 WITH 
 (
   UserID INT, 
   UserName NVARCHAR(64),
   Active BIT,
   [Started] DATETIME '$.SignupDate' -- remap column name
 );

The results are now a bit more relational:

UserID  UserName       Active  Started
------  -------------  ------  -----------------------
1       AaronBertrand  1       2015-10-01 00:00:00.000
2       BobO'Neil      0       2014-12-13 00:00:00.000

Another interesting use case for OPENSJSON() is to facilitate string splitting. In current versions of SQL Server, people typically resort to user-defined functions, CLR, or table-valued parameters . Watch how easy this work becomes with OPENJSON():

CREATE PROCEDURE dbo.ShowObjects
  @list NVARCHAR(4000)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT name 
    FROM sys.all_objects
    WHERE [object_id] IN 
    (
      SELECT CONVERT(INT, value)
        FROM OPENJSON(N'[' + @list + N']')
    );
END
GO

EXEC dbo.ShowObjects @list = N'-101,-102,-103';

Results:

all_columns
all_views
all_objects

You can see more elaborate examples of using OPENJSON() in these blog posts from Microsoft’s Jovan Popovic:OPENJSON – The easiest way to import JSON text into table and JSON Support in SQL Server 2016.

JSON_VALUE()

This function will extract specific scalar values from within the JSON text. As you can imagine, JSON strings can get pretty complex, so there is a set of syntax for determining exactly where in the hierarchy to extract from. A few simple examples:

DECLARE @json NVARCHAR(4000) = N'{
 "UserID" : 1,
 "Cars": [ 
   { "Year":2014, "Make":"Jeep",   "Model":"Grand Cherokee" },
   { "Year":2010, "Make":"Nissan", "Model":"Murano", "Options":
     [{ "AC":true,"Panoramic Roof":true }]
  ]
 }';

SELECT 
  UserID = JSON_VALUE(@json, '$.UserID'),
  Model1 = JSON_VALUE(@json, '$.Cars[0].Model'),
  Model2 = JSON_VALUE(@json, '$.Cars[1].Model'),
  Has_AC = JSON_VALUE(@json, '$.Cars[1].Options[0].AC');

Results:

UserID    Model1            Model2    Has_AC
------    --------------    ------    ------
1         Grand Cherokee    Murano    true

ISJSON()

This function returns a bit value: 1 if the input is a valid JSON document; 0 if not. On its own, the usage is quite simple:

DECLARE @json NVARCHAR(4000) = N'[{
 "UserID" : 1,
 "UserName": "AaronBertrand",
 "Active": true,
 "SignupDate": "2015-10-01"
 }]';

 SELECT ISJSON(@json),  -- returns 1
        ISJSON(N'foo'); -- returns 0

The value here is not so much to interrogate individual values, but rather to define things like validation logic in triggers and check constraints. For example, if I have a table where I am storing JSON text, I may want a constraint to prevent any garbage from getting in there (keep in mind that there is no JSON type; the existing NVARCHAR type is used).

CREATE TABLE dbo.JSONExample
(
  UserID INT PRIMARY KEY,
  Attributes NVARCHAR(4000),
  CONSTRAINT [No Garbage] CHECK (ISJSON(Attributes)) = 1
);

Now, if I try to insert garbage into this column:

INSERT dbo.JSONExample(UserID, Attributes) SELECT 1, N'foo';

I get the following error message:

Msg 547, Level 16, State 0
The INSERT statement conflicted with the CHECK constraint "No Garbage".
The conflict occurred in database "tempdb", table "dbo.JSONExample", 
column 'Attributes'.
The statement has been terminated.

However this will work fine:

INSERT dbo.JSONExample(UserID, Attributes) SELECT 1, N'{"garbage": false}';

Summary

The introduction of JSON functionality to SQL Server will allow for greater compatibility with existing applications and fewer barriers and cumbersome workarounds to sharing data between systems. In forthcoming related tips, I will talk in more detail about practical use cases for some of these features, and demonstrate the conversion of an EAV structure to one that uses JSON for storage instead.

 

By:

Posted in sql server | Leave a Comment »

Buscar ‘termos’ dentro de Store Procedures

Posted by karuta em outubro 25, 2015

select object_name(id) from syscomments where text like ‘% termos %’

Posted in sql server, store procedure | Leave a Comment »

DATABASE MIRRORING

Posted by karuta em outubro 22, 2015

Surgido no SQL Server 2005 esta tecnologia consegue realizar a “cópia” da database do servidor primário e a disponibiliza para o servidor Mirror. Esta operação é realizada através da transferência dos registros do transaction log (Primário para o Mirror), aplicando assim todas as transações ocorridas. Vale ressaltar que o Database Mirroring é compatível com qualquer tipo de hardware suportado pelo SQL Server.

Servidor Primário: Servidor que será a referência para a aplicação armazenar seus dados.

Servidor Mirror: Este será o servidor espelho do servidor principal. O servidor Mirror irá permanecer no estado de restore e este não pode ser acessado diretamente.

Servidor Witness: Este servidor é responsável por monitorar a disponibilidade do servidor principal e em caso de falha o mesmo notifica as aplicações através de um parâmetro na string de conexão e assim direciona para o servidor secundário. Para maiores informações referente ao funcionamento e criação da string de conexão, disponibilizo para vocês uma publicação da Microsoft abordando este tema:

https://msdn.microsoft.com/subscriptions/index/ms175484

Observação: A solução Database Mirroring não permite o espelhamento das databases de sistema (Master, Msdb, Tempdb e Model.)

MODOS DE OPERAÇÃO

Possuímos três modos operacionais para o Database Mirroring, são eles:


TABELA 1: FUNCIONALIDADES.

Apresentaremos primeiro de forma resumida os modos operacionais e funcionalidades que o Database Mirroring nos possibilita, logo a diante iremos detalharemos os componentes envolvidos em cada modo (TRANSACTION SAFETY, WITNESS, FAILOVER).

Alta Disponibilidade

O modo de alta disponibilidade ou High Availability é o único que trabalha com o failover automático e com o servidor Witness. Para o seu funcionamento o mesmo necessitará operar de forma ¹síncrona, que nos garantirá que não haverá perda de dados.

Caso o servidor principal esteja indisponível, ocorrerá o processo conhecido como “role switching”, na qual significará que o servidor mirror assumirá o papel do servidor principal e quando servidor principal antigo retornar sua disponibilidade, este assumirá o papel de servidor Mirror.

Alta Proteção

O modo de alta proteção ou High Protection requer que o transaction safety seja definido como FULL, que representará que as transações deverão trabalhar de forma síncrona, garantindo assim que não haverá perda de dados, mas este também não nos garantirá a disponibilidade da aplicação, devido a não possuir um servidor Witness, tornando assim um failover manual.

Alta Performance

O modo de alta performance ou High Performance, é o modo mais performático (rápido) comparado com outros modos operacionais, isto se dá devido a possuir o modo de transação do tipo ²assíncrona, ou seja, a transação não necessita de ser efetivada em ambos os parceiros para confirmar a transação, entretanto este método não previne na perda de dados, caso ocorra a troca de papéis dos servidores primário e Mirror.

  TABELA 2: MODOS DE OPERAÇÃO

TRANSACTION SAFETY

Este irá definir o nível de segurança que a transação terá no Database Mirror, podendo ser operada de duas formas:

FULL: Virá configurada como default, este representará o nível máximo de     segurança, onde as transações serão operadas de forma ¹ Síncrona.

OFF: Este representará o modo de alto desempenho, onde as transações serão operadas de forma ² Assíncrona.

¹ Síncrona: Essa opção exige que ambos os servidores (Principal e Mirror) confirmem a transação antes que a mesma seja efetivada. Lembramos que este tipo de sincronização eleva o nível de latência das transações.

² Assíncrona: Ao contrário da síncrona, a assíncrona confirma a transação antes que a mesma seja efetivada em ambos os nós, tornando-a mais performática, entretanto esta não previne a perda de dados.

WITNESS

Este campo informará se o modo operacional utilizará o Witness.

FAILOVER

Failover Manual: Este failover requer uma ação manual do DBA para realizar a troca do servidor principal para o Mirror, não acarretando na perda de dados, devido ao tipo de failover operar de forma síncrona.

Failover Forçado: Este também realizará a transferência de forma manual do servidor primário para o servidor Mirror, entretanto este modo do failover possibilitará a perda de dados, isto se dá por possuir o tipo de transação assíncrona.

Failover Automático: Suportado apenas pelo modo operacional de Alta Disponibilidade, este necessita do Witness para realizar o failover automático em caso de falha do servidor principal.

 

Redigida por

Posted in sql server | Leave a Comment »

SQL SERVER – Database Size Limitation in SQL Express

Posted by karuta em outubro 21, 2015

SQL 2000 Desktop Engine (MSDE) has limit of 2 GB data file(s) size.

SQL 2005 Express Edition has limit of 4 GB data file(s) size.

SQL 2008, SQL 2008 R2, SQL 2012 and SQL 2014 Express Edition has limit of 10 GB data file(s) size.

Posted in sql server | Leave a Comment »

CROSS APPLY

Posted by karuta em outubro 16, 2015

Hoje eu vou falar sobre o APPLY. Esse cara serve para fazer uma espécie de JOIN só que sem a clausula ON onde o INNER esta para o CROSS assim como o LEFT ou o RIGHT para o OUTER. Isso resulta em coisas do tipo:

SELECT      T.Nome, CA.Nome,  CA.Gols
FROM        time       T
CROSS APPLY (SELECT * FROM jogador J WHERE J.idTime = T.idTime) CA

Que tem exatamente o mesmo plano de execução e resultado disso aqui:

SELECT      T.Nome,           J.Nome,           J.Gols
FROM        time        T
INNER JOIN  Jogador     J      ON          J.idTime    = T.idTime

Então o que torna esse cara especial? Em minha opinião e experiência uma das coisas mais interessantes e com um grande potencial são as pesquisas com o TOP(n) onde o n tem uma variação de acordo com a regra de negócio em questão e, ainda mais importante, o fato de você conseguir fazer consultas com funções que retornam tabelas!

CREATE FUNCTION Exemplo(@id AS int, @n AS INT)

RETURNS TABLE

AS

RETURN

SELECT      TOP(@n)     *

FROM        jogador     j

WHERE       j.idTime    = @id

ORDER BY    j.Gols      DESC

GO

SELECT            *

FROM        Time  t

CROSS APPLY dbo.Exemplo(t.idTime, CASE WHEN(t.idTime=1) THEN 1 ELSE 2 END)

ORDER BY    t.IdTime    ASC

Vejam que as possibilidades com esse operador são grandes porem no dia a dia da “firma” são raros os casos onde ele pode/precisa ser utilizado.

 

CRÉDITOS E AGRADECIMENTOS: 

http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join

Posted in sql server | Leave a Comment »

Script – ver todas as tabelas em um banco de dados

Posted by karuta em janeiro 23, 2015

SELECT name FROM sysobjects WHERE xtype=’U’ order by name

Posted in sql server | Leave a Comment »

execute mathematical formula stored as string

Posted by karuta em novembro 10, 2014

Declare @weight as char(25)
Declare @output as numeric(18,2)

SET @weight = '2*5+2*5*4'
declare @TheSQL nvarchar(1000)
Set @TheSQL = 'Select @theoutput = ' + @weight


--OutPut should be 50.
exec sp_executesql @stmt = @TheSQL
    , @params= N'@theoutput int OUTPUT' 
	, @theoutput = @output output

print @output


links úteis para pesquisa:
http://www.itjungle.com/fhg/fhg111004-story01.html
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Posted in sql server, store procedure | Leave a Comment »

UPDATE from SELECT using SQL Server

Posted by karuta em agosto 15, 2014

UPDATE
    Table
SET
    Table.col1 = other_table.col1,
    Table.col2 = other_table.col2
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id

Posted in sql server | Leave a Comment »

Limpando logs de bases no SQL Server

Posted by karuta em agosto 7, 2014

backup log “BANCODEDADOS” with truncate_only
go

dbcc shrinkdatabase (’BANCODEDADOS’, 0)
go

Onde, BANCODEDADOS é o nome do banco onde fará manutenção. O primeiro comando faz um backup dos registros do log. O segundo, limpa os arquivos inúteis do banco, como logs gigantescos. 

Posted in sql server | Leave a Comment »