SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INSERT OUTPUT INTO (With an extra column) and SELECT statement


INSERT OUTPUT INTO (With an extra column) and SELECT statement

Author
Message
decates
decates
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
I know this is an old one, but I needed an answer to this and couldn't find one. Below is how I solved the problem, with the sample code addressing your original problem, for anybody else who wanders across this thread. It's not especially efficient (and would naturally be less-so with more columns in the tables) but 'works', and will play nicely in multi-user environment.


BEGIN TRANSACTION

DECLARE @Today DATETIME
SET @Today = GETDATE()

DECLARE @Temp TABLE
(
ID INT ,
[val1] [varchar](50) ,
[val2] [varchar](50) ,
[val3] [varchar](50) ,
[entryDate] [datetime]
)

INSERT INTO [dbo].[TableA]
( val1 ,
val2 ,
val3 ,
entryDate
)
OUTPUT INSERTED.ID ,
INSERTED.val1 ,
INSERTED.val2 ,
INSERTED.val3 ,
INSERTED.entryDate
INTO @Temp
SELECT val1 ,
val2 ,
val3 ,
entryDate
FROM [dbo].[TableC]

INSERT INTO [dbo].[TableB]
( TableAID ,
comID ,
RelationshipID ,
xPercent ,
[status] ,
dateBegin
)
SELECT T.ID AS TableAID ,
C.comID AS comID ,
1 AS RelationshipID ,
'100.00' AS xPercent ,
1 AS [status] ,
@Today AS dateBegin
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY val1 ASC, val2 ASC, val3 ASC, entryDate ASC ) AS RowNumber ,
ID
FROM @Temp
) AS T
INNER JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY val1 ASC, val2 ASC, val3 ASC, entryDate ASC ) AS RowNumber ,
comID
FROM [dbo].[TableC]
) AS C ON T.RowNumber = C.RowNumber

COMMIT TRANSACTION


EDIT:
Another, more efficient solution to this problem appears to be to use something like the following:

merge into [table1] as t
using [external_table] as s
on 1=0 --modify this predicate as necessary
when not matched then insert (data)
values (s.[col1])
output inserted.id, s.[col2] into [table2]
;

Vlad-207446
Vlad-207446
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 322
well this seams to work very well

"NOTE" that I am matching the records based on "sort of"
hash made up by concationations of same columns
from TableA and TableC
and comparing the resulting values




MERGE TableA AS ta
USING (SELECT c.val1 as val1, c.val2 as val2 , c.val3 as val3, c.comID as ComId FROM TableC C) AS tc
ON
[color=#ff0033]
REPLACE(COALESCE(ta.val1,'')+ COALESCE(ta.val2,'')+COALESCE(ta.val3,''),' ','')
=
REPLACE(COALESCE(tc.val1,'')+ COALESCE(tc.val2,'')+COALESCE(tc.val3,''),' ','')
[/color]
WHEN NOT MATCHED BY TARGET THEN
INSERT (val1, val2, val3,entryDate)
VALUES (tc.val1, tc.val2, tc.val3,@Today)
OUTPUT INSERTED.ID ,tc.ComId ,1 As RelationshipID
,'100.00' As xPercent, 1 As [status],@Today As dateBegin
INTO TableB (TableAID, comID, RelationshipID, xPercent, [status], dateBegin)
;

[color=#000][/color][highlight=#ffff11][/highlight][highlight=#ffff11][/highlight][color=#ff0033][/color][color=#ff0033][/color]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search