Showing posts with label key. Show all posts
Showing posts with label key. Show all posts

Wednesday, March 28, 2012

replacing Apostrophe

I’m trying to key the syntax for replacing the Apostrophe with double
Apostrophe and update table.
Example: Programmer’s with Programmer’’s
Here code for finding occurrences:
SELECT DEALNAME
FROM DLWKTABLE20060609
where dealname like '%['']%'
BUT how do I update:
Update dlwktable20060609
Set dealname = replace(dealname, ?,?)
where dealname like '%['']%'declare @.c varchar(50)
select @.c ='O''Brian'
select replace(@.c, '''',''') ,@.c
Then the update will be
Update dlwktable20060609
Set dealname = replace(dealname, '''',''')
where dealname like '%['']%'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Logger wrote:
> I'm trying to key the syntax for replacing the Apostrophe with double
> Apostrophe and update table.
> Example: Programmer's with Programmer''s
> Here code for finding occurrences:
> SELECT DEALNAME
> FROM DLWKTABLE20060609
> where dealname like '%['']%'
> BUT how do I update:
> Update dlwktable20060609
> Set dealname = replace(dealname, ?,?)
> where dealname like '%['']%'

Replacing a Primary Key

Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks
Can you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks
|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks
|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks
|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]
|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]
|||Is this the Steps Tom:
..
_______
| Tech | -- 1 -- --N [ Ticket ]
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
..
_______
| Tech | -- 1 -- --N [ Ticket ]
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||When do i make the new Column the Primary key and whats the code to do that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
sql

Replacing a Primary Key

Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
ThanksCan you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we wan
t
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:[vbcol=seagreen]
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>[quoted text clipped - 10 lines]|||Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:[vbcol=seagreen]
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>[quoted text clipped - 15 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||When do i make the new Column the Primary key and whats the code to do that
?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_I
d
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id reference
s
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above fo
r
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via droptable.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:[vbcol=seagreen]
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1

Replacing a Primary Key

Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
ThanksCan you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>>Can you give us some more detail? What exactly is being moved? How about
>>posting the DDL for the tables involved?
>[quoted text clipped - 10 lines]
>>Thanks|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>>Can you give us some more detail? What exactly is being moved? How about
>>posting the DDL for the tables involved?
>[quoted text clipped - 10 lines]
>>Thanks|||Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>>Could you change the FK to use ON DELETE CASCADE and then just update the
>>PK?
>[quoted text clipped - 15 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via SQLMonster.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>>Could you change the FK to use ON DELETE CASCADE and then just update the
>>PK?
>[quoted text clipped - 15 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||When do i make the new Column the Primary key and whats the code to do that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>>I'd do the following:
>[quoted text clipped - 25 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via SQLMonster.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>>I'd do the following:
>[quoted text clipped - 25 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1

Replacing a column with a foreign key

In an *existing* database, how would you remove the City column in theEmployees table, and put in the CityID key column from the Cities table?first you would add a cityid column with alter table add column syntax.
Then to add the data, you could do a join with cities table.

UPDATE
Employees
SET
Cityid = C.CityID
FROM
Employees E
INNER JOIN Cities C
ON E.city = C.city

Then, removing the city column should be as simple as alter table remove column.|||

The following simplified version also works:

UPDATE Employees
SET Cityid = C.CityID
FROM Cities C
WHERE Employees.City = C.City

Replace-type function for Text datatype

I have a table that has a Text datatype column that has gotten some
garbage
characters in it somehow, probably from key entry. I need to remove
the garbage, multiple occurances of char(15). The replace function
does not work on Text datatype. Any suggestions?Zack Sessions (zcsessions@.visionair.com) writes:
> I have a table that has a Text datatype column that has gotten some
> garbage
> characters in it somehow, probably from key entry. I need to remove
> the garbage, multiple occurances of char(15). The replace function
> does not work on Text datatype. Any suggestions?

One way would be to iterate over the table, and for each row get slices
of 8000 chars to a varchar value on which you run replace(). You would
then use updatetext to update the row. A bit tricky, because if first
got chars 1 to 8000, and removed 6 char(15), you should now start on
char 7994 for the next batch.

Not particularly funny, I know.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hello:

You could write a small vbscript that would loop thru the table and
update the text columns using ado's appendchunk method and the replace
function in vbscript.

See link below on an example that you can adapt to vbscript and your
problem:

http://msdn.microsoft.com/library/d...ples_vb01_8.asp

HTH,

BZ

zcsessions@.visionair.com (Zack Sessions) wrote in message news:<db13d9fb.0308251131.3bb5360d@.posting.google.com>...
> I have a table that has a Text datatype column that has gotten some
> garbage
> characters in it somehow, probably from key entry. I need to remove
> the garbage, multiple occurances of char(15). The replace function
> does not work on Text datatype. Any suggestions?|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93E2DFB86DD18Yazorman@.127.0.0.1>...
> Zack Sessions (zcsessions@.visionair.com) writes:
> > I have a table that has a Text datatype column that has gotten some
> > garbage
> > characters in it somehow, probably from key entry. I need to remove
> > the garbage, multiple occurances of char(15). The replace function
> > does not work on Text datatype. Any suggestions?
> One way would be to iterate over the table, and for each row get slices
> of 8000 chars to a varchar value on which you run replace(). You would
> then use updatetext to update the row. A bit tricky, because if first
> got chars 1 to 8000, and removed 6 char(15), you should now start on
> char 7994 for the next batch.
> Not particularly funny, I know.

Thanks for your response.

I actually thought of trying to do it this way and started to write
the code, but I got stuck on how to get the 8000 character chunks. The
way I read the READTEXT description, it does not return the value into
a local variable. I know how to get the first 8000 characters into a
local varchar, but I haven't figured out how to get any remaining 8000
character chunks. Care to give me a little more help?

Monday, March 26, 2012

Replacement for MAX statement?

Hi all :
I got a problem here.

The scenario :
The serial key code of a table is auto generate, and currently I'm using MAX to return the latest serial key code. The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. The serial key code is the only unique key in the table.

Can anyone give me some guide or tips on how to resolve this? Or other way of implementing the select statement to avoid usage of MAX?

Thanks in advance.the best way to avoid this is not to return the latest serial key at all

what do you need it for?|||I need to get the serial key code as a foreign key to insert into another table. The Serial key code will server as a customer ID in my case. since no customer ID can be and should be duplicate, I need to get the serial key code.

I had figure out a way, using a select * statement and use a while loop to loop thru the whole result set collection. From there I can get the last serial key code.

I got a question here, if I use this way, will it be less effective than using MAX? Consider I got a table with more than 5000 record.

Thanks.|||On the table you're inserting into, will the SerialKeyCode be unique, or will you have a one-to-many relationship?|||Serial Key Code will be generate automatically by the database and been set to unique with increament by 1 everytime new record is add.|||The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. Thanks in

If you return the maximum serial ID at 14:00 and then again at 14:01 they may indeed be different. I don't see a problem. If you wish to keep an accurate record, then you could implement an insert trigger to store the most recent serial code in another table.|||here's what you do --

insert a row, and let the database automatically assign the key

use the provided database function to retrieve the value of the key that was assigned

in SQL Server, this is the @.@.identity function, in mysql it is mysql_insert_id(), use whatever function your database provides

if your database does not provide such a function, then simply query back the row that you just inserted, not with MAX but with the values of the other columns

then use the value of the key as the foreign key when you insert the related record

for example, if you insert a username/password combination of 'fred','sesame' into a user table, then you instead of selecting MAX or (shudder!) returning the entire table to see the last one, just run this query:

select userid from users where username='fred' and password='sesame'

the automatically incremented userid value that you get back will be what you use to insert the related row in the other table|||Thanks for your suggestion. The problem is that there r no unique key in the table except the auto serial key code assign by the database. I can not use the second method u suggest. About the first suggestion, it's great, but only if I know what kind of statement the databse provide for me to get the number assign. I'm using Informix. It would be great if you can give some hints on the command to use to get the sequence number. Thanks|||ah, informix

look up NEXTVAL and CURRVAL in the manual

the serial number is actually separate from the table, and you use NEXTVAL to get the next value that you insert into the table|||The process is like this :

1)Insert into table A
2)Get the result set from table A (for the latest serial key code)

What you try to said is that I need to use NEXTVAL before step 1 to get the serial key code I need.

I'm using Java. I need to write SQL statement for NEXTVAL or in the Java Code.

Would be appreciate if you can give any hints. TY.|||Insert into tableA values (seq.nextval, column1, column2)
select * from tableA where id = seq.currVal

Wednesday, March 21, 2012

replace funtion

how do i write a replace function that will replace a certain character with a return key (ie what happens when we do Ctrl+return key in SQL Enterprise table... so that the rest of the cell data in the column is on the next line?!

SELECT REPLACE(tasks, '/', '??') AS EXPR1
FROM log_descriptions

what should ?? be?I think you want the char function with the ascii code for line break.|||I alway forget the ANSII code for a line break so I use this instead:
SELECT REPLACE(tasks, '/', '
') AS EXPR1
FROM log_descriptions

Works like a charm :)|||or this, maybe easier to read in a big script :)

declare @.cr char(1)
set @.cr = '
'
select 'a' + @.cr + 'b'|||Char(13)+char(10)|||13 & 10... I'll try to rember that :D

So summing this up we got:
DECLARE @.cr CHAR(1)
SET @.cr = CHAR(13) + CHAR(10)
SELECT 'a' + @.cr + 'b'