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 Friday, March 11, 2011 2:17 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 30, 2012 9:37 AM
Points: 51, 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?"
Post #1077170
Posted Friday, March 11, 2011 3:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1077201
Posted Friday, March 11, 2011 3:29 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 30, 2012 9:37 AM
Points: 51, 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.
Post #1077207
Posted Friday, March 11, 2011 3:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1077212
Posted Monday, March 14, 2011 7:39 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 30, 2012 9:37 AM
Points: 51, 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
Post #1077693
Posted Monday, March 14, 2011 8:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:40 PM
Points: 6,604, Visits: 8,909
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
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
Post #1077731
Posted Monday, March 14, 2011 8:29 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 30, 2012 9:37 AM
Points: 51, 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
Post #1077744
Posted Monday, March 14, 2011 8:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:40 PM
Points: 6,604, Visits: 8,909
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
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
Post #1077750
Posted Monday, March 14, 2011 8:51 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 30, 2012 9:37 AM
Points: 51, 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

Post #1077757
Posted Monday, March 14, 2011 9:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 7,120, Visits: 6,978
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.

Post #1077777
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse