join to remove values in tsql

I was looking at this post at stackoverflow. I found it interesting for those of us who are in to the tsql madness. well newbie sometime and a pro on the next..

update with join from a table with one field

To update a field from other table were we want one that field and we know there is a join.

update tableA

set Email = b.email

from tableA a inner join tableAbkp b on a.UserName = b.Username

where b.UserName not like '%user01%'

so here is an example to do the trick

orphan childs in tsql

Sometimes when we restore a db we include a user on it and it might be as well available at the server we are restoring. Sometimes the user stays orphan so you can't access the database even if you have the same user as the login on the server. Here's a quick workaround for it.

to see wich users are orphan:

sp_change_users_login @Action='Report';

to perform the action:

sp_change_users_login @Action='update_one', @UserNamePattern='',

@LoginName='';

hope this helps .

the query did it

Creating a query tu update a field in a new table with the same data to be filled in with.

--== the query who did the miracle

select 'update operator set operation = %' + Operation+'%', 'where id = ',' ' +id+'','%' from mySourceDataBase.dbo.Operator

--== the result

update operator set operation = '{0}.Contains("{1}")' where id = 1

update operator set operation = '{0}.NotContains("{1}")' where id = 2

update operator set operation = '{0}.StartsWith("{1}")' where id = 3

update operator set operation = '{0}.EndsWith("{1}")' where id = 4

update operator set operation = '{0} == "{1}"' where id = 5

update operator set operation = '{0} != "{1}"' where id = 6

update operator set operation = '{0} > "{1}"' where id = 7

update operator set operation = '{0} < "{1}"' where id = 8

update operator set operation = '{0} >= "{1}"' where id = 9

update operator set operation = '{0} <= "{1}"' where id = 10

update operator set operation = '{0} == ""' where id = 12

update operator set operation = '{0} != ""' where id = 13

update operator set operation = '{0} == {1}' where id = 18

update operator set operation = '{0} != {1}' where id = 19

update operator set operation = '{0} > {1}' where id = 20

update operator set operation = '{0} < {1}' where id = 21

update operator set operation = '{0} >= {1}' where id = 22

update operator set operation = '{0} <= {1}' where id = 23

update operator set operation = '{0} == DateTime.Parse("{1}")' where id = 31

update operator set operation = '{0} != DateTime.Parse("{1}")' where id = 32

update operator set operation = '{0} > DateTime.Parse("{1}")' where id = 33

update operator set operation = '{0} 'fickler' where id = 34

update operator set operation = '{0} >= DateTime.Parse("{1}")' where id = 35

update operator set operation = '{0} <= DateTime.Parse("{1}")' where id = 36

update operator set operation = '{0} = {1}' where id = 57

update operator set operation = '{0} <> {1}' where id = 58

few sql tips

How to count instances of character in SQL Column

I have an sql column that is a string of 100 Y or Ns eg 'YYNYNYYNNNYYNY...'

What is the easiest way to get the number of Ys in each row

tsql

SELECT LEN(REPLACE(myColumn, 'N', '')) FROM ...

SELECT

LEN(REPLACE(ColumnName, 'N', '')) as NumberOfYs

FROM

SomeTable

2. Trouble inserting data

INSERT INTO DestinationTable

(ColumnA, ColumnB, ColumnC, etc.)

SELECT FROM SourceTable

(ColumnA, ColumnB, ColumnC, etc.)

And my source table has 22 million rows.

SQL server fills up my hard drive, and errors out.

Why can't SQL server handle my query?

Should I use a cursor and insert a row at a time?

the solution

INSERT INTO DestinationTable

(ColumnA, ColumnB, ColumnC, etc.)

SELECT TOP 100000 ColumnA, ColumnB, ColumnC, etc.

FROM SourceTable

WHERE NOT EXISTS (SELECT *

FROM DestinationTable

WHERE DestinationTable.KeyCols = SourceTable.KeyCols)

WHILE @@ROWCOUNT <> 0

INSERT INTO DestinationTable

(ColumnA, ColumnB, ColumnC, etc.)

SELECT TOP 100000 ColumnA, ColumnB, ColumnC, etc.

FROM SourceTable

WHERE NOT EXISTS (SELECT *

FROM DestinationTable

WHERE DestinationTable.KeyCols = SourceTable.KeyCols)

Creating an insertion from a Table with Select

trying to insert to an Opertor TABLE that has values already in with another Type wich I'm setting as constant.

SELECT 'insert into Operator Values('+','+ Name +','+ 2 as TypeID ,Operation as Operation from dbo.Operator

From the Result of the query (grid or text) you can apply the result with in Linqpad or Sql management Studio.

droping all connections with sql server

you can kill all the processes using a database:

USE master
go

DECLARE @dbname sysname

SET @dbname = 'name of database you want to drop connections from'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END



If you want to drop all the connections to a database immediately

USE master
GO

ALTER DATABASE database name
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE database name
SET ONLINE



or if you are in a hurry there is an option that says"drop all database connections" as a checkbox in the window displayed for the detach task and you just check it and does close the connections.

triggers with a computed column insertion

I had the problem of inserting a row from a table to another because of a computed column cannot have a explicit value on a insert statement. Then I realize i had to take off the name of the column (computed column) in order to insert the row. I make sense , but since I'm pretty scat this things could happen.


the error was resolved with the following trigger code:

CREATE TRIGGER insertanotaVario

on NotaIngreso

SET IDENTITY_INSERT Nota ON
select (idnota,notadescripcion, curso1, curso2, curso3)
FROM INSERTED
SET IDENTITY_INSERT Nota OFF



// Note: do not add the field that is the average calculated from the column(computed column).

Posteando en StackOverflow

Esta era mi pregunta


I want to convert my float field into a decimal field; I want a precision of 11,2 in my decimal field, but when I tried to change the type of my field(example: Amount) I get an error: "Arithmetic overflow error converting float to data type numeric. The statement has been terminated." My field is decimal(11,2) at the table, and my max and min values are: 1,603,837,393.70 < -- > -1,688,000,000.00(amount).

select Id,AccountId, cast(Amount as decimal(12,2)) as Amount,
cast(AmountB as decimal(12,2)) as  AmountB
FROM myTable

Esta fue la respuesta.

But a value of "1,603,837,393.70" would require decimal(12,2) - 12 digits in all, 2 after the decimal point.

Maybe you misinterpreted the way the decimal(11,2) works? This would mean total of 11 digits - 9 to the left, 2 to the right of the decimal point.

See the MSDN docs for decimal and numeric types:

decimal[ (p[ , s] )] and numeric[ (p[, s] )]

p (precision)

The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point.

traduciendo de vb for aplications a t-sql (ver. 1)

el query de la macro sin el valor de cadena que nos enviand en el texto.


qry = " SELECT GLProcessEntry.ProcessEntryId, GLAccount.MethodId, GLAccount.AccountId, GLAccount.OffSetAccountId,GLProcessEntryDetail.CcyAmount, "
qry = qry & " GLProcessEntryDetail.CADAmount, PeriodCcyRate.Quote, Round([CCyAmount]*[Quote]-[CADAmount],2) AS EntryAmount "
qry = qry & " FROM CfgCieMethodQuoteStatus,GLAccount,GLProcessEntry,GLProcessEntryDetail,PeriodCcyRate "
qry = qry & " WHERE GLProcessEntry.ProcessEntryId = GLProcessEntryDetail.ProcessEntryId "
qry = qry & " AND GLAccount.CieId = GLProcessEntry.CieId "
qry = qry & " AND GLAccount.AccountId = GLProcessEntryDetail.AccountId "
qry = qry & " AND GLAccount.CCy = PeriodCcyRate.Ccy "
qry = qry & " AND GLProcessEntry.PeriodId = PeriodCcyRate.PeriodId "
qry = qry & " AND CfgCieMethodQuoteStatus.QuoteStatusId = PeriodCcyRate.QuoteStatusId "
qry = qry & " AND CfgCieMethodQuoteStatus.MethodId = GLAccount.MethodId "
qry = qry & " AND CfgCieMethodQuoteStatus.CieId = GLAccount.CieId "
qry = qry & " AND GLProcessEntry.ProcessEntryId=" & ProcessEntryId
qry = qry & " AND GLAccount.MethodId=" & MethodId
If MethodId = 1 Then
qry = qry & " AND GLPRocessEntryDetail.AmountTypeId = 0 "
qry = qry & " AND ((Round([CCyAmount]*[Quote]-[CADAmount],2))<>0)"

Else
qry = qry & " AND GLPRocessEntryDetail.AmountTypeId = 1 "
qry = qry & " AND ((Round([CCyAmount]*[Quote]-[CADAmount],2))<>0)"
End If

------
la traduccion al t- sql

SELECT GLProcessEntry.ProcessEntryId, GLAccount.MethodId, GLAccount.AccountId, GLAccount.OffSetAccountId,GLProcessEntryDetail.CcyAmount,
GLProcessEntryDetail.CADAmount, PeriodCcyRate.Quote, Round([CCyAmount]*[Quote]-[CADAmount],2) AS EntryAmount
FROM CfgCieMethodQuoteStatus
INNER JOIN PeriodCcyRate on CfgCieMethodQuoteStatus.QuoteStatusId = PeriodCcyRate.QuoteStatusId
INNER JOIN GLAccount ON CfgCieMethodQuoteStatus.MethodId =GLAccount.MethodId
INNER JOIN GLProcessEntryDetail ON GLAccount.AccountId=GLProcessEntryDetail.AccountId
INNER JOIN RefCompany ON RefCompany.CieId = CfgCieMethodQuoteStatus.CieId
INNER JOIN RefDivision ON GLAccount.DivisionId = RefDivision.DivisionId
INNER JOIN GLProcessEntry ON GLProcessEntry.PeriodId = PeriodCcyRate.PeriodId
AND GLProcessEntry.ProcessEntryId = GLProcessEntryDetail.ProcessEntryId
WHERE GLPRocessEntryDetail.AmountTypeId = 0
and GLProcessEntry.ProcessEntryId = 169
AND GLAccount.MethodId = 1
and GLProcessEntryDetail.CADAmount = 0