Prev: There is insufficient system memory to run this query - system.web
Next: deletion cause transacation log to grow
From: pvong on 19 Jul 2008 07:48
I'm trying to do this in SQL 2005. I have a table with no unique identifier
and I need to add this now. Say I have Table1 and I created a column called
ColA. There are already 7000+ records in this table and I want to inject a
number value in ColA from 1-what ever. I'm guessing this is going to be an
update command but I don't know what to use.

I couldn't set ColA as a unique ID because I get the you can not have Null
value in that column when I try to save.

Thanks!


From: Plamen Ratchev on 19 Jul 2008 08:31
Is there any other column or columns that can be used to uniquely identify
each row? If you need to update the new column with a number is there any
particular ordering or sequence? How do you plan to update this in the
future if it is not IDENTITY/GUID?

Assuming there is no meaning to the numbers and they will be just used as
unique identifiers, you can simply add the column as IDENTITY, which will
automatically populate the numbers for you:

CREATE TABLE Foo (
col2 CHAR(1));

INSERT INTO Foo VALUES('a');
INSERT INTO Foo VALUES('b');

GO

ALTER TABLE Foo ADD col1 INT IDENTITY(1, 1) NOT NULL;

GO

SELECT col1, col2
FROM Foo;


HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: pvong on 20 Jul 2008 15:32
Thanks. That did it. That created my unique ID column for me. You brought
up a good question and I'm not sure what the answer is. I import data on a
daily basis from a csv file and the csv file does not have the unique
column. Will this auto put the unique ID now that I've used
ALTER TABLE Foo ADD col1 INT IDENTITY(1, 1) NOT NULL; ?

Basically, how do you get sql to auto fill the unique ID column when it's
from an import? Just in case your wondering, I'm using SSIS to import my
csv file.

Thanks from a newbie


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:F48C1500-E9BA-4F2E-8C0D-B8855EDCE813(a)microsoft.com...
> Is there any other column or columns that can be used to uniquely identify
> each row? If you need to update the new column with a number is there any
> particular ordering or sequence? How do you plan to update this in the
> future if it is not IDENTITY/GUID?
>
> Assuming there is no meaning to the numbers and they will be just used as
> unique identifiers, you can simply add the column as IDENTITY, which will
> automatically populate the numbers for you:
>
> CREATE TABLE Foo (
> col2 CHAR(1));
>
> INSERT INTO Foo VALUES('a');
> INSERT INTO Foo VALUES('b');
>
> GO
>
> ALTER TABLE Foo ADD col1 INT IDENTITY(1, 1) NOT NULL;
>
> GO
>
> SELECT col1, col2
> FROM Foo;
>
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 20 Jul 2008 18:54
Yes, in your case importing the CSV file via SSIS will populate
automatically the unique values in the column. Just make sure in the
mappings of input file to columns the input is set to <ignore> for the new
column, and that will automatically generate on insert new values.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: --CELKO-- on 21 Jul 2008 15:28
>> I have a table with no unique identifier and I need to add this now. <<

A table **by definition** must have a key. What you have is a non-
sequential file written with SQL. A kludge is to use IDENTITY or some
other non-relational numbering to make it into a sequential file; but
it is still not a table.

Since we have no idea what this "table" is supposed to be, we cannot
suggest a proper key for it. In RDBMS, there is no such thing as
"magical, universal, one-size-fits-all" key; it has to be particular
to the table and the data element being modeled.
 |  Next  |  Last
Pages: 1 2
Prev: There is insufficient system memory to run this query - system.web
Next: deletion cause transacation log to grow