Insert from distinct + Scope Identity + write back

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert from distinct + Scope Identity + write back

Author

Topic

zaurska
Starting Member

12 Posts

Posted - 2008-08-21 : 09:31:48

Hi,

I have two tables set out as follows:

-----------------------------------------------------------------------------------------
Table A

col col

PKey

ID_a Name

1 abc1
2 abc2

---------------------------------------------------------------------

Table B

col col col col col col

PKey
ID Name x y ID_a otherID

1 xyz1 a s Null 105
2 xyz2 a s Null 105
3 xyz3 a s Null 105
4 xyz4 a s Null 105
5 xyz5 a t Null 105
6 xyz6 a t Null 105
7 xyz7 b s Null 107
8 xyz8 b s Null 107
9 xyz9 b s Null 107
10 xyz10 b t Null 107

---------------------------------------------------------------------------------------

I want to insert a new row to Table A for each unique group in Table B and write the new back to Table B.

The uniqueness of the entries in Table B is defined by the combination of col. x and col. Y.

So, for example I’d want to insert a new ref: [3], [abc3] to Table A and then simultaneously/imediately populate Table B -> column: ID_a, with [3] on rows 1,2,3 & 4, for the unique combination of a + s

And would want to apply this restricted by the value in otherID, with a @parameter. So in the example above, only affect rows 1 to 6 when using the parameter ‘105’, and create 2 new rows in Table A.

I guess I need to use SCOPE IDENTITY(), INSERT and @parameters, but don’t know syntax well enough (at all)

I’d like to use a stored procedure to do this.

Any help gratefully received

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 09:42:44

Didnt get what you're asking for? How do you relate which rows to be populated on table B based on values put in A?I see a common ID_a field but its having all NULLs in tableB. Can you explain the relation please?

zaurska
Starting Member

12 Posts

Posted - 2008-08-21 : 11:25:24

Yes, Sorry, I wasn't clear. The nulls are the starting point; I want to finish with the nulls populated by the ID_a, which should be created by the insert to TableA. I am finding difficult the concept of INSERTing the number of records equal to the number of distinct rows in the TableB query and passing back that new reference to TableB.

The idea is that I want to associate the items in table B by their common attributes and ID them by the TableA PK.

So, I want to end up with the following, after running a stored Proc Insert, Scope Identity() thing (maybe?) for @param = 105

Table A

1 abc1
2 abc2
3 abc3 (new)
4 abc4 (new)

1 xyz1 a s 3 105
2 xyz2 a s 3 105
3 xyz3 a s 3 105
4 xyz4 a s 3 105
5 xyz5 a t 4 105
6 xyz6 a t 4 105
7 xyz7 b s Null 107
8 xyz8 b s Null 107
9 xyz9 b s Null 107
10 xyz10 b t Null 107

am I doing something fundamentally wrong? I'm pretty new to SQL, sorry. I know I'm not clear but I don't really know the words to use.

I guesss I'm saying that TableB is my starting point to determine what I want to INSERT into TableA then use those new Rows to Update TableB now that I have a unique reference

The data in TableB has rows that are not uniquely related except in combination and I want to associate them to eachother and a PK by adding another field: being the PK inTable A.

So I want to say: for every row in Table B where X and Y are the same and the final field = @paramater, INSERT a new row to TableA, then use this row's ID and write that back to the relevant rows in TableB....and I thought I could use ScopeIdentity() for that.

I could achieve it in C# with loops but that feels not elegant.

thanks
D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 12:53:59

Still unclear what's relation between abc3 and xyz1,xyz2,..? what prompted you to put them ID_a value of 3 which is abc3 row's pk value?

zaurska
Starting Member

12 Posts

Posted - 2008-08-21 : 12:58:45

abc3 is just a name to attach to the single primary key. xyz1, xyz have no priamry relationhip except by evaluating the groupBy / distinct for them until I specifically make the association. TableA only exists to define this uniqueness. Here's something that nearly works:

Declare @paramA int
Declare @scopeIdValue int

Set @paramA = 105 /*for test, would get from c# application*/

SELECT DISTINCT x, y, otherID=@paramA
FROM TableB

INSERT INTO TableA VALUES('')

SET @scopeIdValue = SCOPE_IDENTITY()

UPDATE TableB
SET ID_A =@scopeIdValue

only I want to INSERT for each row of the SELECT DISTINCT rather than get the same scopeIdentity for the whole SELECT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 13:09:24

do like this:-

DECLARE @INSERTED_VALUES table
(
ID_a int,
Name varchar(100)
)

INSERT INTO TableA (Name)
OUTPUT INSERTED.ID_a,INSERTED.Name INTO @INSERTED_VALUES
SELECT DISTINCT x + y
FROM TableB
WHERE otherID=@ParameterValue

UPDATE b
SET b.ID_a=i.ID_a
FROM TableB b
INNER JOIN @INSERTED_VALUES i
ON i.Name=b.x+b.y

zaurska
Starting Member

12 Posts

Posted - 2008-08-21 : 13:57:43

Perfect.

Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources