[MSSQL/DB] DATABASE 생성

ALTER TABLE dbo.Department ADD DepartmentCode VARCHAR(20) NULL;

데이터 베이스 생성 

 

CREATE DATABASE EmployeeDB;

 

CREATE TABLE dbo.Department(
	DepartmentId INT IDENTITY(1,1),
	DepartmentName VARCHAR(500)
)

SELECT * FROM dbo.Department

 

INSERT INTO dbo.Department values('IT')
INSERT INTO dbo.Department values('Support');

 

CREATE TABLE dbo.Employee(
	EmployeeId INT IDENTITY(1,1),
	EmployeeName VARCHAR(500),
	Department VARCHAR(500),
	DateOfJoining DATE,
	PhotoFileName VARCHAR(500), 
)

 

 

INSERT INTO dbo.Employee values ('Sam', 'IT', '2022-03-04', 'anonmymout.png')

 

행추가

 

ALTER TABLE dbo.Department ADD DepartmentCode VARCHAR(20) NULL;

 

 

 

기존에 있는 Table 데이터 쿼리문 가져오기

- DB(우클릭) > 태스트 > 스크립트 생성 

 

 

 

 

 

 

 

USE [EmployeeDB]
GO
/****** Object:  Table [dbo].[Employee]    Script Date: 2022-05-09 오후 8:38:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee3](
	[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeName] [varchar](500) NULL,
	[Department] [varchar](500) NULL,
	[DateOfJoining] [date] NULL,
	[PhotoFileName] [varchar](500) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee] ON 
GO
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [Department], [DateOfJoining], [PhotoFileName]) VALUES (1, N'Sam', N'IT', CAST(N'2022-03-04' AS Date), N'anonmymout.png')
GO
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [Department], [DateOfJoining], [PhotoFileName]) VALUES (2, N'Pam', N'Nature', CAST(N'2021-09-26' AS Date), N'pam.png')
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

 

--SELECT  * FROM [dbo].[Employee01]
--UNION
--SELECT  * FROM [dbo].[Employee02]



--SELECT  * FROM [dbo].[Employee01]
--INTERSECT
--SELECT  * FROM [dbo].[Employee02]



--SELECT  * FROM [dbo].[Employee01]
--EXCEPT
--SELECT  * FROM [dbo].[Employee02]


INSERT INTO dbo.Employee03 ([EmployeeId], [EmployeeName], [Department], [DateOfJoining], [PhotoFileName]) 
SELECT  * FROM [dbo].[Employee01]
EXCEPT
SELECT  * FROM [dbo].[Employee02]