Question Regarding Views and Indexes

If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.

Question Regarding Views and Indexes

If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.

question on upper bound primary key of type int

I have several tables in a deployed database in which the primary key is of type int, and autoincrements by 1 each time a record is added. My question is, since ints are 32-bit, what happens when its value reaches 4,294,967,296? I know that seems like an extrememly large amount of records, but when we imported the data into the database it started at key value 1,000,000. I don't know how to make it use lower numbers which are currently not being used (numbers below 1,000,000), and I am worried I will have problems when I reach the upper bound. What kind of problems could this cause? Should I change the primary key's type?

Thanks!

the upper bound is somewhere around 2.1 bill. Yes when you reach that limit your application will fail. You cannot insert any new data. You could put some alert in place to identify or predict when the storm is coming. you could create a job that gets the MAX(ID) every week and you can monitor the growth of the table. Once the ID reaches closer to 2 bil you can increase your frequency of monitoring. To fix it, you need modify the column and change it to BigInt. Please do not even bother to try ALTER TABLE...ALTER COLUMN...the server will hang.|||Ok, I'll just modify the primary key type. Thanks for the response.sql

question on how to form a query

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
>

question on how to form a query

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
>