Skip to content

Instantly share code, notes, and snippets.

@janvanderhaegen

janvanderhaegen/Script.SampleCustomers.sql

Created September 24, 2013 09:05
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save janvanderhaegen/6682208 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save janvanderhaegen/6682208 to your computer and use it in GitHub Desktop.
Download ZIP
Inserts a couple of sample customers and orders
Raw
Script.SampleCustomers.sql
SET IDENTITY_INSERT [dbo].[Customers] ON;
MERGE INTO [dbo].[Customers] AS Target
USING (VALUES
(1, 'Beth Massi', 'F', 'Burg 1', '8000', 'Brugge', 'Belgium', 1.00),
(2, 'Chris Rummel', 'M', 'Burg 1', '8000', 'Brugge', 'Belgium', 0.90 ),
(3, 'Matt Evans', 'M', 'Burg 1', '8000', 'Brugge', 'Belgium', 0.80 ),
(4, 'Andy Kung', 'M', 'Burg 1', '8000', 'Brugge', 'Belgium', 0.70 ),
(5, 'Brian Moore', 'M', 'Burg 1', '8000', 'Brugge', 'Belgium', 0.60 ),
(6, 'Matt Sampson', 'F', 'Burg 1', '8000', 'Brugge', 'Belgium', 0.50 ),
(7, 'Steve Lasker', 'M', 'Burg 1', '8000', 'Brugge', 'Belgium', 0.40 ),
(8, 'Heinrich Wendel', 'M', 'Burg 1', '8000', 'Brugge', 'Belgium', 0.30 ),
(9, 'General Awesome', 'F', 'Burg 1', '8000', 'Brugge', 'Belgium', 0.00)
)
AS Source(Id, Name, Gender, Street, ZipCode, City, Country, SatisfactionScore)
ON Target.Id = Source.Id
-- update matched rows
WHEN MATCHED THEN
UPDATE SET Name = Source.Name, Gender = Source.Gender, Street = Source.Street,
ZipCode = Source.ZipCode, City = Source.City, Country = Source.Country,
SatisfactionScore = Source.SatisfactionScore, DateOfBirth = NULL,
FullProfile = NULL, Email = NULL, Phone = NULL, AverageYearlySpending = NULL
-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Name, Gender, Street, ZipCode, City, Country, SatisfactionScore)
VALUES (Id, Name, Gender, Street, ZipCode, City, Country, SatisfactionScore)
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
SET IDENTITY_INSERT [dbo].[Customers] OFF;
GO
SET IDENTITY_INSERT [dbo].[Orders] ON;
MERGE INTO [dbo].[Orders] AS Target
USING (VALUES
(1, '20121103', 1, 238, 2),
(2, '20130623', 1, 948, 3),
(3, '20110917', 1, 80, 4),
(4, '20120401', 1, 1508, 5),
(5, '20110615', 1, 1741, 6),
(6, '20110811', 1, 1730, 7),
(7, '20120407', 1, 657, 8),
(8, '20120304', 1, 1526, 9),
(9, '20110417', 1, 327, 1),
(10, '20101125', 1, 809, 2),
(11, '20110525', 1, 404, 3),
(12, '20091130', 1, 1721, 4),
(13, '20100909', 1, 1838, 5),
(14, '20120724', 1, 1241, 6),
(15, '20120721', 1, 1742, 7),
(16, '20101217', 1, 779, 8),
(17, '20120327', 1, 140, 9),
(18, '20120110', 1, 305, 1),
(19, '20100930', 1, 541, 2),
(20, '20120417', 1, 1444, 3),
(21, '20120409', 1, 1857, 4),
(22, '20110130', 1, 971, 5),
(23, '20110324', 1, 1103, 6),
(24, '20130728', 1, 1446, 7),
(25, '20090928', 1, 1563, 8),
(26, '20101008', 1, 170, 9),
(27, '20091221', 1, 1681, 1),
(28, '20100627', 1, 1179, 2),
(29, '20120303', 1, 1832, 3),
(30, '20130527', 1, 1048, 4),
(31, '20110124', 1, 21, 5),
(32, '20121005', 1, 1036, 6),
(33, '20101103', 1, 852, 7),
(34, '20111213', 0, 1281, 8),
(35, '20100317', 1, 1013, 9),
(36, '20120430', 1, 230, 1),
(37, '20120324', 1, 1416, 2),
(38, '20121023', 1, 1044, 3),
(39, '20100108', 1, 906, 4),
(40, '20120225', 1, 1215, 5),
(41, '20110515', 1, 803, 6),
(42, '20130630', 1, 732, 7),
(43, '20100714', 0, 271, 8),
(44, '20120622', 1, 855, 9),
(45, '20101225', 1, 1621, 1),
(46, '20121001', 1, 1, 2),
(47, '20130403', 0, 1346, 3),
(48, '20100225', 1, 374, 4),
(49, '20100421', 1, 1815, 5),
(50, '20110920', 1, 691, 6),
(51, '20130502', 1, 715, 7),
(52, '20100417', 1, 1017, 8),
(53, '20120731', 1, 1778, 9),
(54, '20130102', 1, 1346, 1),
(55, '20090805', 0, 1878, 2),
(56, '20120527', 1, 1813, 3),
(57, '20101003', 1, 1203, 4),
(58, '20110828', 1, 429, 5),
(59, '20091016', 1, 1549, 6),
(60, '20120908', 1, 1830, 7),
(61, '20111106', 1, 766, 8),
(62, '20100602', 1, 1325, 9),
(63, '20130424', 1, 1182, 1),
(64, '20120423', 1, 248, 2),
(65, '20130509', 1, 904, 3),
(66, '20130812', 1, 1242, 4),
(67, '20121004', 1, 1655, 5),
(68, '20130811', 1, 1268, 6),
(69, '20100813', 1, 95, 7),
(70, '20121230', 1, 293, 8),
(71, '20100828', 1, 1099, 9),
(72, '20090814', 1, 521, 1),
(73, '20120512', 1, 1616, 2),
(74, '20120602', 1, 1373, 3),
(75, '20120401', 1, 1342, 4),
(76, '20110720', 1, 626, 5),
(77, '20100714', 1, 1596, 6),
(78, '20121213', 1, 1350, 7),
(79, '20130327', 1, 993, 8),
(80, '20100805', 0, 1174, 9),
(81, '20101215', 1, 1811, 1),
(82, '20110911', 1, 1770, 2),
(83, '20110508', 1, 420, 3),
(84, '20121014', 1, 1260, 4),
(85, '20100426', 1, 550, 5),
(86, '20130102', 1, 735, 6),
(87, '20090901', 1, 338, 7),
(88, '20100828', 1, 1799, 8),
(89, '20120428', 1, 17, 9),
(90, '20100119', 1, 548, 1),
(91, '20100713', 1, 289, 2),
(92, '20120828', 1, 566, 3),
(93, '20100615', 1, 1484, 4),
(94, '20100608', 1, 611, 5),
(95, '20130303', 1, 449, 6),
(96, '20121120', 1, 189, 7),
(97, '20111120', 1, 1444, 8),
(98, '20130120', 1, 1558, 9),
(99, '20111023', 1, 993, 1),
(100, '20130803', 1, 72, 2),
(101, '20091029', 1, 399, 3),
(102, '20091014', 1, 238, 4),
(103, '20110707', 1, 520, 5),
(104, '20090717', 1, 1528, 6),
(105, '20110216', 1, 525, 7),
(106, '20100205', 1, 1619, 8),
(107, '20100912', 1, 381, 9),
(108, '20091117', 1, 1779, 1),
(109, '20110612', 1, 1509, 2),
(110, '20120402', 1, 1360, 3),
(111, '20100606', 1, 1350, 4),
(112, '20100725', 1, 506, 5),
(113, '20130226', 1, 185, 6),
(114, '20130227', 1, 1496, 7),
(115, '20100323', 1, 1074, 8),
(116, '20090909', 1, 1117, 9),
(117, '20110520', 1, 599, 1),
(118, '20101111', 1, 30, 2),
(119, '20100305', 1, 451, 3),
(120, '20120627', 1, 1587, 4),
(121, '20120404', 1, 1477, 5),
(122, '20110429', 1, 1169, 6),
(123, '20130325', 1, 636, 7),
(124, '20130729', 0, 828, 8),
(125, '20100708', 1, 896, 9),
(126, '20111202', 1, 32, 1),
(127, '20120203', 1, 161, 2),
(128, '20121006', 1, 396, 3),
(129, '20111220', 1, 887, 4),
(130, '20101006', 1, 1594, 5),
(131, '20130629', 1, 1466, 6),
(132, '20120715', 1, 922, 7),
(133, '20130728', 1, 405, 8),
(134, '20090917', 1, 1267, 9),
(135, '20120729', 1, 1594, 1),
(136, '20100101', 1, 1323, 2),
(137, '20110519', 1, 1763, 3),
(138, '20120916', 1, 1579, 4),
(139, '20100714', 1, 744, 5),
(140, '20110412', 1, 210, 6),
(141, '20130101', 1, 1819, 7),
(142, '20110730', 1, 1723, 8),
(143, '20101120', 1, 1556, 9),
(144, '20090725', 1, 1551, 1),
(145, '20100425', 1, 1349, 2),
(146, '20111125', 1, 482, 3),
(147, '20120205', 1, 359, 4),
(148, '20100614', 1, 913, 5),
(149, '20091116', 1, 1774, 6),
(150, '20091002', 1, 1247, 7),
(151, '20130325', 1, 210, 8),
(152, '20130311', 1, 880, 9),
(153, '20100913', 0, 1051, 1),
(154, '20091014', 1, 804, 2),
(155, '20100104', 1, 41, 3),
(156, '20130226', 1, 917, 4),
(157, '20130212', 1, 322, 5),
(158, '20130118', 1, 1207, 6),
(159, '20091030', 1, 473, 7),
(160, '20110828', 1, 325, 8),
(161, '20090814', 0, 1863, 9),
(162, '20121212', 1, 1826, 1),
(163, '20100919', 1, 1176, 2),
(164, '20120807', 1, 374, 3),
(165, '20100926', 1, 582, 4),
(166, '20100621', 1, 1124, 5),
(167, '20110924', 1, 745, 6),
(168, '20111006', 1, 340, 7),
(169, '20110813', 1, 460, 8),
(170, '20110830', 1, 803, 9),
(171, '20120925', 1, 325, 1),
(172, '20120118', 1, 932, 2),
(173, '20111019', 1, 1265, 3),
(174, '20130514', 1, 1360, 4),
(175, '20100125', 1, 1185, 5),
(176, '20090815', 1, 1447, 6),
(177, '20120406', 1, 922, 7),
(178, '20101201', 1, 1494, 8),
(179, '20100601', 1, 1720, 9),
(180, '20100428', 1, 1778, 1),
(181, '20100830', 1, 987, 2),
(182, '20121022', 1, 904, 3),
(183, '20130704', 1, 908, 4),
(184, '20100407', 1, 338, 5),
(185, '20091024', 1, 746, 6),
(186, '20101102', 1, 1794, 7),
(187, '20110503', 1, 987, 8),
(188, '20110823', 1, 47, 9),
(189, '20091006', 1, 406, 1),
(190, '20120114', 1, 830, 2),
(191, '20110318', 1, 74, 3),
(192, '20100813', 1, 334, 4),
(193, '20121022', 1, 1598, 5),
(194, '20130103', 1, 223, 6),
(195, '20120614', 1, 1582, 7),
(196, '20120508', 0, 1177, 8),
(197, '20111230', 1, 1774, 9),
(198, '20090901', 1, 306, 1),
(199, '20111127', 1, 158, 2),
(200, '20110802', 1, 903, 3),
(201, '20120715', 1, 1361, 4),
(202, '20130612', 1, 1296, 5),
(203, '20130116', 1, 108, 6),
(204, '20120922', 1, 1749, 7),
(205, '20130311', 1, 1078, 8),
(206, '20111219', 1, 1203, 9),
(207, '20121028', 1, 1568, 1),
(208, '20110305', 1, 949, 2),
(209, '20110719', 1, 749, 3),
(210, '20100104', 1, 195, 4),
(211, '20130327', 1, 775, 5),
(212, '20120211', 1, 579, 6),
(213, '20111220', 1, 536, 7),
(214, '20121229', 1, 1018, 8),
(215, '20110421', 1, 1183, 9),
(216, '20130724', 1, 1594, 1),
(217, '20110306', 1, 78, 2),
(218, '20111007', 1, 1194, 3),
(219, '20101211', 1, 1237, 4),
(220, '20120922', 1, 1151, 5),
(221, '20120607', 1, 1674, 6),
(222, '20090911', 1, 1479, 7),
(223, '20110705', 0, 991, 8),
(224, '20120714', 1, 1814, 9),
(225, '20120304', 0, 876, 1),
(226, '20120605', 1, 1545, 2),
(227, '20100105', 1, 1457, 3),
(228, '20110503', 1, 568, 4),
(229, '20130513', 1, 758, 5),
(230, '20100531', 1, 413, 6),
(231, '20101206', 1, 1338, 7),
(232, '20101101', 1, 1724, 8),
(233, '20101204', 1, 100, 9),
(234, '20100223', 1, 228, 1),
(235, '20120423', 1, 26, 2),
(236, '20130405', 1, 495, 3),
(237, '20121204', 1, 1209, 4),
(238, '20100305', 1, 90, 5),
(239, '20100303', 0, 1856, 6),
(240, '20110323', 1, 916, 7),
(241, '20120122', 0, 41, 8),
(242, '20120514', 1, 1550, 9),
(243, '20120801', 1, 339, 1),
(244, '20110505', 1, 802, 2),
(245, '20100629', 1, 1881, 3),
(246, '20101216', 1, 1810, 4),
(247, '20120610', 1, 1031, 5),
(248, '20130117', 1, 673, 6),
(249, '20110325', 1, 1606, 7),
(250, '20101201', 1, 246, 8),
(251, '20091017', 1, 1360, 9),
(252, '20110409', 1, 1457, 1),
(253, '20130318', 1, 1371, 2),
(254, '20121020', 1, 807, 3),
(255, '20130411', 1, 1381, 4),
(256, '20120925', 1, 581, 5),
(257, '20130518', 1, 942, 6),
(258, '20130707', 1, 1744, 7),
(259, '20091003', 1, 230, 8),
(260, '20101115', 1, 1507, 9),
(261, '20090818', 1, 1588, 1),
(262, '20130531', 1, 1067, 2),
(263, '20100918', 1, 1079, 3),
(264, '20120912', 1, 423, 4),
(265, '20120830', 1, 264, 5),
(266, '20130217', 0, 1895, 6),
(267, '20110114', 1, 1402, 7),
(268, '20110527', 1, 1227, 8),
(269, '20110105', 1, 1474, 9),
(270, '20100503', 1, 426, 1),
(271, '20101218', 1, 553, 2),
(272, '20091020', 1, 459, 3),
(273, '20110313', 1, 1883, 4),
(274, '20121208', 1, 1687, 5),
(275, '20100801', 1, 1222, 6),
(276, '20120629', 1, 396, 7),
(277, '20100601', 1, 349, 8),
(278, '20130208', 0, 676, 9),
(279, '20130522', 1, 1888, 1),
(280, '20121101', 1, 1361, 2),
(281, '20100522', 1, 1060, 3),
(282, '20120315', 1, 223, 4),
(283, '20091015', 1, 1634, 5),
(284, '20091206', 1, 45, 6),
(285, '20130802', 1, 1631, 7),
(286, '20100215', 1, 1454, 8),
(287, '20130808', 1, 216, 9),
(288, '20110414', 1, 1537, 1),
(289, '20130613', 1, 741, 2),
(290, '20091009', 1, 1512, 3),
(291, '20120806', 1, 1172, 4),
(292, '20120627', 1, 1139, 5),
(293, '20120404', 1, 487, 6),
(294, '20110616', 1, 930, 7),
(295, '20120308', 1, 557, 8),
(296, '20121215', 1, 1098, 9),
(297, '20110729', 1, 1196, 1),
(298, '20120903', 1, 105, 2),
(299, '20110220', 1, 1689, 3)
)
AS Source(Id, CreationDate, Completed, OrderTotal, Order_Customer)
ON Target.Id = Source.Id
-- update matched rows
WHEN MATCHED THEN
UPDATE SET CreationDate = Source.CreationDate, Completed = Source.Completed, OrderTotal = Source.OrderTotal,
Order_Customer = Source.Order_Customer
-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, CreationDate, Completed, OrderTotal, Order_Customer)
VALUES (Id, CreationDate, Completed, OrderTotal, Order_Customer)
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
SET IDENTITY_INSERT [dbo].[Orders] OFF;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment