SELECT InventoryCode,InventoryName Product, isnull(Price,0) Price
FROM Erp_Inventory
left join Erp_InventoryPriceList on Erp_InventoryPriceList.InventoryId = Erp_Inventory.RecId
FOR XML PATH('MALZEMELER'), ROOT('BILGILER')
Category: Sql
Büyük tablodan sqlden verileri silme
/*bu etkili alttakiler deil fazla*/
declare @counter int
declare @numOfRecords int
declare @batchsize int
set @numOfRecords = (SELECT COUNT(*) AS NumberOfRecords FROM Erp_InventoryReceiptItem with(nolock) where ParentItemId is not null)
set @counter = 0
set @batchsize = 2500
set rowcount @batchsize
while @counter < (@numOfRecords/@batchsize) +1
begin
set @counter = @counter + 1
Update Erp_InventoryReceiptItem set ParentItemId = null;
end
set rowcount 0
----------------------------------------------
/*alttakiler:*/
USE LIVE_UNIMAR_YENI
GO
-- Set to simple mode
ALTER DATABASE LIVE_UNIMAR_YENI SET RECOVERY SIMPLE;
GO
-- Get count of records
SELECT COUNT(*) AS Total FROM Erp_InventoryReceiptItem
GO
-- Delete in batches
DECLARE @VAR_ROWS INT = 1;
WHILE (@VAR_ROWS > 0)
BEGIN
DELETE TOP (100000) FROM Erp_InventoryReceiptItem
SET @VAR_ROWS = @@ROWCOUNT;
CHECKPOINT;
END;
GO
-- Set to full mode
ALTER DATABASE LIVE_UNIMAR_YENI SET RECOVERY FULL;
GO
SENTEZ V6 HAREKET GÖRMEYEN ÜRÜNLERİ SİLME
DELETE from SKART FROM SKART LEFT JOIN SFISD ON SFISD.MKOD = SKART.KOD
WHERE SFISD.MKOD IS NULL
version 6 depo bazlı stoklar ve cari bakiyeleri
depo bazlı ürünler
select DEPO.ADI WarehouseName,SKART.KOD URUN_KODU,SKART.ADI URUN_ADI,SUM((TGIRIS-TCIKIS)) Quantity
from SKARTK
LEFT JOIN SKART ON SKARTK.KOD = SKART.KOD
left join DEPO ON SKARTK.DEPO = DEPO.KOD
where (SKARTK.SIRKET='08' AND SKART.SIRKET='08' AND DEPO.SIRKET = '08')
where (SKARTK.SIRKET='_ActiveCompanyId_' AND SKART.SIRKET='_ActiveCompanyId_' AND DEPO.SIRKET = '_ActiveCompanyId_')
GROUP BY DEPO.ADI,SKART.ADI,SKART.KOD
cari bakiyeleri
select CKART.KOD,CKART.ADI,ISNULL(sum(cast(CARID.BORC AS DECIMAL(10,2)) - cast(CARID.ALACAK AS DECIMAL(10,2))),0) BAKIYE from CKART left join CARID on (CKART.SIRKET=CARID.SIRKET and CKART.KOD=CARID.CKOD) where
--CKART.SIRKET='08' AND
CKART.SIRKET='_ActiveCompanyId_' AND
CKART.SAYI1 >= 0 and CKART.SAYI1 <= 999999999999999 and
CKART.SAYI2 >= 0 and CKART.SAYI2 <= 999999999999999 and
CKART.SAYI3 >= 0 and CKART.SAYI3 <= 999999999999999 and
CKART.SAYI4 >= 0 and CKART.SAYI4 <= 999999999999999 and
CKART.SAYI5 >= 0 and CKART.SAYI5 <= 999999999999999 and INUSE=1
GROUP BY CKART.KOD,CKART.ADI
ORDER BY CKART.ADI
Sentez Dev Mizan Excele
excelde
data -> from other source -> from microsoft query>new data source ilişklendirmeyi yap sorguyada aşağıdakileri yapıştır…
data -> from other source -> from microsoft query>new data source ilişklendirmeyi yap sorguyada aşağıdakileri yapıştır…
profilerda mizanı çekerken kod:
exec sp_executesql N' if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp create table #GLReceiptBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,ForexDebit decimal(28,8),ForexCredit decimal(28,8),ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8),ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8)) insert into #GLReceiptBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,ForexDebit,ForexCredit,ForexDebitBalance,ForexCreditBalance ,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance,InflowQty,OutflowQty) select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,isnull(sum(AR.Debit),0) Debit,0 Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,isnull(sum(AR.ForexAmount),0)ForexDebit,0 ForexCredit,isnull(sum(AR.ForexAmount),0)ForexDebitBalance,0 ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,isnull(sum(AR.Quantity),0)InflowQty,0 OutflowQty from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId) left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) and (AR.IsDeleted is null or AR.IsDeleted = 0) and AR.Debit 0 and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5) and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2 group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName
union all select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,0 Debit,isnull(sum(AR.Credit),0) Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,0 ForexDebit,isnull(sum(AR.ForexAmount),0) ForexCredit,0 ForexDebitBalance,isnull(sum(AR.ForexAmount),0) ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,0 InflowQty,isnull(sum(AR.Quantity),0) OutflowQty from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId) left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) and (AR.IsDeleted is null or AR.IsDeleted = 0) and AR.Credit 0 and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5) and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2 group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp create table #GLAccountBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,DBalance decimal(28,8),CBalance decimal(28,8),ForexDebit decimal(28,8),ForexCredit decimal(28,8) ,ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8),FDBalance decimal(28,8),FCBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8) ,ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8),FCDBalance decimal(28,8),FCCBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8)) insert into #GLAccountBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,DBalance,CBalance,ForexDebit,ForexCredit ,ForexDebitBalance,ForexCreditBalance,FDBalance,FCBalance,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance ,FCDBalance,FCCBalance,InflowQty,OutflowQty) select CompanyId,AccountId,AccountCode,AccountName,isnull(sum(Debit),0)Debit,isnull(sum(Credit),0)Credit,isnull(sum(DebitBalance),0)DebitBalance,isnull(sum(CreditBalance),0)CreditBalance ,case when isnull(sum(DebitBalance),0)>isnull(sum(CreditBalance),0) then round(isnull(sum(DebitBalance),0)-isnull(sum(CreditBalance),0),2) else 0 end DBalance ,case when isnull(sum(DebitBalance),0)isnull(sum(ForexCreditBalance),0) then round(isnull(sum(ForexDebitBalance),0)-isnull(sum(ForexCreditBalance),0),2) else 0 end FDBalance ,case when isnull(sum(ForexDebitBalance),0)isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateDebitBalance),0)-isnull(sum(ForexCalculateCreditBalance),0),2) else 0 end FCDBalance ,case when isnull(sum(ForexCalculateDebitBalance),0)<isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateCreditBalance),0)-isnull(sum(ForexCalculateDebitBalance),0),2) else 0 end FCCBalance ,isnull(sum(InflowQty),0)InflowQty,isnull(sum(OutflowQty),0)OutflowQty from #GLReceiptBalanceTmp group by CompanyId,AccountId,AccountCode,AccountName having round(isnull(sum(Debit),0),2) round(isnull(sum(Credit),0),2) select A.CompanyId,'''' [Şirket],isnull((select MF.ForexCode from Meta_Forex MF where MF.RecId=A.ForexId),'''') [Dvz],'''' [HDvz],A.AccountCode [Hesap Kodu],A.AccountName [Hesap Adı],A.SpecialCode [Özel Kod],0.0 [Devir Borç],0.0 [Devir Alacak],sum(isnull(B.Debit,0)) [Borç],sum(isnull(B.Credit,0)) [Alacak],case when sum(isnull(B.DBalance,0)) > sum(isnull(B.CBalance,0)) then sum(isnull(B.DBalance,0)) - sum(isnull(B.CBalance,0)) when sum(isnull(B.DBalance,0)) sum(isnull(B.CBalance,0)) then ''BB'' when sum(isnull(B.DBalance,0)) sum(isnull(B.FCBalance,0)) then round(sum(isnull(B.FDBalance,0)) - sum(isnull(B.FCBalance,0)),2) when sum(isnull(B.FDBalance,0)) sum(isnull(B.FCBalance,0)) then ''BB'' when sum(isnull(B.FDBalance,0)) sum(isnull(B.FCCBalance,0)) then round(sum(isnull(B.FCDBalance,0)) - sum(isnull(B.FCCBalance,0)),2)when sum(isnull(B.FCDBalance,0)) sum(isnull(B.FCCBalance,0)) then ''BB'' when sum(isnull(B.FCDBalance,0)) < sum(isnull(B.FCCBalance,0)) then ''AB'' else '''' end [Hesaplanan Döviz Bakiye Tipi],sum(isnull(B.FCDBalance,0)) [Hesaplanan Döviz Borç Bakiye],sum(isnull(B.FCCBalance,0)) [Hesaplanan Döviz Alacak Bakiye],sum(isnull(B.InflowQty,0)) [Giriş Miktarı],sum(isnull(B.OutflowQty,0)) [Çıkış Miktarı],sum(isnull(B.InflowQty,0)) - sum(isnull(B.OutflowQty,0)) [Kalan],-1 CAccount from Erp_GLAccount A with (nolock) left join #GLAccountBalanceTmp B on (B.AccountId in(select AA.RecId from Erp_GLAccount AA where AA.CompanyId=A.CompanyId and AA.AccountCode like rtrim(A.AccountCode)+''%'' )) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) group by A.CompanyId,A.ForexId,A.AccountCode,A.AccountName,A.SpecialCode having round(isnull(sum(B.Debit),0),2) round(isnull(sum(B.Credit),0),2) order by A.AccountCode
if object_id(''tempdb..#GLReceiptTmp'') is not null drop table #GLReceiptTmp if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp ',N'@0ARErp_GLReceiptItemReceiptDate1 datetime,@0ARErp_GLReceiptItemReceiptDate2 datetime',@0ARErp_GLReceiptItemReceiptDate1='2017-01-01 00:00:00',@0ARErp_GLReceiptItemReceiptDate2='2018-12-31 23:59:00'
sql server kill and rename database
--TEST DATABASE AKTİF ET
ALTER DATABASE LKSDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_rename 'LKSDB', 'LKSDBORJ' ,'DATABASE';
GO
ALTER DATABASE LKSDBORJ
SET MULTI_USER
GO
ALTER DATABASE LKSDBTEST
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_rename 'LKSDBTEST', 'LKSDB' ,'DATABASE';
GO
ALTER DATABASE LKSDB
SET MULTI_USER
GO
--ORJİNAL DB AKTİF ET
ALTER DATABASE LKSDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_rename 'LKSDB', 'LKSDBTEST' ,'DATABASE';
GO
ALTER DATABASE LKSDBTEST
SET MULTI_USER
GO
ALTER DATABASE LKSDBORJ
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_rename 'LKSDBORJ', 'LKSDB' ,'DATABASE';
GO
ALTER DATABASE LKSDB
SET MULTI_USER
GO
Visual studio 2015le oracle db bağlantısı
Oracle Developer Tools for Visual Studio 2015 indir http://www.oracle.com/technetwork/developer-tools/visual-studio/overview/index.html
kur ve kodlar
dbconnect.txt
Data Source=ORCL;User Id=hr;Password=hr;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Oracle.ManagedDataAccess;
using Oracle.ManagedDataAccess.Client;
namespace OracleTest
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
string oradb;
private void Form1_Load(object sender, EventArgs e)
{
//bağlantı
StreamReader sr = new StreamReader("dbConnect.txt");
oradb = sr.ReadLine();
sr.Close();
}
private void button1_Click(object sender, EventArgs e)
{
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
StreamReader sr = new StreamReader("sql1.txt");
string sql1 = sr.ReadLine();
sr.Close();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sql1;
cmd.CommandType = CommandType.Text;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ss");
gridControl1.DataSource = ds.Tables["ss"];
//OracleDataReader dr = cmd.ExecuteReader();
//dataGridView1.DataSource = dr;
//while (dr.Read())
//{
// listBox1.Items.Add(dr.GetString(2));
//}
//conn.Dispose();
}
}
}
SQL 2014TEN 2008E VERİTABANI KOPYALA
sql server 2014’ten 2008e database kopyalama
önce sql server 2014’ten isdenilen databasee sağ tıklanıp
task
generate script
bütün tablolar
advanceddan schema and table ve serve version 2008 yapılır
next next finish yapılır
nereye kaydedildiyse o script usbye kopyalanır cmdyi yönetici olarak açıp örnek olarak aşağıdaki komut yazilir
sqlcmd -S DESKTOP-VFSMD2M\SQL2008 -i “d:/c.sql”
not sql scriptinin içinden veritabanı adını deiştir alter malter yazan
Visual c# ms sql database çifte kontrol
SqlConnection connectionString = new SqlConnection("Server = " + ondegerler.SQLBAGLANTISI_KAYNAK + "; Database = " + ondegerler.SQLBAGLANTISI_VERITABANI + "; User Id = " + ondegerler.SQLBAGLANTISI_KULLANICI + "; Password = " + ondegerler.SQLBAGLANTISI_PAROLA);
connectionString.Open();
{
SqlCommand sqlcom2 = new SqlCommand("select count(malzeme_kodu) from tbl_malzeme where tbl_malzeme.malzeme_kodu = '" + Text_malzeme_kodu.Text + "'", connectionString);
if (sqlcom2.ExecuteScalar() != null)
{
int UserExist = (int)sqlcom2.ExecuteScalar();
if (UserExist > 0)
{
MessageBox.Show("Bu İsimde Malzeme Kodu Daha Önceden Yaratılmıştır!");
}
}
visual c# update ms sql table from query inside txt file
// MessageBox.Show(Text_malzeme_grubu.EditValue.ToString());
// string connectionString = null;
SqlConnection connectionString = new SqlConnection("Server = " + ondegerler.SQLBAGLANTISI_KAYNAK + "; Database = " + ondegerler.SQLBAGLANTISI_VERITABANI + "; User Id = " + ondegerler.SQLBAGLANTISI_KULLANICI + "; Password = " + ondegerler.SQLBAGLANTISI_PAROLA);
connectionString.Open();
FileInfo file = new FileInfo(Application.StartupPath + "\\tbl_cari_kartlari_duzenle_kaydet.txt");
string duzenle = file.OpenText().ReadToEnd();
//MessageBox.Show(silinecekkod.ToString());
SqlCommand sqlcom = new SqlCommand(duzenle, connectionString);
sqlcom.Parameters.AddWithValue("@cari_turu", Text_cari_turu.Text.Trim());
sqlcom.Parameters.AddWithValue("@cari_adi", Text_cari_adi.Text.Trim());
sqlcom.Parameters.AddWithValue("@irtibat", Text_irtibat.Text.Trim());
sqlcom.Parameters.AddWithValue("@adres", Text_adres.Text.Trim());
sqlcom.Parameters.AddWithValue("@paremetre", text_paremetre.Text.Trim());
sqlcom.Parameters.AddWithValue("@cari_kodu", Text_cari_kodu.Text.Trim());
sqlcom.ExecuteNonQuery();
connectionString.Close();
MessageBox.Show("Cari Başarıyla Düzenlendi!", "CARİ KARTI DÜZENLEME", MessageBoxButtons.OK, MessageBoxIcon.Information);
/*txt file =*/
update tbl_cariler
set cari_turu=@cari_turu , cari_adi = @cari_adi ,irtibat=@irtibat , adres = @adres, paremetre = @paremetre
where cari_kodu = @cari_kodu
Blog of Sem 