Combine Multiple Rows to a Single Row in TSQL
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
solution from web for SSIS “can not convert between unicode and non-unicode string data types”
Useful SSIS tips and tricks from the web
http://www.bidn.com/blogs/kylewalker/ssis/997/setting-up-an-ssis-package-with-a-dymanic-excel-source
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
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
}
When tables where last analyzed in a schema
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
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;
Generate Table and Constraint DDLs – Oracle
Generate Table DDL Oracle
Generate data insert script as insert into
Declare
v_table_name varchar2(30) := ‘MY_TABLE’; — Your Tablename
v_column_list varchar2(4000);
v_insert_list varchar2(4000);
v_ref_cur_columns varchar2(4000);
v_ref_cur_query varchar2(4000);
v_ref_cur_output varchar2(4000);
V_Column_Name Varchar2(4000);
Cursor C1 Is Select Column_Name, Data_Type From DBA_Tab_Columns Where Table_Name = V_Table_Name Order By Column_Id;
refcur sys_refcursor;
begin
for i in c1 loop
v_column_list := v_column_list||’,’||i.column_name;
if i.data_type = ‘NUMBER’ then
v_column_name := i.column_name;
elsif i.data_type = ‘DATE’ then
v_column_name :=
chr(39)||’to_date(‘||chr(39)||’||chr(39)’||’||to_char(‘||i.column_name||’,’||chr(39)||’dd/mm/yyyy hh:mi:ss’||chr(39)||’)||chr(39)||’||chr(39)||’, ‘||chr(39)||’||chr(39)||’||chr(39)||’dd/mm/rrrr hh:mi:ss’||chr(39)||’||chr(39)||’||chr(39)||’)’||chr(39);
elsif i.data_type = ‘VARCHAR2’ then
v_column_name := ‘chr(39)||’||i.column_name||’||chr(39)’;
end if;
v_ref_cur_columns := v_ref_cur_columns||’||’||chr(39)||’,’||chr(39)||’||’||v_column_name;
end loop;
v_column_list := ltrim(v_column_list,’,’);
v_ref_cur_columns := substr(v_ref_cur_columns,8);
V_Insert_List := ‘INSERT INTO ‘||V_Table_Name||’ (‘||V_Column_List||’) VALUES ‘;
V_Ref_Cur_Query := ‘SELECT ‘||V_Ref_Cur_Columns||’ FROM ‘||V_Table_Name;
open refcur for v_ref_cur_query;
loop
fetch refcur into v_ref_cur_output;
exit when refcur%notfound;
v_ref_cur_output := ‘(‘||v_ref_cur_output||’);’;
v_ref_cur_output := replace(v_ref_cur_output,’,,’,’,null,’);
v_ref_cur_output := replace(v_ref_cur_output,'(,’,'(null,’);
v_ref_cur_output := replace(v_ref_cur_output,’,,)’,’,null)’);
v_ref_cur_output := replace(v_ref_cur_output,’null,)’,’null,null)’);
v_ref_cur_output := REPLACE(v_ref_cur_output,’,);’,’,null);’);
v_ref_cur_output := v_insert_list||v_ref_cur_output;
dbms_output.put_line (v_ref_cur_output);
end loop;
End;
/
Job scheduling in Oracle
exec dbms_scheduler.create_job(‘upd_dosrub_job’, job_type=>’plsql_block’, job_action=>’ dropme_upd_dosrub;’, start_date => ’18-JUL-14 9.00.00PM US/Pacific’,enabled=>true);
exec dbms_scheduler.set_attribute(‘upd_dosrub_job’, ‘max_run_duration’ , interval ’50’ hours);
select * from DBA_SCHEDULER_JOBS where job_name=’upd_dosrub_job’;
TekyBlog 