I have a question on a query I hope it is not a dim question.
Table structure
Table name: Subscriptioncustomer
Fields: CustomerId - int , SubID - int, TypeId - smallint, date
- datetime, userid - varchar
Table Name: Customer
Fields: CustomerID - int, LastName- varchar. FirstName - varchar,
SSN - char
Table Name: Address
Field Name: AddressID-int, address - varchar
Table Name: Archived_Address
Field name: AddressID - int, CustomerID - int, TypeID - smallint,
active -bit
Question is I need to format the queries that will insert a new
customer for SubID 95122. Once you have the new CustomerID, tie the
customer to AddressID 854268 with another insertHi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data in a form that will be useful to someone replying to a message.
If you make your ID columns identity values then you could use the
SCOPE_IDENTITY() function to return the ID you have just inserted.
You description is not clear what the Archived_Address is for maybe this
should be the linking table for Customer and Address?
Ignoring Archive_address, then if you had the following table definitions
CREATE TABLE Customer (
CustomerID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Customer PRIMARY KEY
CLUSTERED,
LastName VARCHAR(128) NOT NULL,
FirstName VARCHAR(128) NOT NULL,
SSN CHAR(15) NULL )
CREATE TABLE Address (
AddressID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Address PRIMARY KEY
CLUSTERED,
address VARCHAR(500) NOT NULL )
CREATE TABLE Subscriptioncustomer (
CustomerId INT NOT NULL,
SubID INT NOT NULL,
TypeId SMALLINT NOT NULL,
[date] DATETIME NOT NULL CONSTRAINT DF_date DEFAULT GETDATE(),
userid VARCHAR(128) NOT NULL CONSTRAINT DF_userid DEFAULT USER_ID(),
CONSTRAINT PK_Subscriptioncustomer PRIMARY KEY CLUSTERED (CustomerId, SubID)
,
CONSTRAINT FK_Subscriptioncustomer_Customer FOREIGN KEY (CustomerID)
REFERENCES Customer (CustomerID)
)
CREATE TABLE Archived_Address (
AddressID INT NOT NULL,
CustomerID INT NOT NULL,
TypeID SMALLINT NOT NULL,
active bit,
CONSTRAINT PK_Archived_Address PRIMARY KEY CLUSTERED
(AddressID,CustomerID,TypeID ),
CONSTRAINT FK_Archived_Address_Customer FOREIGN KEY (CustomerID) REFERENCES
Customer (CustomerID),
CONSTRAINT FK_Archived_Address_Address FOREIGN KEY (AddressID) REFERENCES
Address (AddressID)
)
You could use the following procedure to add a new customer, address for
that customer and a subscription.
CREATE PROCEDURE spr_New_Customer_and_Subscription ( @.LastName VARCHAR(128),
@.FirstName VARCHAR(128),
@.SSN CHAR(15),
@.address VARCHAR(500),
@.SubID INT,
@.TypeId INT )
AS
BEGIN
SET NOCOUNT ON
DECLARE @.customerid int
DECLARE @.addressid int
DECLARE @.stat int
BEGIN TRANSACTION
INSERT INTO Customer ( LastName, FirstName, SSN )
VALUES ( @.LastName, @.FirstName, @.SSN )
SELECT @.customerid = SCOPE_IDENTITY(), @.stat = @.@.ERROR
-- Add Error Checking
INSERT INTO Address ( address )
VALUES (@.address)
SELECT @.addressid = SCOPE_IDENTITY(), @.stat = @.@.ERROR
-- Add Error Checking
INSERT INTO Subscriptioncustomer (CustomerId, SubID, TypeId )
VALUES (@.CustomerId, @.SubID, @.TypeId )
SET @.stat = @.@.ERROR
-- Add Error Checking
COMMIT TRANSACTION
-- Rollback if previous error
END
-- Test Run
EXEC dbo.spr_New_Customer_and_Subscription @.LastName = 'Bell',
@.FirstName = 'John',
@.SSN = 'N/A',
@.address = 'Somewhere',
@.SubID = 1,
@.TypeId = 1
-- Check it worked
SELECT * FROM Customer
SELECT * FROM Address
SELECT * FROM Subscriptioncustomer
If you want to pass an existing AddressID change the parameters to the
procedure and remove the insert into the Address table.
HTH
John
"shismith123@.gmail.com" wrote:
> I have a question on a query I hope it is not a dim question.
> Table structure
> Table name: Subscriptioncustomer
> Fields: CustomerId - int , SubID - int, TypeId - smallint, date
> - datetime, userid - varchar
> Table Name: Customer
> Fields: CustomerID - int, LastName- varchar. FirstName - varchar,
> SSN - char
> Table Name: Address
> Field Name: AddressID-int, address - varchar
> Table Name: Archived_Address
> Field name: AddressID - int, CustomerID - int, TypeID - smallint,
> active -bit
> Question is I need to format the queries that will insert a new
> customer for SubID 95122. Once you have the new CustomerID, tie the
> customer to AddressID 854268 with another insert
>|||Hi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data in a form that will be useful to someone replying to a message.
If you make your ID columns identity values then you could use the
SCOPE_IDENTITY() function to return the ID you have just inserted.
You description is not clear what the Archived_Address is for maybe this
should be the linking table for Customer and Address?
Ignoring Archive_address, then if you had the following table definitions
CREATE TABLE Customer (
CustomerID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Customer PRIMARY KEY
CLUSTERED,
LastName VARCHAR(128) NOT NULL,
FirstName VARCHAR(128) NOT NULL,
SSN CHAR(15) NULL )
CREATE TABLE Address (
AddressID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Address PRIMARY KEY
CLUSTERED,
address VARCHAR(500) NOT NULL )
CREATE TABLE Subscriptioncustomer (
CustomerId INT NOT NULL,
SubID INT NOT NULL,
TypeId SMALLINT NOT NULL,
[date] DATETIME NOT NULL CONSTRAINT DF_date DEFAULT GETDATE(),
userid VARCHAR(128) NOT NULL CONSTRAINT DF_userid DEFAULT USER_ID(),
CONSTRAINT PK_Subscriptioncustomer PRIMARY KEY CLUSTERED (CustomerId, SubID)
,
CONSTRAINT FK_Subscriptioncustomer_Customer FOREIGN KEY (CustomerID)
REFERENCES Customer (CustomerID)
)
CREATE TABLE Archived_Address (
AddressID INT NOT NULL,
CustomerID INT NOT NULL,
TypeID SMALLINT NOT NULL,
active bit,
CONSTRAINT PK_Archived_Address PRIMARY KEY CLUSTERED
(AddressID,CustomerID,TypeID ),
CONSTRAINT FK_Archived_Address_Customer FOREIGN KEY (CustomerID) REFERENCES
Customer (CustomerID),
CONSTRAINT FK_Archived_Address_Address FOREIGN KEY (AddressID) REFERENCES
Address (AddressID)
)
You could use the following procedure to add a new customer, address for
that customer and a subscription.
CREATE PROCEDURE spr_New_Customer_and_Subscription ( @.LastName VARCHAR(128),
@.FirstName VARCHAR(128),
@.SSN CHAR(15),
@.address VARCHAR(500),
@.SubID INT,
@.TypeId INT )
AS
BEGIN
SET NOCOUNT ON
DECLARE @.customerid int
DECLARE @.addressid int
DECLARE @.stat int
BEGIN TRANSACTION
INSERT INTO Customer ( LastName, FirstName, SSN )
VALUES ( @.LastName, @.FirstName, @.SSN )
SELECT @.customerid = SCOPE_IDENTITY(), @.stat = @.@.ERROR
-- Add Error Checking
INSERT INTO Address ( address )
VALUES (@.address)
SELECT @.addressid = SCOPE_IDENTITY(), @.stat = @.@.ERROR
-- Add Error Checking
INSERT INTO Subscriptioncustomer (CustomerId, SubID, TypeId )
VALUES (@.CustomerId, @.SubID, @.TypeId )
SET @.stat = @.@.ERROR
-- Add Error Checking
COMMIT TRANSACTION
-- Rollback if previous error
END
-- Test Run
EXEC dbo.spr_New_Customer_and_Subscription @.LastName = 'Bell',
@.FirstName = 'John',
@.SSN = 'N/A',
@.address = 'Somewhere',
@.SubID = 1,
@.TypeId = 1
-- Check it worked
SELECT * FROM Customer
SELECT * FROM Address
SELECT * FROM Subscriptioncustomer
If you want to pass an existing AddressID change the parameters to the
procedure and remove the insert into the Address table.
HTH
John
"shismith123@.gmail.com" wrote:
> I have a question on a query I hope it is not a dim question.
> Table structure
> Table name: Subscriptioncustomer
> Fields: CustomerId - int , SubID - int, TypeId - smallint, date
> - datetime, userid - varchar
> Table Name: Customer
> Fields: CustomerID - int, LastName- varchar. FirstName - varchar,
> SSN - char
> Table Name: Address
> Field Name: AddressID-int, address - varchar
> Table Name: Archived_Address
> Field name: AddressID - int, CustomerID - int, TypeID - smallint,
> active -bit
> Question is I need to format the queries that will insert a new
> customer for SubID 95122. Once you have the new CustomerID, tie the
> customer to AddressID 854268 with another insert
>
