Karuta’s ASP & M$ SQLserver

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

Archive for the ‘sql server’ Category

Tratamento de erros no SQL Server (TRY-CATCH)

Posted by karuta em agosto 24, 2018

— Criando tabela para teste 
CREATE TABLE #teste (id int)

— Adicionando restrição à tabela.
— Somente IDs menores do que 10 serão aceitos 

ALTER TABLE #teste ADD CONSTRAINT chkID CHECK (id < 10)

 

— Executando a tarefa em transação
BEGIN TRANSACTION

BEGIN TRY
        
    INSERT INTO #teste VALUES (5)
    INSERT INTO #teste VALUES (2)
    INSERT INTO #teste VALUES (13)  — Inserção de ID inválido

    COMMIT TRANSACTION — Efetivando alterações na base

END TRY
BEGIN CATCH
    
    PRINT ‘Erro ao executar script’
    ROLLBACK TRANSACTION — Desfazendo as alterações na base
          
END CATCH

SELECT * FROM #teste

DROP TABLE #teste

Anúncios

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

Uso de WITH e MERGE para inserção em lote pegando os ID do campo Identity Increment

Posted by karuta em agosto 24, 2018

agenda 1 – tabela com os dados [id_agenda1, nome, telefone]

agenda 2  – tabela nova que vai receber os dados [id_agenda2, nome, telefone]

agenda 3 – id da agenda1 e id criado na agenda 2 [id_agenda1, id_agenda2]

 


;WITH magica
AS (
SELECT * FROM [agenda1]
)
MERGE INTO [agenda2]
USING magica Y
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (nome, telefone)
OUTPUT INSERTED.id_agenda2, Y.id_agenda1  INTO agenda3;

Posted in sql server, store procedure | 2 Comments »

Função para criar JSON a partir de um XML

Posted by karuta em agosto 16, 2018

— Como usar:
SELECT dbo.FlattenedJSON(
(select top 10 artista_id, empresa_id, data_alteracao, login_alteracao from yb_artistas
FOR XML path, root)
)
— —————————————————————————————————————–
CREATE FUNCTION dbo.FlattenedJSON (@XMLResult XML)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE  @JSONVersion NVARCHAR(MAX), @Rowcount INT
SELECT @JSONVersion = , @rowcount=COUNT(*) FROM @XMLResult.nodes(‘/root/*’) x(a)
SELECT @JSONVersion=@JSONVersion+
STUFF(
  (SELECT TheLine FROM
    (SELECT ‘,
    {‘+
      STUFF((SELECT ‘,”‘+COALESCE(b.c.value(‘local-name(.)’, ‘NVARCHAR(255)’),)+‘”:”‘+
       REPLACE( –escape tab properly within a value
         REPLACE( –escape return properly
           REPLACE( –linefeed must be escaped
             REPLACE( –backslash too
               REPLACE(COALESCE(b.c.value(‘text()[1]’,‘NVARCHAR(MAX)’),),–forwardslash
               ‘\’, ‘\\’),
              ‘/’, ‘\/’),
          CHAR(10),‘\n’),
         CHAR(13),‘\r’),
       CHAR(09),‘\t’)
     +‘”‘
     FROM x.a.nodes(‘*’) b(c)
     FOR XML PATH(),TYPE).value(‘(./text())[1]’,‘NVARCHAR(MAX)’),1,1,)+‘}’
   FROM @XMLResult.nodes(‘/root/*’) x(a)
   ) JSON(theLine)
  FOR XML PATH(),TYPE).value(‘.’,‘NVARCHAR(MAX)’ )
,1,1,)
IF @Rowcount>1 RETURN ‘[‘+@JSONVersion+
]’
RETURN @JSONVersion
END

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

Localizar palavras em Store Procedures do SQL SERVER

Posted by karuta em janeiro 31, 2018

TYPE armazena o tipo do objeto a ser localizado, onde :
U => Tabela Usuário
S => Tabela de sistema
P => Procedure
V => View
F => Function

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

UPDATE COLUNA com o resultado da SOMA de outra tabela

Posted by karuta em janeiro 26, 2018

begin tran
UPDATE M
SET M.pago_royalty_arredondado = F.valsum
FROM yb_royalty_pagamento M
INNER JOIN
(
select pagamento_id, sum(valor_pago) as valsum
from yb_royalty_pagamento_integrante
group by pagamento_id) F
ON M.pagamento_id = F.pagamento_id

rollback tran
commit tran

Posted in sql server | Leave a Comment »

SQL SERVER COMMAND LIST ALL TABLES – listar todas as tabelas do banco

Posted by karuta em junho 13, 2017

SELECT * FROM SYSOBJECTS WHERE XTYPE='U' order by name

Posted in sql server | Leave a Comment »

contar os registros com valor do campo repetido

Posted by karuta em junho 12, 2017

select contrato_id, count(contrato_id) 
from yb_contratos
group by contrato_id
having count(contrato_id)>1

Posted in sql server | Leave a Comment »

SQL SERVER MANAGEMENT STUDIO AND EXCEL – COLUMN HEADINGS ON COPY/PASTE

Posted by karuta em fevereiro 7, 2017

Here is a tip about a setting that I think should be on by default, but it isn’t. How many times are you writing T-SQL in SQL Server Management Studio (SSMS) and you need to just copy the results out, paste to Excel and either do more analysis or send off to someone. What happens when you have a result set with 20 columns? 30? 50?

Well, the old way to get the column headings in Excel was to just type them in. Ouch. This gets old realllly fast. But there is a setting in SSMS that most people don’t even know exists, and then once they find out about it, they are like, dang! I wish I would have known about that.

Well, fire up SSMS, Tools->Options, Query Results, SQL Server, Results to Grid. Then check the “Include column headers when copying or saving the results” Hit OK, and the any new query window you have will automatically copy the column headings from your result set with the data!

 

image

Posted in sql server | Leave a Comment »

SQL SERVER 2008 – SAVING CHANGES IS NOT PERMITTED

Posted by karuta em fevereiro 7, 2017

Finally getting around to doing some work on SQL 2008, and after about 3 minutes, I run into this error: “Saving Changes is not permitted.. blah blah blah” See screenshot below.

image

This is different than SQL 2005. Microsoft maybe trying to save us from ourselves? The thing is, I never “enabled the option Prevent saving changes that require the table to be re-created” – it seems to be enabled by default. It would be awesome if this error told me exactly where the setting was.

 

Well, it happes to be in Tools->Options, Designers, Table and Database Designers. Uncheck the box and go about your merry way!

 

image

Posted in sql server | Leave a Comment »

SQL Server – Running large script files

Posted by karuta em outubro 30, 2016

This tool (Big SQL Script File Runner) on CodePlex will run any size script file with log and GUI.

Posted in sql server | Leave a Comment »