Click here to monitor SSC
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
krushkoder
krushkoder
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 138
I have a need to INSERT some values into TableA, These values are SELECTed from TableC, during this whole process, I also need to take the inserted.ID, and a column value from TableC, and OUTPUT INTO TableB. Is this possible, and what am I doing wrong? (Sample Code Follows)


DECLARE @Today datetime
SET @Today = GETDATE()

INSERT TableA
(val1, val2, val3, entryDate)

OUTPUT
inserted.ID As TableAID,
C.comID As comID,
1 As RelationshipID,
'100.00' As xPercent,
1 As [status],
@Today As dateBegin
INTO TableB (TableAID, comID, RelationshipID, xPercent, [status], dateBegin)

SELECT val1, val2, val3, @Today As entryDate
FROM TableC C



Any help or guidance is greatly appreciated!

TIA,

KK

"If you don't have time to do it right, when will you have time to do it over?"
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9939 Visits: 14376
I would like to help...kindly post the DDL for your tables and DML to generate some useful test data.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
krushkoder
krushkoder
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 138
Here are the table structures for the example above:

TableA

ID int [PK,IDENTITY]
val1 varchar(10)
val2 varchar(10)
val3 varchar(10)
entryDate datetime


TableB

ID int [PK,IDENTITY]
TableAID int
comID int
RelationshipID int
xPercent varchar(6)
[status] int
dateBegin datetime

TableC

ID int [PK,IDENTITY]
val1 varchar(10)
val2 varchar(10)
val3 varchar(10)
entryDate datetime
comID int


TIA,

KK

"Life is shorter than expected." - Anon.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9939 Visits: 14376
Not ideal but I can work with that for DDL...can you please provide some test data?

Please have a look a this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
krushkoder
krushkoder
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 138
/*TABLE A*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[val1] [varchar](50) NOT NULL,
[val2] [varchar](50) NOT NULL,
[val3] [varchar](50) NOT NULL,
[entryDate] [datetime] NOT NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


/*TABLE B*/

SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableB](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TableAID] [int] NOT NULL,
[comID] [int] NOT NULL,
[RelationshipID] [int] NOT NULL,
[xPercent] [varchar](6) NOT NULL,
[status] [int] NOT NULL,
[dateBegin] [datetime] NOT NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


/*TABLE C*/

SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableC](
[ID] [int] IDENTITY(1,1) NOT NULL,
[val1] [varchar](50) NOT NULL,
[val2] [varchar](50) NOT NULL,
[val3] [varchar](50) NOT NULL,
[entryDate] [datetime] NOT NULL,
[comID] [int] NOT NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



INSERT INTO TableC
(val1, val2, val3, entrydate, comID)
SELECT 'val11', 'val21', 'val31', GETDATE(), 100 UNION ALL
SELECT 'val12', 'val22', 'val32', GETDATE(), 200 UNION ALL
SELECT 'val13', 'val23', 'val33', GETDATE(), 300 UNION ALL
SELECT 'val14', 'val24', 'val34', GETDATE(), 400 UNION ALL
SELECT 'val15', 'val25', 'val35', GETDATE(), 500



Here you go.

TIA,

KK
WayneS
WayneS
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7035 Visits: 10437
Let's see if I understand this correctly. If we take the information from your last post, and only do the inserts for TableC, you then want to insert all the information from TableC into the other two tables, with the results being the data from the insert statements that you provided for those two tables?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

krushkoder
krushkoder
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 138
Yes, You only need to populate TableC with the initial data.
The query I am attempting will populate the TableA and TableB
with the data from TableC.

Just to be clear. I want val1, val2, val3 & entryDate to be
INSERTed INTO TableA FROM TableC and preferably during the same step,
take the @@IDENTITY (aka Inserted.ID) from TableA, and the comID
from TableC, and INSERT those values INTO TableB

Thanks for the guidance.

KK
WayneS
WayneS
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7035 Visits: 10437
The only way you're going to be able to do this is if you add the comID column to TableA, insert into that, and use the INSERTED.comID to insert into TableB. The output clause will only see the columns actually being used in the table being inserted into ( @TableA ), and you aren't allowed to join back to TableC.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

krushkoder
krushkoder
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 138
Since I was unable to find any other examples of this all weekend, I had feared as much. Unfortunately, I can't add that column to that table or any other schema changes.

Since I can't do it in one step, what, IYEO (In Your Expert Opinion), would be the best way to approach it in two or more steps?

Should I put the values I need in a temp table and join to that?

I was really hoping the OUTPUT method would work! I actually thought about writing the value to a pseudo-comID colmun, meaning that we have some "deprecated" columns in this table that I could write the value to temporarily, then in a second step, write the value to TableB and then delete it from the unused column in TableA. Dirty, but it would work. Could get really messy though if anything failed along the way.

Any other solutions? Anyone, anyone... Beuuuuuuller...

TIA,

KK
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8191 Visits: 9566
My suggestion would be to create a temp table (including a TempID identity column) of all the data to be inserted/used from TableC
Insert into TableA from this temp table but with null/empty values (if allowed) to reserve ID values and capture the ID output to another temp table
Find the lowest ID from that table
Update TableA from the first temp table joining ID on TempID+(new lowest ID)-1
Make sure it all happens in one transaction


Far away is close at hand in the images of elsewhere.

Anon.


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