TVP insertion

Posted on 2010-01-12
Medium Priority
Last Modified: 2012-05-08
i have a bunch of user-defined table TYPES, to which i am doing table-valued parm insertions.

suddenly the table def needs to change, and i need to put FK's on two values within the target tables, that are written to with these TVP's.

i can put FK's on the target tables, right?  they needn't go on the TYPE tables, too, do they?
what's the best way to handle that?
Question by:dbaSQL
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 57

Accepted Solution

Raja Jegan R earned 800 total points
ID: 26292646
You can't create Foreign Key constraints on a Type table.
You would be able to create just Primary and Unique Keys constraints on a type table and not Foreign Keys..

And may I know the exact thing why you went for a user defined table type instead of a table..
LVL 17

Author Comment

ID: 26292720
yes, i realize they can't go onto the TYPEs, but i'm just saying that's not really a big deal, and I'd put them onto the regular tables themselves.  right?

i didn't go for a table type instead of a table, i am inserting into a regular table via the table types, simply for massive data inserts.  i use the table-valued-parameter insert thru my table types, to do the large inserts in fewer round trips from client to server

i know i can put unique constraints or pk's onto the table types, but i'm just wondering... is it sufficient merely to put the FK's into place on the target table

going to try it out now
LVL 17

Author Comment

ID: 26293156
yep.  seems to work just fine.  though i answered my own inquiry, i will award and close.  thank you for looking

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question