Showing posts with label temp. Show all posts
Showing posts with label temp. Show all posts

Wednesday, March 28, 2012

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

Monday, March 26, 2012

Replace temp table with inline table-value function

PREFACE: We are getting rid of the temporary tables so I don't need to be
convinced not to use them.
In our current system we have a pattern where a temporary table is created
in one or more "calling" procedures and populated with selected keys of a
table and in the "called" procedure, those keys (from the temporary table)
are joined to a set of tables to produce a detail result set. Multiple
"calling" procedures exist that populate the temp key table based on various
criteria, but they all call the same "called" procedure which centralizes
the logic for pulling together the details.
This method causes concurrency problems because the "called" procedure is
re-compiled every time because it references a temporary table defined in
another procedure.
The method I have come up with to get rid of the temporary tables but to
still centralize and re-use the detail logic is as follows:
I have created an inline table-value function that replaces the common
"called" procedure in the above scenario. Now in the "calling" procedures,
instead of populating a temp table with keys and calling the "called"
procedure, they simply join the criteria with the user defined function,
selecting the needed fields from the results. Looking at the query plan,
this seems very optimal because it appears that the whole query (the key
criteria and the user-defined function statements) are merged together and
an execution plan is generated for them as a whole (instead of as 2 discrete
statements), giving me the best of both worlds: centralized, re-usable
logic, and a good execution plan.
My question is: Is there anything inherently non-scalable about using SQL
Server 2000's inline table-value function that will burn me under heavy
load?
Thanks,
Mike Jansen
(Abbreviated DDL follows)
OLD WAY
---
CREATE TABLE dbo.Entities
(
entity_pk int IDENTITY(100, 1) NOT NULL CONSTRAINT pk_Entities PRIMARY
KEY,
blah
blah
)
GO
CREATE PROCEDURE dbo.spGetEntityDetails
AS
SELECT
E.entity_pk, E.blah, E.blah, D.blah, D.blah
FROM
#EntityList E
INNER JOIN EntityDetails D ON E.entity_pk = D.entity_pk
GO
CREATE PROCEDURE dbo.spSeeOneGroupOfEntities
AS
CREATE TABLE #EntityList (entity_pk int NOT NULL PRIMARY KEY)
INSERT #EntityList (entity_pk)
SELECT E.entity_pk
FROM Entities E INNER JOIN .....
WHERE E.blah = 'one kind'
EXEC dbo.spGetEntityDetails
DROP TABLE #EntityList
GO
CREATE PROCEDURE dbo.spSeeAnotherGroupOfEntities
AS
CREATE TABLE #EntityList (entity_pk int NOT NULL PRIMARY KEY)
INSERT #EntityList (entity_pk)
SELECT E.entity_pk
FROM Entities E INNER JOIN .....
WHERE E.blah = 'another kind' AND ...
EXEC dbo.spGetEntityDetails
DROP TABLE #EntityList
GO
NEW WAY
---
CREATE FUNCTION dbo.fnGetEntityDetails()
RETURNS TABLE
RETURN
(
SELECT D.entity_pk, D.blah, D.blah, D2.blah, D2.blah
FROM EntityDetails D INNER JOIN EntityDetails2 D2 ON ....
)
GO
CREATE PROCEDURE dbo.spSeeOneGroupOfEntities
AS
SELECT
E.entity_pk, D.blah, D.blah
FROM
Entities E INNER JOIN dbo.fnGetEntityDetails() D ON E.entity_pk =
D.entity_pk
WHERE
E.blah = 'one criteria' AND ...
GO
CREATE PROCEDURE dbo.spSeeAnotherGroupOfEntities
AS
SELECT
E.entity_pk, D.blah, D.blah
FROM
Entities E INNER JOIN dbo.fnGetEntityDetails() D ON E.entity_pk =
D.entity_pk
WHERE
E.blah = 'another criteria' AND ...Noting to self that my descriptions can be a little long (and hence take too
long to read...), here's my question succinctly:
Is there anything inherently non-scalable about using SQL Server 2000's
inline table-value function that will burn me under heavy
load?
Thanks,
Mike|||> Is there anything inherently non-scalable about using SQL Server 2000's inline table-valu
e
> function that will burn me under heavy
> load?
Not that I know of. I haven been told that they are optimized and used in th
e same way as views (and
they were called parametized views during early stages of development of SQL
Server 2000). I can't
offer proof or similar, I'm afraid, but that are my experiences and what I h
ave been told.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O19%23vR5kFHA.1044@.tk2msftngp13.phx.gbl...
> Noting to self that my descriptions can be a little long (and hence take t
oo long to read...),
> here's my question succinctly:
> Is there anything inherently non-scalable about using SQL Server 2000's in
line table-value
> function that will burn me under heavy
> load?
> Thanks,
> Mike
>|||Mike Jansen wrote:
> Noting to self that my descriptions can be a little long (and hence take t
oo
> long to read...), here's my question succinctly:
> Is there anything inherently non-scalable about using SQL Server 2000's
> inline table-value function that will burn me under heavy
> load?
I agree with Tibor.
A couple of years ago, I did a good bit of tuning work on a system that
made heavy use of udf's. My experience was positive with inline
table-valued functions. The plans produced looked to me like the
optimizer treats them as it would a view or a derived table. It can
"see inside" them and optimize to the base table level. I think I saw
this behavior even when nesting functions.
Multistatement table-valued functions, on the other hand, seemed to be
a black box to the optimizer. That makes sense. How could he (the
optimizer) evaluate the logic that could be inside a multi-statement
function. Instead, it uses a table scan of whatever table variable is
returned.
Best of luck
Payson

> Thanks,
> Mike

Wednesday, March 21, 2012

Replace first 3 digit of phone numbers

Hi All,

I am trying to replace only first 3-digit of phone number starting with 011 to 000.

CREATETABLE #temp(stringRep VARCHAR(60))

INSERTINTO #temp(stringRep)VALUES('011-203-0011')

INSERTINTO #temp(stringRep)VALUES('011-203-2333')

DECLARE @.startPos INT

SET @.startPos = 1

UPDATE #temp SET stringRep =REPLACE(stringRep,SUBSTRING(stringRep, 1,

3),'000')

SELECT*FROM #temp

DROPTABLE #temp

stringRep

000-203-0000
000-203-2333

it also replaces the last three digit with 000. the result i would like to get is

stringRep

000-203-0011
000-203-2333

can anybody advise me on this? thanks!

Try:

update #temp

set stringRep = stuff(stringRep, 1, 3, '000')

where stringRep like '011%'

AMB

sql