초보코더의 메모장

간단한 쿼리문..
1시간동안 해맸던.

---------------------------
간단한 테스트 테이블
---------------------------
create table Test
(
   child varchar(100),
   parent varchar(100),
   data varchar(100)
)

------------------------------------
테스트 데이터 : child 컬럼의 데이터는 null(최상위) 이거나 부모를 가지고 있다
------------------------------------

insert into Test values('1', null,'data1')
insert into Test values('2', '1','data2')
insert into Test values('3', '1','data3')
insert into Test values('4', '2','data4')
insert into Test values('5', '1','data5')
insert into Test values('6', '1','data6')
insert into Test values('7', '3','data7')
insert into Test values('8', '3','data8')
insert into Test values('9', '1','data9')
------------------------------------
셀프 조인을 통해(left join) 부모데이터와
자식 데이터를 나란히 위치 시킨다
------------------------------------
select t1.child , t1.parent, t1.data childData, t2.data parentData
from Test t1 left outer join
Test t2 on t1.parent = t2.child
------------------------------------
원래 결과
child   parent        data
1           NULL      data1
2           1           data2
3           1           data3
4           2           data4
5           1           data5
6           1           data6
7           3           data7
8           3           data8
9           1           data9

쿼리후 나온 결과
child    parent childData      parentData
1           NULL      data1           NULL
2           1           data2           data1
3           1           data3           data1
4           2           data4           data2
5           1           data5           data1
6           1           data6           data1
7           3           data7           data3
8           3           data8           data3
9           1           data9           data1


끝

귀찮은 디비 백업 스크립트로 간단히 백업


--SQL 에서 폴더 생성 가능하게 셋팅
 --SP_CONFIGURE XP_CMDSHELL ,1
 --RECONFIGURE

--날짜에 맞게 폴더 생성
DECLARE @COMMAND NVARCHAR(100)
declare @folderName NVARCHAR(100)
set @folderName = N'D:\백업\DB백업\'  +  CONVERT(VARCHAR(10),GETDATE(), 121)
SET @COMMAND=N'MD ' + @folderName
 EXEC MASTER..XP_CMDSHELL @COMMAND
GO
--적당히 쓸 변수들
DECLARE @QUERY NVARCHAR(MAX)
DECLARE @PARAMS NVARCHAR(100)
DECLARE @DBNAME2 NVARCHAR(100)
DECLARE @PATH2 NVARCHAR(200)
DECLARE @ROWCOUNT INT;
DECLARE @COUNT INT;
--백업할 디비명들 저장
DECLARE @TABLE TABLE(TABLENAME NVARCHAR(100))

INSERT INTO @TABLE VALUES ('디비명');

....
..
.

SET @COUNT =1
SELECT @ROWCOUNT =  COUNT( TABLENAME) FROM @TABLE

-- 테이블변수에서 디비명 읽어와서 백업
WHILE @COUNT<@ROWCOUNT +1
BEGIN
 SELECT TOP 1  @DBNAME2 = TABLENAME FROM @TABLE
 WHERE TABLENAME IN (
 SELECT TABLENAME
  FROM(
   SELECT TABLENAME, ROW_NUMBER() OVER (ORDER BY TABLENAME) NUM FROM  @TABLE
   ) t1 WHERE NUM = @COUNT
  )
 
 
 
--D:\백업\DB백업\2010-03-19\ACDDYN_2010-03-19.BAK
SELECT @PATH2  = N'D:\백업\DB백업\'  +  CONVERT(VARCHAR(10),GETDATE(), 121) +'\'+ @DBNAME2 +N'_' +CONVERT(VARCHAR(10),GETDATE(), 121)+ N'.BAK'
SET @QUERY = N'BACKUP DATABASE @DBNAME TO DISK=@PATH'
SET @PARAMS = N' @DBNAME NVARCHAR(100),@PATH NVARCHAR(200)'
EXEC sp_executesql @QUERY,@PARAMS, @DBNAME =@DBNAME2, @PATH = @PATH2

SET @COUNT = @COUNT +1;
 
END

끝.

참조 : http://www.phpied.com/dynamic-script-and-style-elements-in-ie/

 function btn1Click1() { //create style tag
            var cssStr = "div {color:blue;}";
            var styleSheet = document.createElement("style");
            styleSheet.setAttribute("id", "id1");
            styleSheet.setAttribute("type", "text/css");
            if (styleSheet.styleSheet) { //IE
                styleSheet.styleSheet.cssText = cssStr;
            }
            else { // W3
                var css = document.createTextNode(cssStr);
                styleSheet.appendChild(css);
            }
            var head = document.getElementsByTagName("head")[0];
            var id1 = document.getElementById("id1");
            if (!id1)
            head.appendChild(styleSheet);
        }

        function btn1Click2() { //remove style tag

            var id1 = document.getElementById("id1");
            var head = document.getElementsByTagName("head")[0];
            if (id1)
            head.removeChild(id1);
        }