Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Retrieving Identity Values When Inserting Multiple Rows

Suppose you have the following tables: create table Customers (CustomerID int identity primary key, CustomerName varchar(100) not null)create table AddressTypes (AddressType varchar(10) primary key)create table CustomerAddress (CustomerID int references Customers(CustomerID),AddressType varchar(10) references AddressTypes(AddressType),Street varchar(100),City varchar(100),State varchar(2),ZIP varchar(20),primary key (CustomerID, AddressType)) This is a simple schema for which a Customer can have multiple addresses, one per AddressType. Read more →