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)