March 11, 2011 at 2:17 pm
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?"
March 11, 2011 at 3:18 pm
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
March 11, 2011 at 3:29 pm
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.
March 11, 2011 at 3:50 pm
March 14, 2011 at 7:39 am
/*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
March 14, 2011 at 8:18 am
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
March 14, 2011 at 8:29 am
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
March 14, 2011 at 8:41 am
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
March 14, 2011 at 8:51 am
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
March 14, 2011 at 9:11 am
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.
October 4, 2011 at 3:47 am
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]
;
October 12, 2012 at 7:20 am
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
REPLACE(COALESCE(ta.val1,'')+ COALESCE(ta.val2,'')+COALESCE(ta.val3,''),' ','')
=
REPLACE(COALESCE(tc.val1,'')+ COALESCE(tc.val2,'')+COALESCE(tc.val3,''),' ','')
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)
;
[highlight=#ffff11][/highlight][highlight=#ffff11][/highlight]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply