Populate a table with a guid

How do I populate a table with existing data with a new guid field? I tried
creating the field, entering NewID() as the default, and isrowguid Yes but
it just gives me errors that the field cannot be null because it already is
null. Do i have to create a blank table with the new id, then dump the data
into it, then rename the tables? I have a lot of relationships taht will
break if I have to do that. Thanks for your help.Stop using Enterprise Manager for this stuff.
CREATE TABLE foo
(
a INT
)
GO
INSERT foo SELECT 1
INSERT foo SELECT 2
ALTER TABLE foo ADD guid UNIQUEIDENTIFIER
GO
ALTER TABLE foo ADD CONSTRAINT guidDefault DEFAULT(NEWID()) FOR guid
GO
UPDATE foo SET guid = NEWID()
GO
SELECT * FROM foo
GO
INSERT foo(a) SELECT 3
GO
SELECT * FROM foo
GO
DROP TABLE foo
GO
"eagle" <eagle@.yahoo.com> wrote in message
news:uR4rkB3pFHA.156@.TK2MSFTNGP11.phx.gbl...
> How do I populate a table with existing data with a new guid field? I
> tried creating the field, entering NewID() as the default, and isrowguid
> Yes but it just gives me errors that the field cannot be null because it
> already is null. Do i have to create a blank table with the new id, then
> dump the data into it, then rename the tables? I have a lot of
> relationships taht will break if I have to do that. Thanks for your help.
>|||Never Mind, updating the table with set newfield=newid() did the trick.
"eagle" <eagle@.yahoo.com> wrote in message
news:uR4rkB3pFHA.156@.TK2MSFTNGP11.phx.gbl...
> How do I populate a table with existing data with a new guid field? I
> tried creating the field, entering NewID() as the default, and isrowguid
> Yes but it just gives me errors that the field cannot be null because it
> already is null. Do i have to create a blank table with the new id, then
> dump the data into it, then rename the tables? I have a lot of
> relationships taht will break if I have to do that. Thanks for your help.
>|||Hi,
You can set the column to allow nulls and then do a simple update statement
to update the column
You don't need to to set isrowguid is you don't need to
--
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"eagle" <eagle@.yahoo.com> wrote in message
news:uR4rkB3pFHA.156@.TK2MSFTNGP11.phx.gbl...
> How do I populate a table with existing data with a new guid field? I
> tried creating the field, entering NewID() as the default, and isrowguid
> Yes but it just gives me errors that the field cannot be null because it
> already is null. Do i have to create a blank table with the new id, then
> dump the data into it, then rename the tables? I have a lot of
> relationships taht will break if I have to do that. Thanks for your help.
>

Point in Time: "Append to Media" Vs "Overwrite an existing media" in SQL Ser

Dear All,

I know the basic defiinition of these two options, but i am not very clear why would someone choose one over another,
currently I am using Append to Media option, and every day backup, I see my backup files growing in size.

can someone give me nice example about these two options,

Thanks,

When you append to the media the previous backups also remain in the backup file. One backup file can contain numerous backups. When you overwrite the backup file is recreated every time so it contains just one file.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Thanks for your reply.

That means, overwrite backup media will occupy less space, then why would i choose Append to media,

any risk involved in overwriting option ?, becuase every body recommends appending, why not ovewritting.

Thanks,|||

It will definitely use less space.

I do not see the risk of overwriting your backup unless you need a full backup that was taken a couple of days earlier to be accessible. In most cases I suppose you will only be interested in the latest version of your database and not the one you had 3 months ago. For the 'special' occasions where a specific version may be required I would simply take a separate backup.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Thanks, your advice is helpful,

but if i want to be able to recover point in time, then I have to go with append to media, along with Transaction long back , RIGHT?

|||

The risk is that if you overwrite the previous backup, and then the current one fails for any reason you are left with no backup for your database. Not a good situation.

What I'd recommend instead, which is a fairly common practice, is to do each backup to a new file which incorporates the current date/time as part of the filename.

That way you always have unique filenames, you can easily prune out old backups, and it's obvious which backup is from what time.

|||

Thanks,

but even if i append to media, and it fails, i will left with no backups , Right?

also for your best suggestion, can you send me sample query or how to do in Enterprise manager.

|||Fahim,
If you are appending to a media daily and if the backup fails today (assumption) then you are atleast left with the backups of previous days since you are appending the previous backups will be present ! but if chose the overwrite option it will overwrite the existing backup file and then will backup the database so if your backup fails due to some reason you are left with no backups for your database.
You can make use of the maintenance plan in sql 2000 to configure backup job and you can specify the number of days to retain the backup file........the backup file will be created with dd/mm/hr format so that it can be deleted by chosing the option "Remove files older than"
http://www.databasejournal.com/features/mssql/article.php/3530486

Friends pls correct me if am wrong

Regards
Deepak
sql