[sql server] Genereren van unieke id's bij bestaande data

[Verwijderd]

In een MS SQL Server tabel die gevuld is met data moet een primary key (dus uniek) worden toegevoegd met als datatype varchar. Het toevoegen van een extra kolom aan de tabel gaat prima. Het probleem is echter het vullen van deze kolom met een uniek id (hoeft niet globaal uniek te zijn).

Ik wil de oplossing graag uitvoeren in een SQL-script, dus niet met behulp van een applicatie of vbs(of zo) script.

Nu heb ik twee oplossingen bedacht, maar bij beiden loop ik vast
Methode 1: Voeg de kolom toe aan de tabel en vul voor alle records dat veld met een unieke waarde (bijvoorbeeld een opvolgend nummer).
Probleem: Hoe moet je in een sql-script per record een andere waarde invoeren?

Methode 2: Maak een nieuwe tabel met dezelfde structuur, voeg daaraan een kolom toe (type int) met 'IDENTITY' (en primary) gezet. Kopieer vervolgens de data uit de originele tabel naar de tweede tabel en het id-veld wordt dankzij 'identity' automatisch gemaakt.
Probleem: Hoe verwijder ik hierna het automatisch genereren van waarden door 'identity' en hoe verander ik het datatype van int naar varchar?
Als ik deze handelingen doe via de Enterprise Manager, gaat het goed, maar aangezien het voor 150+ tabellen moet gebeuren, doet ik dat liever met een script.
Het uitzetten van identity kan via

code:

SET IDENTITY_INSERT <tabelnaam> OFF

Het veranderen van het datatype levert de volgende foutmelding op:

code:

ALTER TABLE <tabelnaam> ALTER COLUMN <id-field> [varchar] (30)
The object '<primary key identifier>' is dependent on column '<id-field>'.
ALTER TABLE ALTER COLUMN <id-field> failed because one or more objects access this column.

Het verwijderen van de constraint voor de primary key kan wel, mits ik de naam daarvan weet. Die is op te halen met "EXEC sp_helpindex", maar hoe krijg ik de index_name dan in mijn remove-constraint query?
Als ik de constraint handmatig verwijder en daarna het veldtype aan pas, krijg ik de melding:

code:

Identity column '<id-field>' must be of data type int, ... or numeric with a scale of 0, 
and constrained to be nonnullable.

De identity flag is dus kennelijk nog niet weggehaald.

Het nogmaals kopieeren van alle data uit alle tabellen naar weer een nieuwe tabel kan natuurlijk ook... maar ik vind 1x alle data dupliceren al wel genoeg eigenlijk...

Kortom; hoe krijg ik het toevoegen van een id-kolom voor een primary key voor elkaar volgens (een van) bovenstaand (of andere) methode?

[curry684]

Verwijderd schreef op 16 juli 2004 @ 16:30:
Het verwijderen van de constraint voor de primary key kan wel, mits ik de naam daarvan weet. Die is op te halen met "EXEC sp_helpindex", maar hoe krijg ik de index_name dan in mijn remove-constraint query?

Tipje:

SQL:

declare @query varchar(512);
set @query = 'select * from 'mytable'
execute @query;

[whoami]

Verwijderd schreef op 16 juli 2004 @ 16:30:
Probleem: Hoe verwijder ik hierna het automatisch genereren van waarden door 'identity' en hoe verander ik het datatype van int naar varchar?
Als ik deze handelingen doe via de Enterprise Manager, gaat het goed, maar aangezien het voor 150+ tabellen moet gebeuren, doet ik dat liever met een script.
Het uitzetten van identity kan via

Je zult met een alter table statement dat datatype moeten veranderen.
Je zult wel (zoals je al ziet aan die foutmelding) eventuele relations eerst moeten droppen of disablen.

Waarom wil je die column eigenlijk nadien van int naar varchar veranderen?

[Verwijderd]

Die veldtypeconversie moet ivm het programma dat met de data moet werken. Die typering is van ondergeschikt belang; het automatisch genereren van het <id-field> door IDENTITY moet wel uitgeschakeld worden...
Het ALTER TABLE statement werkt niet vanwege de relaties...

Eerst eens aan de slag met de tip van curry684

[Verwijderd]

Met SQL Server kan je Transact SQL gebruiken. Dit is een procedureel taaltje, waarin je bijvoorbeeld een cursor kan openen voor de tabel en in een loop-je record voor record kan updaten. Er zijn beslist voorbeelden te vinden.

[P_de_B]

Verwijderd schreef op 17 juli 2004 @ 10:39:
Met SQL Server kan je Transact SQL gebruiken. Dit is een procedureel taaltje, waarin je bijvoorbeeld een cursor kan openen voor de tabel en in een loop-je record voor record kan updaten. Er zijn beslist voorbeelden te vinden.

Transact-SQL is juist geen procedureel taaltje, het is juist bedoeld om setbased te werken.

Het heeft wel een aantal ondersteuningen voor procedureel programmeren, maar het gebruik daarvan kan het best tot een minimum beperkt worden.

[LoekD]

Gebruik

code:

SET IDENTITY_INSERT ON

om identities te kunnen inserten.

Vergeet het niet weer op 'OFF' te zetten later..

[Verwijderd]

Transact-SQL lijkt inderdaad te kunnen wat ik nodig heb voor methode 1. Ik zal daar morgens eens mee aan de slag gaan op de SQL-server. Kan iemand me vast tippen over hoe ik test of ik buiten een recordset ben gelopen (wat in vb-script recordset.eof zijn zijn)?

Voor zover mij bekend werkt SET IDENTITY_INSERT [ON | OFF] zo dat als je een tabel hebt waar een 'identity' gezet is voor een veld, je toch de mogelijkheid krijg met de hand aan dat veld te prutsen. Dit commando haalt niet de functionaliteit (en bijbehorende constraints) van het veld, maar zet de functionaliteit volgens mij alleen even uit, wat mijn probleem bij methode 2 niet verhelpt.

[Annie]

Verwijderd schreef op 18 juli 2004 @ 13:29:
Kan iemand me vast tippen over hoe ik test of ik buiten een recordset ben gelopen (wat in vb-script recordset.eof zijn zijn)?

@@FETCH_STATUS

[SuperRembo]

Ik neem aan dat er al een unieke kolom is.
Met behulp van een update als deze kan je een kolom vullen op basis van een volgnummer.

SQL:

UPDATE Products
SET NewID = sub.NewID
FROM Products p
INNER JOIN (
    SELECT p1.ProductID, 'ID' + CAST(COUNT(p2.ProductID) AS varchar) AS NewID
    FROM Products p1 LEFT OUTER JOIN Products p2 ON p1.ProductID >= p2.ProductID
    GROUP BY p1.ProductID
) sub ON sub.ProductID = p.ProductID

En de query is eenvoudig aan te passen als je meerdere kolommen hebt die samen de unieke key vormen.

[Verwijderd]

Bedankt zover; ik heb een werkende oplossing voor Methode 1:

code:

DECLARE t_cursor CURSOR FOR
SELECT <newid> FROM <tabel>

declare @count int
select @count = 1 

OPEN t_cursor
FETCH NEXT FROM t_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE <tabel> SET <newid>=@count WHERE CURRENT OF t_cursor

  SELECT @count = @count + 1 
  FETCH NEXT FROM t_cursor
END
CLOSE t_cursor
DEALLOCATE t_cursor

Zit ik nog met 1 dingetje:
'UPDATE <tabel> SET <newid>=@count WHERE CURRENT OF t_cursor' is flink traag (warschijnlijk omdat in dit geval voor iedere regel van t_cursor dat record opnieuw wordt opgezocht in <tabel>. Kan dit niet sneller, door iets van 'UPDATE t_cursor SET <newid>=@count' (dit geeft een error: 'Invalid object name 't_cursor')?

[curry684]

Of je slaat gewoon het hele overbodige geneuzel met cursors over die je nooit moet gebruiken tenzij je ze nodig hebt, en gebruikt de SET-based aspecten van SQL:

SQL:

create table TestTable
    (
    myint int
    );
insert TestTable values(0);
insert TestTable values(0);
insert TestTable values(0);
insert TestTable values(0);
insert TestTable values(0);
insert TestTable values(0);

declare @myvar int;
set @myvar = 0;
update TestTable set myint = @myvar, @myvar = @myvar + 1;

select * from TestTable;
drop table TestTable;