· 8 years ago · Apr 26, 2018, 06:54 PM
1INSERT INTO UserAddresses (UserId, AddressId)
2 SELECT UserId, Id
3 FROM Addresses
4
5SELECT * FROM Addresses
6WHERE Id NOT IN (SELECT Id FROM Addresses)
7
8CREATE TABLE [Addresses] (
9 [Id] [uniqueidentifier] NOT NULL ,
10 PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY] ,
11) ON [PRIMARY]
12CREATE TABLE [Users] (
13 [Id] [uniqueidentifier] NOT NULL ,
14 PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
15) ON [PRIMARY]
16CREATE TABLE [UserAddresses] (
17 [UserId] [uniqueidentifier] NOT NULL ,
18 [AddressId] [uniqueidentifier] NOT NULL ,
19 CONSTRAINT [FK569ABB5045EE0940] FOREIGN KEY
20 (
21 [AddressId]
22 ) REFERENCES [Addresses] (
23 [Id]
24 ),
25 CONSTRAINT [UserAddressesToAddressFK] FOREIGN KEY
26 (
27 [UserId]
28 ) REFERENCES [Users] (
29 [Id]
30 )
31) ON [PRIMARY]
32ALTER TABLE Addresses ADD UserId UNIQUEIDENTIFIER
33INSERT INTO Addresses (UserId, Id)
34SELECT legacy_userid, legacy_single_useraddressid -- both are guids
35FROM LegacyUsers INNER JOIN LegacyAddresses
36
37DECLARE c CURSOR FOR SELECT UserId, Id FROM Addresses
38OPEN c
39DECLARE @uid UNIQUEIDENTIFIER, @aid UNIQUEIDENTIFIER
40FETCH NEXT FROM c INTO @uid, @aid
41WHILE @@FETCH_STATUS = 0
42BEGIN
43 PRINT @aid
44 INSERT INTO UserAddresses (UserId, AddressId)
45 VALUES (@uid, @aid)
46FETCH NEXT FROM c INTO @uid, @aid
47END
48CLOSE c
49DEALLOCATE c
50
51ALTER TABLE UserAddresses DROP CONSTRAINT FK569ABB5045EE0940
52
53INSERT INTO UserAddresses (UserId, AddressId)
54SELECT UserId, Id
55FROM Addresses
56
57 alter table UserAddresses
58 add constraint FK569ABB5045EE0940
59 foreign key (AddressId)
60 references Addresses
61
62SET NOCOUNT ON
63GO
64--<< ========================== DROPS ==========================
65IF OBJECT_ID('tempdb..#UserGUIDs') IS NOT NULL
66 DROP TABLE #UserGUIDs
67GO
68IF OBJECT_ID('tempdb..#AddressGUIDs') IS NOT NULL
69 DROP TABLE #AddressGUIDs
70GO
71IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('UserAddresses'))
72 DROP TABLE [UserAddresses]
73GO
74IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('Users'))
75 DROP TABLE [Users]
76GO
77IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('dbo.Addresses'))
78 DROP TABLE dbo.[Addresses]
79GO
80IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('harry.Addresses'))
81 DROP TABLE harry.[Addresses]
82GO
83
84--<< ========================== TABLES ==========================
85--<< Users
86CREATE TABLE [Users] (
87 [Id] uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY KEY,
88 [UserName] varchar(10) NOT NULL
89) ON [PRIMARY]
90GO
91
92--<< Addresses
93CREATE TABLE harry.[Addresses] (
94 [Id] uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY KEY,
95 [Address1] varchar(20) NOT NULL
96) ON [PRIMARY]
97GO
98CREATE TABLE dbo.[Addresses] (
99 [Id] uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY KEY,
100 [Address1] varchar(20) NOT NULL
101) ON [PRIMARY]
102GO
103
104--<< UserAddresses
105CREATE TABLE [UserAddresses] (
106 [UserId] uniqueidentifier NOT NULL,
107 [AddressId] uniqueidentifier NOT NULL,
108 CONSTRAINT [FK569ABB5045EE0940] FOREIGN KEY ([AddressId]) REFERENCES [Addresses] ([Id]),
109 CONSTRAINT [UserAddressesToAddressFK] FOREIGN KEY ([UserId]) REFERENCES [Users] ([Id])
110) ON [PRIMARY]
111GO
112
113--<< ========================== DATA ==========================
114--<< Populate Users
115CREATE TABLE #UserGUIDs ([UserId] uniqueidentifier)
116GO
117INSERT INTO [Users] ([UserName]) VALUES ('UserName1')
118INSERT INTO [Users] ([UserName]) VALUES ('UserName2')
119INSERT INTO [Users] ([UserName]) VALUES ('UserName3')
120INSERT INTO [Users] ([UserName]) VALUES ('UserName4')
121GO
122INSERT INTO #UserGUIDs ([UserId]) SELECT [Id] FROM [Users]
123GO
124
125--<< Populate Addresses
126CREATE TABLE #AddressGUIDs ([AddressId] uniqueidentifier)
127GO
128INSERT INTO harry.[Addresses] ([Address1]) VALUES ('1234 First Street')
129INSERT INTO harry.[Addresses] ([Address1]) VALUES ('2345 Second Street')
130INSERT INTO harry.[Addresses] ([Address1]) VALUES ('3456 Third Street')
131INSERT INTO harry.[Addresses] ([Address1]) VALUES ('4567 Fourth Street')
132GO
133INSERT INTO #AddressGUIDs ([AddressId]) SELECT [Id] FROM harry.[Addresses]
134GO
135
136PRINT 'Users'
137SELECT * FROM [Users]
138PRINT 'Addresses'
139SELECT * FROM harry.[Addresses]
140GO
141
142--<< ========================== TEST ==========================
143--<< Populate UserAddresses
144INSERT INTO UserAddresses (UserId, AddressId)
145SELECT
146 u.Id, -- UserID
147 a.Id -- AddressID
148FROM harry.Addresses AS a
149CROSS JOIN Users AS u
150GO
151
152PRINT 'UserAddresses'
153SELECT * FROM [UserAddresses]
154GO
155
156Msg 547, Level 16, State 0, Line 4
157The INSERT statement conflicted with the FOREIGN KEY constraint "FK569ABB5045EE0940". The conflict occurred in database "RGTest1", table "dbo.Addresses", column 'Id'.
158
159SET NOCOUNT ON
160GO
161--<< ========================== DROPS ==========================
162IF OBJECT_ID('tempdb..#UserGUIDs') IS NOT NULL
163 DROP TABLE #UserGUIDs
164GO
165IF OBJECT_ID('tempdb..#AddressGUIDs') IS NOT NULL
166 DROP TABLE #AddressGUIDs
167GO
168IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('UserAddresses'))
169 DROP TABLE [UserAddresses]
170GO
171IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('Users'))
172 DROP TABLE [Users]
173GO
174IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('Addresses'))
175 DROP TABLE [Addresses]
176GO
177
178--<< ========================== TABLES ==========================
179--<< Users
180CREATE TABLE [Users] (
181 [Id] uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY KEY,
182 [UserName] varchar(10) NOT NULL
183) ON [PRIMARY]
184GO
185
186--<< Addresses
187CREATE TABLE [Addresses] (
188 [Id] uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY KEY,
189 [Address1] varchar(20) NOT NULL
190) ON [PRIMARY]
191GO
192
193--<< UserAddresses
194CREATE TABLE [UserAddresses] (
195 [UserId] uniqueidentifier NOT NULL,
196 [AddressId] uniqueidentifier NOT NULL,
197 CONSTRAINT [FK569ABB5045EE0940] FOREIGN KEY ([AddressId]) REFERENCES [Addresses] ([Id]),
198 CONSTRAINT [UserAddressesToAddressFK] FOREIGN KEY ([UserId]) REFERENCES [Users] ([Id])
199) ON [PRIMARY]
200GO
201
202--<< ========================== DATA ==========================
203--<< Populate Users
204CREATE TABLE #UserGUIDs ([UserId] uniqueidentifier)
205GO
206INSERT INTO [Users] ([UserName]) VALUES ('UserName1')
207INSERT INTO [Users] ([UserName]) VALUES ('UserName2')
208INSERT INTO [Users] ([UserName]) VALUES ('UserName3')
209INSERT INTO [Users] ([UserName]) VALUES ('UserName4')
210GO
211INSERT INTO #UserGUIDs ([UserId]) SELECT [Id] FROM [Users]
212GO
213
214--<< Populate Addresses
215CREATE TABLE #AddressGUIDs ([AddressId] uniqueidentifier)
216GO
217INSERT INTO [Addresses] ([Address1]) VALUES ('1234 First Street')
218INSERT INTO [Addresses] ([Address1]) VALUES ('2345 Second Street')
219INSERT INTO [Addresses] ([Address1]) VALUES ('3456 Third Street')
220INSERT INTO [Addresses] ([Address1]) VALUES ('4567 Fourth Street')
221GO
222INSERT INTO #AddressGUIDs ([AddressId]) SELECT [Id] FROM [Addresses]
223GO
224
225PRINT 'Users'
226SELECT * FROM [Users]
227PRINT 'Addresses'
228SELECT * FROM [Addresses]
229GO
230
231--<< ========================== TEST ==========================
232--<< Populate UserAddresses
233INSERT INTO UserAddresses (UserId, AddressId)
234SELECT
235 u.Id, -- UserID
236 a.Id -- AddressID
237FROM Addresses AS a
238CROSS JOIN Users AS u
239GO
240
241PRINT 'UserAddresses'
242SELECT * FROM [UserAddresses]
243GO
244
245INSERT INTO UserAddresses (UserId, AddressId)
246 SELECT UserId, Id
247 FROM Addresses
248
249-- is actually
250INSERT INTO harry.UserAddresses (UserId, AddressId)
251 SELECT UserId, Id
252 FROM bob.Addresses
253-- or
254INSERT INTO bob.UserAddresses (UserId, AddressId)
255 SELECT UserId, Id
256 FROM harry.Addresses