November 9, 2011 at 11:00 am
Hi,
I got there tables
Table A, Table B, Table C
Table A
CREATE TABLE [dbo].[TableA](
[ID] [int] NULL,
[Name] [char](10) NULL,
[Class] [char](10) NULL,
[SUbject1] [varchar](2) NULL,
[subject2] [varchar](2) NULL
) ON [PRIMARY]
Table B
CREATE TABLE [dbo].[TableB](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Subject1] [varchar](2) NULL,
[subject2] [varchar](2) NULL
) ON [PRIMARY]
Table C
CREATE TABLE [dbo].[TableC](
[NameId] [int] NOT NULL,
[Name] [char](10) NULL,
[SubId] [int] NOT NULL
)
Data in table A
INSERT INTO [SampleData].[dbo].[TableA]
([ID]
,[Name]
,[Class]
,[SUbject1]
,[subject2])
VALUES
(1,'Raj','A','X1','X2'),
(2,'Raj','B','X3','X4'),
(3,'Sat','A','X2','X1')
GO
Now if i do
Select * from tableA where name='Raj'
i will get the result set as
IDNameClassSUbject1subject2
1Raj A X1X2
2Raj B X3X4
Here from the result set i want to insert on row by row basis into the two tables TableB and Table C
like intially i will take first row and want to insert into Table B with values of X1 and X2 and take that value of ID in table B with scopeIdentity and insert into TableC with Raj and 1
Like from the above result set intially i should insert into TableB
So Table B will be like
1--X1--X2
Now i want to take that 1 value in table B and insert that to tableC likw
1--Raj--1
Again for the second row.
Sorry if i'm confusing you. I want to avoid using cursors. So how can i do that with set based operations.
November 9, 2011 at 11:06 am
You do it with two separate queries. One to update table "B" and then another to update table "C".
You are thinking procedurally, not set-based, whenever you say you want to do something on a row-by-row basis.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 9, 2011 at 11:32 am
The other option is to use the OUTPUT clause to return ALL of the new IDs rather than just the LAST new ID that you would get with scope identity.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 9, 2011 at 11:40 am
TableA = TableB joined to TableC
This is, in my opinion, bad design. It's redundant. Tables B and C can always be derived from TableA and TableA can always be derived from B + C.
I would suggest figuring out what exactly it is you're trying to accomplish rather than focusing on how to do something that doesn't make sense in a set based fashion.
November 9, 2011 at 11:45 am
I assumed that Table A was a staging table of some kind and that the OP was trying to move data to Tables B and C.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 9, 2011 at 11:47 am
Table A is a staging table and we are pulling data from that table to tableB and tableC
November 9, 2011 at 11:59 am
drew.allen (11/9/2011)
The other option is to use the OUTPUT clause to return ALL of the new IDs rather than just the LAST new ID that you would get with scope identity.Drew
So what do you do with the results of the OUTPUT? You do a second query to update "C". Unless of course the OUTPUT clause is enough by itself to be used as table C.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 9, 2011 at 12:06 pm
I understand that is a pseudo staging table. Normally staging tables are used to take flat data and move it into a relational format. That's not going on here. This is merely splitting a table into two separate tables with a 1 to 1 "relationship" based on an irrelevant identity. All of the advantages of relational design are lost. Instead of wasting the time and storage splitting it, it would be more effective to replace TableB and TableC with views based on TableA.
A better option would be to implement true relational design.
November 10, 2011 at 12:11 am
NO, you have garbage; Tables have keys and you do not. Tables ought to in at east First Normal Form and the garbage is not.
It may be a fact but may also an example of offensive language.
For SET operation, do we really neeed keys & normal forms?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply