1

So I have a huge script file that will inset loads and loads of data into my database, here is a sample.

INSERT INTO [dbo].[SysCountry] ([country_id], [country_name]) VALUES (NEWID(), 'Argentina');
INSERT INTO [dbo].[SysCountry] ([country_id], [country_name]) VALUES (NEWID(), 'Armenia');
INSERT INTO [dbo].[SysCountry] ([country_id], [country_name]) VALUES (NEWID(), 'Aruba');

No I have 100s if not 1000s of NEWID() tags, however I need to re-use a alot of these ID's. So I want to replace all NEWID() with an actual random GUID. So the output would look like.

INSERT INTO [dbo].[SysCountry] ([country_id], [country_name]) VALUES ('42FF2BE3-D2A7-4FD1-AAA9-6AA0FDBDDD28', 'Argentina');
INSERT INTO [dbo].[SysCountry] ([country_id], [country_name]) VALUES ('59FF040C-5102-41D8-9F8B-782B42983F0E', 'Armenia');
INSERT INTO [dbo].[SysCountry] ([country_id], [country_name]) VALUES ('7589B5F1-21C0-4EF8-897E-B8C7FF8EDFA9', 'Aruba');

I am using Notepad++ to write this, so maybe if you know a Regex I can use in the Find and Replace would be great. Or if you know another way.

Thanks, Ben

Ben
  • 187
  • 3
  • 12
  • In what way to you need to reuse the guids? – Szymon Oct 06 '13 at 11:14
  • I need to use most of them again inside linking tables, but only in the script. – Ben Oct 06 '13 at 11:15
  • but obviously I don't want to go through each and everyone manually putting GUIDs into them – Ben Oct 06 '13 at 11:19
  • I know I will have to copy and paste them at a later date when I am creating the links. But for now I would be good if I could just get them all replaced with actual guids – Ben Oct 06 '13 at 11:22

1 Answers1

3

NEWID() is actually a random GUID.

If you want to reuse it, e.g. when populating a child table, you can grab it again from the parent table, e.g. let's say you populate a country and then need to populate a state table:

INSERT INTO [dbo].[SysCountry] ([country_id], [country_name]) VALUES (NEWID(), 'Australia');

INSERT INTO [dbo].[SysState] ([state_id], [country_id], [state_name])
SELECT 
   NEWID(),
   (SELECT [country_id] FROM [dbo].[SysCountry] WHERE [country_name] = 'Australia'),
   'Victoria'

You can also use a local variable to do the same - the script will look better:

INSERT INTO [dbo].[SysCountry] ([country_id], [country_name]) VALUES (NEWID(), 'Australia');

DECLARE @id uniqueidentifier
SELECT @id = [country_id] FROM [dbo].[SysCountry] WHERE [country_name] = 'Australia'
INSERT INTO [dbo].[SysState] ([state_id], [country_id], [state_name])
    VALUES (NEWID(), @id, 'Victoria')
INSERT INTO [dbo].[SysState] ([state_id], [country_id], [state_name])
    VALUES (NEWID(), @id, 'New South Wales')
Szymon
  • 42,577
  • 16
  • 96
  • 114
  • Ok this is good, but is there a way to just simple replace NEWID() with a GUID? I'll wait for a few more replies, but if I don't get any I will use this. – Ben Oct 06 '13 at 11:25
  • But NEWID() will give you a guid. What's wrong with the one it gives? – Szymon Oct 06 '13 at 11:26
  • There is nothing wrong with that, except I will need to re-use the guid over and over. This answer is good, however still does require more coding. I just want to limit that, but if its all I can do then I will do this. But I would still prefer to just replace NEWID() with an actual ID – Ben Oct 06 '13 at 11:30
  • Ok, I get it. Let's see, maybe someone will have another idea. – Szymon Oct 06 '13 at 11:32
  • I am using something called Roundhouse, I put new scripts all the time which will also be using these IDs, NEWID would change the ID everytime, and I need it to stay the same – Ben Oct 06 '13 at 11:32