[SQL]SQLServerにて一時保存テーブルを扱う。

一瞬だけ取り込んで加工したい場合…
一時保存テーブルですが
きちんと消えているかはちゃんと確認したほうがいいです。
（念の為一応前後にDROP句をつけています）

  
---------------------------------------------

 BEGIN TRAN TEST;
   
   --ワークの事前削除 
   IF OBJECT_ID('tempdb..#TMP_取込ワーク') IS NOT NULL DROP TABLE #TMP_取込ワーク;
   
   --ワークの生成
   CREATE TABLE #TMP_取込ワーク 
   (Folder1 varchar(100), 
    Folder2 varchar(100),
Folder3 varchar(100),
Folder4 varchar(100),
Folder5 varchar(100),
Folder6 varchar(100),
Level varchar(100),
Size varchar(100),
Percentage varchar(100));
   
   
   --ここを取り込みプログラムにしてみたり
   INSERT INTO #TMP_取込ワーク VALUES
    ( '24245' ,'1503AK1502D','仕様書.xls'  , NULL, NULL, NULL,NULL,'1',NULL );
   INSERT INTO #TMP_取込ワーク VALUES
    ( '24245' ,'1403A44674D','お見積り.xls'  , NULL, NULL, NULL,NULL,'1',NULL );


   --できたデータをここで加工したり
    SELECT *
    FROM   #TMP_取込ワーク  


   --一応終わったら消す
   DROP TABLE #TMP_取込ワーク  

   --ROLLBACK TRAN TEST;
   COMMIT TRAN TEST;

---------------------------------------------

[その他]SQLServerとExcelマクロを使ってリバースER図を作成する。（エンティティのみ）

①まずは以下のSQL文を↓SQLServer上指定のDB上にて実行しましょう

SELECT
object_name(sys.columns.object_id),
sys.columns.column_id,
sys.columns.name,
sys.indexes.name AS INDEX_NAME,
sys.indexes.is_primary_key AS TEST

FROM
sys.columns inner join sys.objects on
sys.columns.object_id = sys.objects.object_id


LEFT JOIN
sys.index_columns
on sys.columns.column_id = sys.index_columns.column_id
and sys.columns.object_id = sys.index_columns.object_id

LEFT JOIN sys.indexes
on sys.indexes.object_id = sys.index_columns.object_id

where

ISNULL(sys.indexes.is_primary_key ,-1) IN (-1,1)
and
sys.objects.type = 'U'

--もしER図に出したくないカラムがある場合はここで指定

--and
--sys.columns.name NOT IN ( --'created',
--'テスト項目',
--'削除フラグ'
--)

--テーブルしぼるならここで！

--and
--LEFT(object_name(sys.columns.object_id),8) IN (
--'T_氏名'
--'T_給与'
--)


order by
object_name(sys.columns.object_id),
column_id

②出力された結果をエクセルに貼り付けて以下の様にします
　（必ずＡ１セルから貼り付けてください）

③貼り付けたシート上で以下のＶＢＡマクロを実行します。

Dim WB As Workbook
Set WB = ActiveWorkbook
Dim WS1 As Worksheet
Set WS1 = WB.ActiveSheet

WS1.Select
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select

'--------------------------------------------------------
'実行速度向上のため画面更新と自動計算を停止
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'-----------------------------------------------------------

'現在選択しているセルの取得
Dim intStartGyo As Integer: intStartGyo = Selection.Row
Dim intLastGyo As Integer: intLastGyo = Selection.Row + Selection.Rows.Count - 1
Dim intStartClm As Integer: intStartClm = Selection.Column
Dim intLastClm As Integer: intLastClm = Selection.Column + Selection.Columns.Count - 1

'※初期置
Dim Rng As Range
Set Rng = WS1.Cells(intStartGyo, intStartClm)


Dim lWidth As Long: lWidth = 150 'エンティティオブジェクトのサイズ
Dim lHeight As Long: lHeight = 14 'エンティティオブジェクトのサイズ

lLeft = Rng.Left + 350 '初期位置
Ltop = Rng.Top + 10 '初期位置

Dim TblCnt As Integer: TblCnt = 0
Dim TblTitle As String
Dim TblStartTop As Integer
Dim ECnt As Integer
Dim strShapes As String
Dim FlgKey As Boolean

For j = intStartGyo To intLastGyo

'テーブルカウント
If TblTitle <> WS1.Cells(j, intStartClm).Text Then
TblCnt = TblCnt + 1
TblTitle = WS1.Cells(j, intStartClm).Text
ECnt = 0
strShapes = ""
Ltop = WS1.Cells(j + 1, intStartClm).Top + 10
TblStartTop = Ltop
FlgKey = True
End If

'エンティティの作成---------------------------------------

Dim myShape1 As Shape

'主キーからそうでなくなった場合、線を足す
If WS1.Cells(j, intStartClm + 4).Text <> "1" Then
If FlgKey = True Then
Dim SShape As Shape
Set SShape = WS1.Shapes.AddConnector(msoConnectorStraight, lLeft, Ltop, lLeft + lWidth, Ltop)
strShapes = strShapes & "," & SShape.Name
FlgKey = False
End If
End If


Dim oShape As Shape
Set oShape = WS1.Shapes.AddShape(msoShapeRectangle, lLeft, Ltop, lWidth, lHeight)
With oShape
.Fill.Visible = msoFalse
.Fill.Solid 'グラデーション等無し
.Line.Visible = msoFalse
.TextFrame.Characters.Font.Color = vbBlack
If WS1.Cells(j, intStartClm + 4).Text <> "1" Then
.TextFrame.Characters.Text = WS1.Cells(j, intStartClm + 2).Text
Else
.TextFrame.Characters.Text = "■" & WS1.Cells(j, intStartClm + 2).Text
End If
.TextFrame.Characters.Font.Size = 8
.TextFrame.HorizontalAlignment = xlHAlignLeft
End With
strShapes = strShapes & "," & oShape.Name


'追加位置を下へずらす
Ltop = Ltop + lHeight
ECnt = ECnt + 1
'-------------------------------------------------------

'次が異なるテーブルor終了行---------------------------------
If Len(strShapes) > 0 Then
If WS1.Cells(j + 1, intStartClm).Text <> TblTitle Or j + 1 > intLastGyo Then


Set oShape = WS1.Shapes.AddShape(msoShapeRectangle, lLeft, TblStartTop - 20, lWidth, 30 + ECnt * (lHeight + 1))
With oShape
.Fill.Visible = msoFalse
.Fill.Solid 'グラデーション等無し
.Line.Visible = msoTrue
.TextFrame.Characters.Font.Color = vbBlue
.TextFrame.Characters.Text = TblTitle
.TextFrame.Characters.Font.Size = 8
.TextFrame.HorizontalAlignment = xlHAlignLeft
End With
'strShapes(ECnt) = oShape.Name
'ActiveSheet.Shapes.Range(Array("""" & oShape.Name & """" & strShapes)).Select
strShapes = strShapes & "," & oShape.Name


Dim valShapes() As String
valShapes = Split(strShapes, ",")


'oShape.Select
'Selection.ShapeRange.Group.Select
WS1.Shapes.Range(valShapes).Group

'追加位置を右へずらす
'lLeft = lLeft + lWidth + 5
'追加位置を下へずらす
Ltop = Ltop + 30 + ECnt * (lHeight + 1) + 10

End If
End If
'------------------------------------------------------------

Next

'実行速度向上のため画面更新と自動計算を再開---------------
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'-----------------------------------------------------------

MsgBox ("作成成功")



End Sub

④以下の様にER図用のエンティティ図形が出来ます。

[その他]MK-2からコピーしたスキーマ情報からER図用のエンティティオブジェクトを作成するマクロ

Sub MR2ERtoExcelER()

 
    MsgBox "※変換したい情報を全部選択しておく必要があります※"

    Dim WB As Workbook
    Set WB = ActiveWorkbook
    Dim WS1 As Worksheet
    Set WS1 = WB.ActiveSheet
 
    '--------------------------------------------------------
    '実行速度向上のため画面更新と自動計算を停止
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    '-----------------------------------------------------------

    '現在選択しているセルの取得
    Dim intStartGyo As Integer: intStartGyo = Selection.Row
    Dim intLastGyo As Integer: intLastGyo = Selection.Row + Selection.Rows.Count - 1
    Dim intStartClm As Integer: intStartClm = Selection.Column
    Dim intLastClm As Integer: intLastClm = Selection.Column + Selection.Columns.Count - 1
 
    '※初期置
    Dim Rng As Range
    Set Rng = WS1.Cells(intStartGyo, intStartClm)
 
 
    Dim lWidth As Long: lWidth = 150  'エンティティオブジェクトのサイズ
    Dim lHeight  As Long: lHeight = 14  'エンティティオブジェクトのサイズ

    lLeft = Rng.Left + 350 '初期位置
    Ltop = Rng.Top + 10 '初期位置
 
    Dim TblCnt As Integer: TblCnt = 0
    Dim TblTitle As String
    Dim TblStartTop As Integer
    Dim ECnt As Integer
    Dim strShapes As String
    Dim FlgKey As Boolean
 
    For j = intStartGyo To intLastGyo
               
            'テーブルカウント
            If WS1.Cells(j, intStartClm).Text = "[Entity]" Then
                TblCnt = TblCnt + 1
                TblTitle = Replace(WS1.Cells(j + 1, intStartClm).Text, "PName=", "")
                ECnt = 0
                strShapes = ""
                Ltop = WS1.Cells(j + 1, intStartClm).Top + 10
                TblStartTop = Ltop
                FlgKey = True
            End If
                                           
            'エンティティの作成---------------------------------------
            If Left(WS1.Cells(j, intStartClm).Text, 6) = "Field=" Then
         
              Dim myShape1 As Shape
              Dim strText() As String
              strText = Split(WS1.Cells(j, intStartClm).Text, ",")
           
           
                '主キーからそうでなくなった場合、線を足す
                If strText(4) = "" Then
                    If FlgKey = True Then
                        Dim SShape As Shape
                        Set SShape = WS1.Shapes.AddConnector(msoConnectorStraight, lLeft, Ltop, lLeft + lWidth, Ltop)
                        strShapes = strShapes & "," & SShape.Name
                        FlgKey = False
                    End If
                End If
           
           
              Dim oShape As Shape
              Set oShape = WS1.Shapes.AddShape(msoShapeRectangle, lLeft, Ltop, lWidth, lHeight)
              With oShape
                .Fill.Visible = msoFalse
                .Fill.Solid 'グラデーション等無し
                .Line.Visible = msoFalse
                .TextFrame.Characters.Font.Color = vbBlack
                If strText(4) = "" Then
                    .TextFrame.Characters.Text = Replace(strText(1), """", "")
                Else
                    .TextFrame.Characters.Text = "■" & Replace(strText(1), """", "")
                End If
                .TextFrame.Characters.Font.Size = 8
                .TextFrame.HorizontalAlignment = xlHAlignLeft
              End With
              strShapes = strShapes & "," & oShape.Name
           
           
              '追加位置を下へずらす
              Ltop = Ltop + lHeight
              ECnt = ECnt + 1
            End If
            '-------------------------------------------------------
               
            '次が異なるテーブルor終了行---------------------------------
            If Len(strShapes) > 0 Then
            If WS1.Cells(j + 1, intStartClm).Text = "[Entity]" Or j + 1 = intLastGyo Then
           
               
              Set oShape = WS1.Shapes.AddShape(msoShapeRectangle, lLeft, TblStartTop - 20, lWidth, 30 + ECnt * (lHeight + 1))
              With oShape
                .Fill.Visible = msoFalse
                .Fill.Solid 'グラデーション等無し
                .Line.Visible = msoTrue
                .TextFrame.Characters.Font.Color = vbBlue
                .TextFrame.Characters.Text = TblTitle
                .TextFrame.Characters.Font.Size = 8
                .TextFrame.HorizontalAlignment = xlHAlignLeft
              End With
              'strShapes(ECnt) = oShape.Name
              'ActiveSheet.Shapes.Range(Array("""" & oShape.Name & """" & strShapes)).Select
              strShapes = strShapes & "," & oShape.Name
           
           
              Dim valShapes() As String
              valShapes = Split(strShapes, ",")
         
           
              'oShape.Select
              'Selection.ShapeRange.Group.Select
              WS1.Shapes.Range(valShapes).Group
               
                 '追加位置を右へずらす
                'lLeft = lLeft + lWidth + 5
                '追加位置を下へずらす
                Ltop = Ltop + 30 + ECnt * (lHeight + 1) + 10
         
            End If
            End If
            '------------------------------------------------------------
 
    Next

    '実行速度向上のため画面更新と自動計算を再開---------------
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    '-----------------------------------------------------------


End Sub

[SQL]他テーブルを利用して更新するＳＱＬ文その３

苦手な他テーブルを使ったUPDATE文その３。続きもの。


で、３にて最終形態。観念してEXIST文を使う。

----------------------------------
①EXISTにて他テーブルでの存在をチェックしつつ
②更新内容には他テーブル（まぁ一緒なんだけど）の内容を利用するSQL文。
----------------------------------

UPDATE テーブル１
SET
 項目１ = B1.項目１,
 項目２= B1.項目２,
FROM
 テーブル２ AS B1
WHERE
  テーブル１.主キー = 1 and B1.主キー = 0
 AND
 EXISTS
 ( SELECT * FROM  テーブル２ AS B2 WHERE テーブル１.主キー = B2.主キー  )

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
①の存在チェックはＢ２にてしつつ、
②の内容をとってくるのはＢ１にてしているという概要。
元をただせばＢ１もＢ２も同じテーブルなんだけど。

[SQL]他テーブルを利用して更新するＳＱＬ文その２

相変わらず苦手なＳＱＬ文。


①他テーブルを利用する基本の形は（SQLServerにて）
'--------------------------
UPDATE  T_テストテーブル SET 区分 = 2
FROM  T_参照テーブル AS B
where
T_テストテーブル.伝票No =  B.伝票No  AND B.区分 = 2
'--------------------------

②どうも確認する術がないので

WITH文を使って一度確認したいなら

'--------------------------

with B as (

SELECT  B2.伝票No

FROM Ｔ_参照テーブル AS B1 LEFT JOIN T_テストテーブル AS B2  ON B1.伝票No = B2.伝票No 

WHERE 

B1.区分 = 2

)

'--------------------------

で、確認してから

'--------------------------

with B as (

SELECT  B2.伝票No

FROM Ｔ_参照テーブル AS B1 LEFT JOIN T_テストテーブル AS B2  ON B1.伝票No = B2.伝票No 

WHERE 

B1.区分 = 2

)

UPDATE  T_テストテーブル

 SET     (区分 = 2)

 where T_テストテーブル.伝票No  IN (SELECT * FROM B)

'--------------------------

というか、結局は
②をやるならEXIST文使えという話になるのでした。

[SQL]指定したグループ別に通し番号を振る。

よく忘れるシリーズ。

SQL Serverにて指定したグループで通し番号を振るには
ROW_NUMBER() を使用します。
OVER（）内でグループ単位を指定（複数もＯＫ）、通し順を指定します。

'---------------------------------------------

SELECT
   ROW_NUMBER() OVER( PARTITION BY 市, 町  ORDER BY 引っ越して来た日 ) AS SEQ 
   名前, 市, 町, 引っ越して来た日
FROM
  住民台帳
ORDER BY
  市, 町, 引っ越して来た日

'---------------------------------------------

結果は

1,田中,広島市,南町,2012/01/01
2,佐藤,広島市,南町,2012/01/03
1,竹田,広島市,中町,2012/01/02
1,田中,福山市,北町,2012/01/02
2,田中,福山市,北町,2012/01/03

となります。

[SQL]SQL文での日付変換あれこれ。

From Evernote:

SQL文での日付変換あれこれ。

SQL文での日付変換あれこれ。

●yyyyMMdd（数値型）→Date型

---------------------------------------

SELECT    CONVERT(datetime, CAST(CA.数値型 AS VARCHAR), 112) AS 日付型
FROM      カレンダーテーブル AS CA 

---------------------------------------

●Date型→yy/MM（文字列）

---------------------------------------

SELECT     RIGHT(CONVERT(VARCHAR, CA.日付型,111),5) AS 文字列型

FROM       カレンダーテーブル AS CA 

---------------------------------------

From Evernote:

[ACCESS2.0]ACCESS2.0でSQLServerを読みに行く

97以降ならADOで読みにいってたんだけど

2.0はDAOじゃないとダメなのかな？

ということでDAOで作り直し。

 'チェックフラグ
 Dim ChkFLG As Integer: ChkFLG = 0
 '*2.0*
 Dim stConnect As String
 stConnect = "ODBC;DSN=＊＊＊＊;UID=＊＊＊＊;PWD=＊＊＊＊;DATABASE=＊＊＊＊;"
 Dim WS As WorkSpace
 Dim DB2 As Database 'データベースオブジェクト変数
 Dim RS2 As Recordset 'レコードセットオブジェクト変数
 Set WS = DBEngine.Workspaces(0)
 Set DB2 = WS.OpenDatabase("", False, False, stConnect)
 Dim mySQL As String
 mySQL = "SELECT * FROM sysobjects WHERE xtype IN ('N' , 'U' ) ORDER BY name"
 Set RS2 = DB2.OpenRecordset(mySQL, db_open_Dynaset)
 Do Until RS2.EOF 'レコードの終わり
      If RemoteTblName = RS2.Fields("Name") Then
           ChkFLG = 1
           Exit Do
      End If
 RS2.MoveNext
 Loop
 'レコードセットオブジェクトを閉じる
 RS2.Close
 Set RS2 = Nothing
 'データベースオブジェクトを閉じる
 DB2.Close
 Set DB2 = Nothing

From Evernote:

[SQLServer]ﾃｰﾌﾞﾙの一覧を習得する。

MEMO：こちら参考。

http://csharp.yaminabe.info/2006/07/sqlserver.html

SELECT B.NAME AS OWNER, A.NAME, A.TYPE

FROM sysobjects A JOIN sysusers B ON A.UID = B.UID

WHERE A.TYPE IN('U', 'IF', 'TF', 'V', 'P') AND A.STATUS >= 0

ORDER BY A.TYPE, A.NAME

From Evernote:

[SQLServer]ﾃｰﾌﾞﾙを消す！

ぽちぽちチェックして削除するのは面倒くさい。

テーブル毎に

'----------------------

use [TestDB]

GO

DROP TABLE  [dbo].[マスターテーブル１] 

DROP TABLE  [dbo].[マスターテーブル２] 

DROP TABLE  [dbo].[マスターテーブル３] 

GO

'----------------------

というスクリプトを用意して一度に実行すればＯＫ

From Evernote:

[SQLServer]他テーブルを利用したＵＰＤＡＴＥ文

更新の際によくつまづくので…。

-------------------------------------------------

update [dbo].[T_日報計上]  AS A
set  A.受注情報No =

     ( SELECT B.test受注情報No FROM  [dbo].[T_日報基本] AS B where  B.日報No = A.日報No)

-------------------------------------------------

がいいんだけど

SQLServerだとうまくいかないので

FROM句を使うこと。

-------------------------------------------------

update [dbo].[T_日報計上]
set 受注情報No = B.test受注情報No
FROM [dbo].[T_日報基本] AS B
where  B.日報No = [dbo].[T_日報計上].日報No

-------------------------------------------------

ちなみにこれだと、Ｂテーブルが存在しない場合、ＡにＮＵＬＬが入ってしまうので

それが嫌な場合は

さらにExistかWHEREで？あるかどうか調べることをお忘れなく。

[SQLServer]特定ユーザに特定権限を与えるスクリプト

ぽちぽちチェックして権限付与するのは面倒くさい。


更新、選択、挿入、削除権限を与えるなら
テーブル毎に

'----------------------

use [TestDB]
GO



GRANT UPDATE,DELETE,SELECT,INSERT ON  [dbo].[マスターテーブル１] TO [userName]


GRANT UPDATE, DELETE ,SELECT,INSERT ON  [dbo].[マスターテーブル２] TO [userName]


GRANT UPDATE, DELETE ,SELECT,INSERT ON  [dbo].[マスターテーブル３] TO [userName]


GO


'----------------------


というスクリプトを用意して一度に実行すればＯＫ