mirror of
https://github.com/datahub-project/datahub.git
synced 2025-07-05 16:22:17 +00:00
162 lines
4.1 KiB
Transact-SQL
162 lines
4.1 KiB
Transact-SQL
DROP DATABASE IF EXISTS NewData;
|
|
CREATE DATABASE NewData;
|
|
GO
|
|
USE NewData;
|
|
GO
|
|
CREATE TABLE ProductsNew (ID int, ProductName nvarchar(max), Price money);
|
|
GO
|
|
CREATE SCHEMA FooNew;
|
|
GO
|
|
CREATE TABLE FooNew.ItemsNew (ID int, ItemName nvarchar(max), Price smallmoney);
|
|
GO
|
|
CREATE TABLE FooNew.PersonsNew (
|
|
ID int NOT NULL PRIMARY KEY,
|
|
LastName varchar(255) NOT NULL,
|
|
FirstName varchar(255),
|
|
Age int
|
|
);
|
|
GO
|
|
CREATE VIEW FooNew.View1 AS
|
|
SELECT LastName, FirstName
|
|
FROM FooNew.PersonsNew
|
|
WHERE Age > 18
|
|
GO
|
|
|
|
DROP DATABASE IF EXISTS DemoData;
|
|
CREATE DATABASE DemoData;
|
|
GO
|
|
USE DemoData;
|
|
GO
|
|
CREATE TABLE Products (ID int, ProductName nvarchar(max));
|
|
GO
|
|
CREATE SCHEMA Foo;
|
|
GO
|
|
CREATE TABLE Foo.Items (ID int, ItemName nvarchar(max));
|
|
GO
|
|
-- Create a table, ephemeral will be used as temp table pattern in some test case
|
|
CREATE TABLE Foo.EphemeralItems (ID int, ItemName nvarchar(max));
|
|
GO
|
|
CREATE TABLE Foo.FinalItems (ID int, ItemName nvarchar(max));
|
|
GO
|
|
CREATE TABLE Foo.Persons (
|
|
ID int NOT NULL PRIMARY KEY,
|
|
LastName varchar(255) NOT NULL,
|
|
FirstName varchar(255),
|
|
Age int
|
|
);
|
|
GO
|
|
CREATE VIEW Foo.PersonsView AS SELECT * FROM Foo.Persons;
|
|
GO
|
|
CREATE TABLE Foo.SalesReason
|
|
(
|
|
TempID int NOT NULL,
|
|
SomeId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
|
|
Name nvarchar(50)
|
|
, CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID)
|
|
, CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
|
|
REFERENCES Foo.Persons (ID)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
)
|
|
;
|
|
GO
|
|
DROP PROCEDURE IF EXISTS [Foo].[Proc.With.SpecialChar];
|
|
GO
|
|
CREATE PROCEDURE [Foo].[Proc.With.SpecialChar] @ID INT
|
|
AS
|
|
SELECT @ID AS ThatDB;
|
|
GO
|
|
|
|
DROP PROCEDURE IF EXISTS [Foo].[NewProc];
|
|
GO
|
|
CREATE PROCEDURE [Foo].[NewProc]
|
|
AS
|
|
BEGIN
|
|
--insert into items table from salesreason table
|
|
insert into Foo.Items (ID, ItemName)
|
|
SELECT TempID, Name
|
|
FROM Foo.SalesReason;
|
|
|
|
-- lineage: Foo.Items --> Foo.EphemeralItems
|
|
insert into Foo.EphemeralItems (ID, ItemName)
|
|
select ID, ItemName
|
|
from Foo.Items;
|
|
|
|
-- lineage: Foo.EphemeralItems --> Foo.FinalItems
|
|
UPDATE DemoData.Foo.FinalItems
|
|
SET ItemName = e.ItemName
|
|
FROM DemoData.Foo.FinalItems f
|
|
JOIN DemoData.Foo.EphemeralItems e ON f.ID = e.ID;
|
|
|
|
IF OBJECT_ID('Foo.age_dist', 'U') IS NULL
|
|
BEGIN
|
|
-- Create and populate if table doesn't exist
|
|
SELECT Age, COUNT(*) as Count
|
|
INTO Foo.age_dist
|
|
FROM Foo.Persons
|
|
GROUP BY Age
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
-- Update existing table
|
|
TRUNCATE TABLE Foo.age_dist;
|
|
|
|
INSERT INTO Foo.age_dist (Age, Count)
|
|
SELECT Age, COUNT(*) as Count
|
|
FROM Foo.Persons
|
|
GROUP BY Age
|
|
END
|
|
|
|
SELECT ID, Age INTO #TEMPTABLE FROM NewData.FooNew.PersonsNew
|
|
|
|
UPDATE DemoData.Foo.Persons
|
|
SET Age = t.Age
|
|
FROM DemoData.Foo.Persons p
|
|
JOIN #TEMPTABLE t ON p.ID = t.ID
|
|
|
|
END
|
|
GO
|
|
|
|
EXEC Foo.NewProc
|
|
GO
|
|
EXEC sys.sp_addextendedproperty
|
|
@name = N'MS_Description',
|
|
@value = N'Description for table Items of schema Foo.',
|
|
@level0type = N'SCHEMA', @level0name = 'Foo',
|
|
@level1type = N'TABLE', @level1name = 'Items';
|
|
GO
|
|
|
|
GO
|
|
EXEC sys.sp_addextendedproperty
|
|
@name = N'MS_Description',
|
|
@value = N'Description for column LastName of table Persons of schema Foo.',
|
|
@level0type = N'SCHEMA', @level0name = 'Foo',
|
|
@level1type = N'TABLE', @level1name = 'Persons',
|
|
@level2type = N'COLUMN',@level2name = 'LastName';
|
|
GO
|
|
USE msdb ;
|
|
GO
|
|
EXEC dbo.sp_add_job
|
|
@job_name = N'Weekly Demo Data Backup' ;
|
|
GO
|
|
EXEC sp_add_jobstep
|
|
@job_name = N'Weekly Demo Data Backup',
|
|
@step_name = N'Set database to read only',
|
|
@database_name = N'DemoData',
|
|
@subsystem = N'TSQL',
|
|
@command = N'ALTER DATABASE DemoData SET READ_ONLY',
|
|
@retry_attempts = 5,
|
|
@retry_interval = 5 ;
|
|
GO
|
|
EXEC dbo.sp_add_schedule
|
|
@schedule_name = N'RunOnce',
|
|
@freq_type = 1,
|
|
@active_start_time = 233000 ;
|
|
GO
|
|
EXEC sp_attach_schedule
|
|
@job_name = N'Weekly Demo Data Backup',
|
|
@schedule_name = N'RunOnce';
|
|
GO
|
|
EXEC dbo.sp_add_jobserver
|
|
@job_name = N'Weekly Demo Data Backup'
|
|
GO |