Showing posts with label update. Show all posts
Showing posts with label update. Show all posts

Wednesday, March 28, 2012

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid =
(
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__Stephen
I got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid =
> (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid =
(
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__StephenI got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid =
> (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

normalize data run

I have an table (DDT) that I have ID, Name as columns. Second table (DDC) I
have added the FK-ID column and I now want to update that null value with
the ID from DDT.
update DDC
set tableid = (
select ddt.tableID from ddc
left join ddt
on ddc.Name = ddt.Name
)
select * from ddc
That gives to many returns, so adding Top 1 only gives #1 to all rows?
What do I need to do to update 30,000 rows from a set of 600+?
TIA
__StephenI got it straight.
(
select tableID from ddt
where ddt.tablename = ddc.tablename
)
"_Stephen" <srussell@.electracash.com> wrote in message
news:esvWGJpTGHA.1576@.tk2msftngp13.phx.gbl...
>I have an table (DDT) that I have ID, Name as columns. Second table (DDC)
>I have added the FK-ID column and I now want to update that null value with
>the ID from DDT.
> update DDC
> set tableid => (
> select ddt.tableID from ddc
> left join ddt
> on ddc.Name = ddt.Name
> )
> select * from ddc
> That gives to many returns, so adding Top 1 only gives #1 to all rows?
> What do I need to do to update 30,000 rows from a set of 600+?
> TIA
> __Stephen
>

Monday, March 26, 2012

Normalization question

Hello,

I'm building an OLTP employee roster application, and I have the data model normalized, but I'm finding it challenging to update the data in all of the underlying tables via a web form and stored procedures. I'm getting the direction to keep things normalized, but I'm struggling with table updates.

Is it sound OLTP database practice to write records to a "roster_data" table that is a foreign key repository for all of the underlying tables (almost like a view without the update restrictions)? It would be much easier to manage this table of foreign keys (fact table) instead of writing several stored procedures to keep all of the related tables (dimensions) updated. (almost like a star schema in OLAP)

I know I'm being vague, but I would like to bounce off some OLTP experts.

Thanks,

Jim

What type of data is being changed?

Is it key data?

Would CASCADE UPDATE work?

More information, table DDL, etc., would be useful.

|||

Not that you are a beginner in normalization topic, but the following links should get you the idea in thsi regard:

http://www.informit.com/articles/article.asp?p=27785&rl=1 & http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887

|||

Everyone,

My basic issue was keeping my underlying normalized data model updated via one VIEW, and I was running into the "can't updated more than one base table issue." After some research, I found the INSTEAD OF triggers -- this functionality will enable me to update all of my underyling tables using one VIEW.

Thanks everyone and have a great holiday!

Jim

Monday, February 20, 2012

No sync - No Initialize & scripts

If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
In SQL2000, you need to call sp_scriptpublicationcustomprocs to generate a script for creating the sp_MSins|del|upd procedures at the subscriber and then call sp_MSaddsynctrigger for each article at the subscriber to create the immediate updating triggers. The following kb has more details on this:
http://support.microsoft.com/default...b;en-us;320499
In SQL2005 (publisher), we create the replication system objects for you automatically if you use 'replication support only' or 'initialize with backup' as the @.sync_method in sp_addsubscription (SQL2005 Management Studio will use "replication support only" if you choose not to initialize data\schema)
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eTe%23PyJ5FHA.3276@.TK2MSFTNGP10.phx.gbl...
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
|||It is Sql 2000, Thanx for the link to the kb! You're a great help!!!
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:uSamV6J5FHA.2524@.TK2MSFTNGP10.phx.gbl...
In SQL2000, you need to call sp_scriptpublicationcustomprocs to generate a script for creating the sp_MSins|del|upd procedures at the subscriber and then call sp_MSaddsynctrigger for each article at the subscriber to create the immediate updating triggers. The following kb has more details on this:
http://support.microsoft.com/default...b;en-us;320499
In SQL2005 (publisher), we create the replication system objects for you automatically if you use 'replication support only' or 'initialize with backup' as the @.sync_method in sp_addsubscription (SQL2005 Management Studio will use "replication support only" if you choose not to initialize data\schema)
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eTe%23PyJ5FHA.3276@.TK2MSFTNGP10.phx.gbl...
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS