Karuta’s ASP & M$ SQLserver

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

Archive for novembro \22\UTC 2015

How do I deal with FK constraints when importing data using DTS Import/Export Wizard?

Posted by karuta em novembro 22, 2015

I was given this solution over at SQLTeam.com:

Use:

 EXEC sp_msforeachtable 'ALTER TABLE [table-name] NOCHECK CONSTRAINT all'

Then import your data

EXEC sp_msforeachtable 'ALTER TABLE  [table-name]  CHECK CONSTRAINT all'

 

Using that method I was able to import all the data in with no issues.

 

 

–Reports all constraints that are not trusted (SQL 2000)
SELECT name, type, status,
(status & 2048) AS IsTrusted,
(status & 256) AS IsEnabled,
OBJECTPROPERTY(id,’CnstIsNotTrusted’) as is_not_trusted,
OBJECTPROPERTY(id,’CnstIsDisabled’) as is_disabled
FROM sysobjects
WHERE type IN (‘C’, ‘F’) –C=Constraint, F=Foreign Key
AND OBJECTPROPERTY(id,’CnstIsNotTrusted’) <> 0
AND OBJECTPROPERTY(id,’CnstIsDisabled’) = 0

 

or

select name, is_disabled, is_not_trusted from sys.foreign_keys

Anúncios

Posted in sql server | Leave a Comment »

Excluir todos os Objetos no SQL Server

Posted by karuta em novembro 19, 2015

Este artigo é uma referência á um script que salvou tempo e serviço em um projeto atual. No qual precisávamos zerar o banco, mas com o detalhe que não poderíamos excluí-lo e criá-lo novamente.

Sendo assim o que este script faz é excluir todos os objetos nesta ordem:

  1. Stored Procedures
  2. Views
  3. Functions
  4. Foreign Keys
  5. Primary Keys
  6. Tables
  7. Indexes

-- Drop all non-system stored procs
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
WHILE @name is not null
BEGIN
SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Procedure: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] &gt; @name ORDER BY [name])
END
GO

-- Drop all views
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped View: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] &gt; @name ORDER BY [name])
END
GO

-- Drop all functions
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Function: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] &gt; @name ORDER BY [name])
END
GO

-- Drop all Foreign Key constraints
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint IS NOT NULL
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)
EXEC (@SQL)
PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME &lt;&gt; @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

-- Drop all Primary Key constraints
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint is not null
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)
EXEC (@SQL)
PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME &lt;&gt; @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

-- Drop all tables
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] &gt; @name ORDER BY [name])
END
GO

-- Drop all indexes
declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
set @command1 = 'DECLARE @indexName NVARCHAR(128)'
set @command1 = @command1 + ' DECLARE @dropIndexSql NVARCHAR(4000)'
set @command1 = @command1 + ' DECLARE tableIndexes CURSOR FAST_FORWARD FOR'
set @command1 = @command1 + ' SELECT name FROM sys.indexes'
set @command1 = @command1 + ' WHERE object_id = OBJECT_ID(''?'') AND index_id &gt; 0 AND index_id &lt; 255 AND is_primary_key = 0'
set @command1 = @command1 + ' ORDER BY index_id DESC'
set @command1 = @command1 + ' OPEN tableIndexes FETCH NEXT FROM tableIndexes INTO @indexName'
set @command1 = @command1 + ' WHILE @@fetch_status = 0'
set @command1 = @command1 + ' BEGIN'
set @command1 = @command1 + ' SET @dropIndexSql = N''DROP INDEX ?.['' + @indexName + '']'''
set @command1 = @command1 + ' EXEC sp_executesql @dropIndexSql'
set @command1 = @command1 + ' print @dropIndexSql'
set @command1 = @command1 + ' FETCH NEXT FROM tableIndexes INTO @indexName'
set @command1 = @command1 + ' END'
set @command1 = @command1 + ' CLOSE tableIndexes'
set @command1 = @command1 + ' DEALLOCATE tableIndexes'
Print '-----------------------------------------'
exec @RETURN_VALUE = sp_MSforeachtable @command1=@command1
GO

Segue o link para o post, no qual encontrei este script:http://paigecsharp.blogspot.com/2008/03/drop-all-objects-in-sql-server-database.html

Posted in sql server | Leave a Comment »

ALTER SCHEMA SQL SERVER FAST

Posted by karuta em novembro 18, 2015

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

Posted in sql server | Leave a Comment »

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.

Posted in sql server | Leave a Comment »