Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

INSERT OUTPUT INTO (With an extra column) and SELECT statement Expand / Collapse
Author
Message
Posted Tuesday, October 4, 2011 3:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 6, 2011 4:20 AM
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]
;
Post #1185126
Posted Friday, October 12, 2012 7:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 7:49 AM
Points: 36, Visits: 218
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]
Post #1372108
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse