SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insert and update from table type list
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zath
Constraint Violating Yak Guru

USA
282 Posts

Posted - 11/19/2012 :  14:44:05  Show Profile  Visit Zath's Homepage  Reply with Quote
Ran into a wall here and need some input.


--@my_Table_ids is a user defined table type that is a list of PK's

--This INSERT works just fine

INSERT INTO dbo.MyMainTable 
(MyMainField)
SELECT
MyMainField
FROM dbo.MyMainTable_TEMP WHERE MY_PK IN (SELECT id FROM @my_Table_ids)	

--Now, I need the new list of PK's that were created 
--above and update another table that which
--also contains the list @my_Table_ids

--NOT SURE ABOUT THIS UPDATE for the WHERE clause

UPDATE MyXTable SET 
MY_MyMainTable_FK = (SELECT MY_PK FROM dbo.MyMainTable WHERE) 
WHERE MyTempFK IN (SELECT id FROM @my_Table_ids)


The Update is what I think may work but need a second opinion.

Thanks!

Hommer
Aged Yak Warrior

782 Posts

Posted - 11/19/2012 :  15:22:47  Show Profile  Reply with Quote
are you looking for something like this?

UPDATE MyXTable

SET MyXTable.Field1 = MyMainTable. Field1
FROM
MyXTable INNER JOIN MyMainTable
ON MyXTable.Field2 = MyMainTable. Field2
WHERE ...
Go to Top of Page

Zath
Constraint Violating Yak Guru

USA
282 Posts

Posted - 11/19/2012 :  15:38:48  Show Profile  Visit Zath's Homepage  Reply with Quote
I really don't think that would do what I need.
I've been looking into OUTPUT and came up with this:

DECLARE @MyTableVar TABLE
(
	id BIGINT
);

INSERT INTO dbo.MyMainTable 
(MyMainField)
OUTPUT INSERTED.MY_PK INTO @MyTableVar
SELECT
MyMainField
FROM dbo.MyMainTable_TEMP WHERE MY_PK IN (SELECT id FROM @my_Table_ids)	

--BELOW WON"T WORK???????????

UPDATE MyXTable SET 
MY_MyMainTable_FK = (SELECT MY_PK FROM dbo.MyMainTable IN (SELECT id FROM @MyTableVar))
WHERE MyTempFK IN (SELECT id FROM @my_Table_ids)


I have a list of the new PK's and the original list of the other table's PK's, both in the perfect order that is needed.

Now to match them....

Suggestions?
Go to Top of Page

Zath
Constraint Violating Yak Guru

USA
282 Posts

Posted - 11/19/2012 :  15:52:56  Show Profile  Visit Zath's Homepage  Reply with Quote
Ok, I think I got it working. It should have been:

UPDATE MyXTable SET
MY_MyMainTable_FK = (SELECT MY_PK FROM dbo.MyMainTable WHERE MY_PK IN (SELECT id FROM @MyTableVar))
WHERE MyTempFK IN (SELECT id FROM @my_Table_ids)

Go to Top of Page

Zath
Constraint Violating Yak Guru

USA
282 Posts

Posted - 11/19/2012 :  16:29:58  Show Profile  Visit Zath's Homepage  Reply with Quote
Unfortunately, this is NOT working.....

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/19/2012 :  16:58:36  Show Profile  Reply with Quote
To save a lot of back and forth could you post DDL and DML with a few rows of sample data and expected results. Please make it runnable code so we don't have to type it all out.

I'm assuming your primary key is an identity column and you are having trouble correlating the temp ids with the actual (different) ones generated by the insert? but you didn't say that so I'm not sure.

Another "red flag" I heard is "both in the perfect order that is needed". Order in the table is irrelevant. You would use an ORDER BY clause in a SELECT statement to present the rows in whatever order you want. So not sure what you mean there.

But the DDL,DML, and expected results should answer all that.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000