Archive

Author Archive

Combine Multiple Rows to a Single Row in TSQL

December 11, 2015 Leave a comment

DECLARE @CUSTNO [varchar](50)
DECLARE @PREV_CUSTNO [varchar](50)
DECLARE @PRICE [decimal](20, 2)
DECLARE @CNT bigint
set @PREV_CUSTNO = ‘DUMMY’
set @CNT = 0

DECLARE db_cursor CURSOR FOR
SELECT CUSTNO, PRICE FROM MyDatabase.myschema.MyColTable

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CUSTNO , @PRICE

WHILE @@FETCH_STATUS = 0
BEGIN

IF @PREV_CUSTNO = @CUSTNO
set @CNT = @CNT + 1
ELSE
set @CNT = 1

set @PREV_CUSTNO = @CUSTNO

IF @CNT = 1
insert into [myschema].[MyRowTable] ([ColAccountNUM],[ColPRICE1],InsertDate) values (@CUSTNO,@PRICE,getdate())
ELSE
DECLARE @sql nvarchar(max) = ‘update [myschema].[MyRowTable] set ColPRICE’+ cast(@CNT as varchar)+’ = ‘ +cast(@PRICE as varchar)+ ‘
where [ColAccountNUM] = ‘ +@CUSTNO
exec (@sql)
print (@sql)

FETCH NEXT FROM db_cursor INTO @CUSTNO , @PRICE
END
CLOSE db_cursor
DEALLOCATE db_cursor

Advertisements
Categories: Uncategorized

solution from web for SSIS “can not convert between unicode and non-unicode string data types”

Categories: Uncategorized

Useful SSIS tips and tricks from the web

Categories: Uncategorized

Copy Excel Data to SQL Server Tables using Copy and Paste

This works and saves lot of headache and time

Try to create the Excel columns and table structure in the same format and structure as ms sql table is. Open the excel select the row (whole row by clicking on row number) select the whole data till end of the data (selected first row to last row). press ctrl+c

edit the ms sql table, go to last row where you can find blank.

Select that row by clicking on row button and press ctrl+v.

Done you’re excel data will be inserted into sql table.

Suggested on Stackoverflow by user3666801

Categories: Uncategorized

Refresh Multiple Excel files in a filder using Powershell

$libraryPath = "\\Shared\Reports"
$allExcelfiles = Get-ChildItem -path $libraryPath -include *.xlsx, *.xls;

foreach ($file in $allExcelfiles)
{
$excel = new-object -comobject Excel.Application
$excel.Visible = $false
$excel.displayAlerts = $false
Start-Sleep -s 1

Write-Host "$file"
$workbookpath = $file.fullname
$excelworkbook = $excel.workbooks.Open($workbookpath)
Start-Sleep -s 1

$excelworkbook.RefreshAll()
Start-Sleep -s 1

$excelworkbook.Save()
Start-Sleep -s 1

$excel.quit()
Start-Sleep -s 1
}

Categories: Uncategorized

When tables where last analyzed in a schema

September 17, 2014 Leave a comment

When tables where last analyzed in a schema

SQL> select TABLE_NAME, LAST_ANALYZED,num_rows,sample_size from DBA_TAB_STATISTICS where OWNER=’TRCASIT;

Also, shows sample size and number of rows

Categories: Uncategorized

Script to output count for all tables in the database – Oracle

set serveroutput on
exec DBMS_OUTPUT.ENABLE (buffer_size => NULL);

DECLARE

cursor c1 is select table_name from user_tables order by 1;
cnt integer;
BEGIN
FOR C in C1
LOOP
execute immediate ‘select count(*) from ‘ || c.table_name into cnt;
dbms_output.put_line(C.TABLE_NAME || ‘ – ‘ || cnt);
END LOOP;
END;

Categories: Uncategorized