Skip to content

Instantly share code, notes, and snippets.

@janvanderhaegen

janvanderhaegen/Script.SampleCustomers.sql

Created September 24, 2013 09:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save janvanderhaegen/6682208 to your computer and use it in GitHub Desktop.
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