Any help is welcome: Newbee - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Any help is welcome: Newbee

Author

Topic

David Lopez
Starting Member

19 Posts

Posted - 2005-06-27 : 11:58:18

Hi,

I am trying to convert a table of an old system (CLIPPER) to SQL Server 2003.
But in the new table, some columns can´t be NULL.
And in the old one it doesn´t exists the table, so I cam up with:

THE FIRST PART WORKS FINE===
INSERT INTO NEWTABLE (COL1,COL2,COL3,...)
SELECT COL1,COl2,.....
FROM OLDTABLE
WHERE (TABLEX <>'0')

===THIS PARTS IS WHERE I CAN´t GET A COUNTER running!!
IDCUENTA = 100
UPDATE NEWTABLE
SET ENTERPRISE = 9000, IDCOUNT = IDCOUNT + 1
WHERE ENTERPRISE = 0
===

Any help is welcome.

I am running the system from the w2003 SQL Server module.

David Lopez

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-27 : 12:05:43

DECLARE @AShortIterationCounterVariable INT

SET @AShortIterationCounterVariable = 100
UPDATE NewTable
SET @AShortIterationCounterVariable = IDCOUNT = @AShortIterationCounterVariable + 1 ,
Enterprise = 9000

David Lopez
Starting Member

19 Posts

Posted - 2005-06-27 : 12:47:03

quote:
Originally posted by SamC

DECLARE @AShortIterationCounterVariable INT
SET @AShortIterationCounterVariable = 100
UPDATE NewTable
SET @AShortIterationCounterVariable = IDCOUNT = @AShortIterationCounterVariable + 1 ,
Enterprise = 9000

hx it works,

But strangly I can´t use the first part as it states that it can not have duplicated keys (which is correct).

Is there a way to do the second part always after I have inserted a line?

Many thx to any help

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-27 : 13:59:51

quote:
Originally posted by David Lopez

THE FIRST PART WORKS FINE===
INSERT INTO NEWTABLE (COL1,COL2,COL3,...)
SELECT COL1,COl2,.....
FROM OLDTABLE
WHERE (TABLEX <>'0')

===THIS PARTS IS WHERE I CAN´t GET A COUNTER running!!
IDCUENTA = 100
UPDATE NEWTABLE
SET ENTERPRISE = 9000, IDCOUNT = IDCOUNT + 1
WHERE ENTERPRISE = 0

quote:
But strangly I can´t use the first part as it states that it can not have duplicated keys (which is correct).

Is there a way to do the second part always after I have inserted a line?

Can you be more specific about what is not working? What error message?

On the "First Part" not allowing duplicate keys, why not eliminate the duplicate keys before inserting... and what are the UNIQUE key column(s) (if you want an accurate answer).

Maybe you should read Brett's post about how to give all the needed information [url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Without further info...

INSERT INTO NEWTABLE (COL1,COL2,COL3,...)
SELECT DISTINCT COL1,COl2,.....
FROM OLDTABLE
WHERE (TABLEX <>'0')

OR...

INSERT INTO NEWTABLE (COL1,COL2,COL3,...)
SELECT DISTINCT COL1,COl2,.....
FROM OLDTABLE
WHERE (TABLEX <>'0')
AND ColN NOT IN (SELECT ColN FROM NewTable) -- Eliminate Duplicates

On the other hand, you might consider creating a column in NewTable like this:


CREATE TABLE NetTable (
  IDCOUNT INT IDENTITY(9000,1) UNIQUE NOT NULL, 
  ...
)

So IDCount is automatically incrimented... but you don't mention if it should be incremented ONLY when Enterprise = 9000 or if there is any dependency on Enterprise. (I imagine that IDCount's value is somehow linked to Enterprise?)

If there is an undocumented dependency on Enterprise, such as: IDCount starts at "Enterprise" and incriments by 1 for each additional row, then...

... it's a lot harder. Maybe Dr. Cross Join can help.

David Lopez
Starting Member

19 Posts

Posted - 2005-06-28 : 02:34:28

Hi SamC,

The whole thing is that the new table has a key wich increment automaticly, but can not be duplicated (the IDCOUNT simulates that number)

So when the TABLEX (of the old table) isn´t '0' it will copy that record, but by inserting the new record it has to change the IDCOUNT into a value which is permitted. Cause if it doesn´t it will be ZERO (0), and it will be duplicated. Which it can´t do.....

Thx,

David

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-28 : 07:13:24

quote:
Originally posted by David Lopez

The whole thing is that the new table has a key wich increment automaticly, but can not be duplicated (the IDCOUNT simulates that number)

quote:
On the other hand, you might consider creating a column in NewTable like this:

CREATE TABLE NetTable (
IDCOUNT INT IDENTITY(9000,1) UNIQUE NOT NULL,
...
)

Did you try an IDENTITY column like above?

David Lopez
Starting Member

19 Posts

Posted - 2005-06-28 : 12:04:54

quote:
Originally posted by SamC

quote:
Originally posted by David Lopez

The whole thing is that the new table has a key wich increment automaticly, but can not be duplicated (the IDCOUNT simulates that number)

quote:
On the other hand, you might consider creating a column in NewTable like this:

CREATE TABLE NetTable (
IDCOUNT INT IDENTITY(9000,1) UNIQUE NOT NULL,
...
)

Did you try an IDENTITY column like above?

I don´t tink that creating an new column is the solution. Because the colum TABLEX is a key which is neede later and can not be duplicated or NULL.

Is there a way that after the first INSERT of a record you do a routine (updating the column TABLEX)?
And then you go to the second record.

I was thinking of such a thing like "DO WHILE NEOF" stuff.

Regards,

David

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-28 : 15:26:33

quote:
Originally posted by David Lopez

Is there a way that after the first INSERT of a record you do a routine (updating the column TABLEX)?

Yes, triggers, but its unnecessary if you only need an IDENTITY column.

So... the column you want to incriment is TableX.

Also, I'm not sure we're talking about adding a new column. You could modify your existing column to be IDENTITY.

Could you post the DDL for this table? e.g.

CREATE TABLE MyTable (...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-29 : 01:47:44

i think i did this once for a friend, check if you have the same scenario

the new table has autoincrement, but the old one has values already (and he needs to preserve the IDs), so what i did was:
1. turn off autoincrement first on the new table
2. copied all the records with the unique ID
3. turned on autoincrement
4. copied all the records that do not have unique ID but using the max(ID) for seed - the new records get new ID nicely provided by autoincrement
5. issued a reseed statement to adjust the IDs

HTH

--------------------
keeping it simple...

David Lopez
Starting Member

19 Posts

Posted - 2005-06-29 : 02:32:39

quote:
Originally posted by jen

i think i did this once for a friend, check if you have the same scenario

the new table has autoincrement, but the old one has values already (and he needs to preserve the IDs), so what i did was:
1. turn off autoincrement first on the new table
2. copied all the records with the unique ID
3. turned on autoincrement
4. copied all the records that do not have unique ID but using the max(ID) for seed - the new records get new ID nicely provided by autoincrement
5. issued a reseed statement to adjust the IDs

HTH

--------------------
keeping it simple...

The new table is not incremente automatically. It is just that the TableCOlumn is a KEY. In the program you personally add the number by hand, and can not be duplicated because the number is unique for every office (more than 2000 offices).

Let me explain everything again, cause I believe I am off track.

I have an old table, which doens´t has a key or stuff like that with some columns. No the new table has two keys, one the code for the enterprise and another for the office. The second column is UNIQUE and can not be duplicated. Althought in the OLD table there is no columns for the enterprise, so which I have to add by hand or by an UPDATE.
What works is that if I do an insert for a certain number of office (WHERE OLDTABLE.OFFICE=30) and then an update as I wrote before. That works, but it is gfood if you have to do 10 record, not a 2000.
Let me try to write down what I have in mind:

INSERT INTO NEWTABLE(NEWCol1,NEWCol2,NEWCol3,....,NEWColX)
SELECT DISTINCT (OLDCol1,OLDCol2,OLDCol3,...,OLDColX)
FROM OLDTABLE
WHERE (COD_BAN = 21

===Now I update the table and change the colums CODEENTERPRISE to 9000 ===

DECLARE @AShortIterationCounterVariable SmallINT
SET @AShortIterationCounterVariable = 28
UPDATE NEWTABLE
SET @AShortIterationCounterVariable = IDOFFICE = @AShortIterationCounterVariable + 1, CODEENTERPRISE = 9000
WHERE CodigoEmpresa = 0

=====
What I can not get working is that after every INSERT of the first part it does the second part (Changing the code of the Enterprise and adidng a number to the ID of the Office)

Any ideas????

David

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-29 : 10:41:11

Please post the DDL for the target table.

Any reason why an IDENTITY column for TableX isn't a good idea?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources