Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insert and update from table type list

Author  Topic 

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2012-11-19 : 14:44:05
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

808 Posts

Posted - 2012-11-19 : 15:22:47
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

298 Posts

Posted - 2012-11-19 : 15:38:48
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

298 Posts

Posted - 2012-11-19 : 15:52:56
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

298 Posts

Posted - 2012-11-19 : 16:29:58
Unfortunately, this is NOT working.....

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-19 : 16:58:36
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
   

- Advertisement -