Comparing Date ranges

I have two sets of dates to work with. One is an existing booking with a start and an end date. The other is a new booking with a start and an end date. I want to compare them and calculate how much overlap there is. If the overlap is over a certain amount (say 4 days), then I want to flag the user.

Is there any thing I can use in terms of a SQL query to assist in this comparison? I'm relatively new to SQL so I'm not entirely sure what functions and keywords are available to me to make this comparison.BOL (Books On Line) is the best source for some date functions..they also have sample codes..

hth|||You can you the DATEDIFF(datepart, datetime1,datetime2) function to get the difference between two dates.
Take a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp
for SQL Date Function.|||I actually ended up checking whether the start and end dates of each set were with in a certain range using a BETWEEN X AND Y syntax.

Compare tables layout in dev and prod

Hi,
Does anyone know that without the tool, how to compare table's layout those
are existing development and production servers?
Regards,
-Chen
Hi,
You could use INFORMATION_SCHEMA.COLUMNS Views.Put the contents into a table
and compare.
SQLCompare from Redgate software has got a very good tool for data and
schema comparison.
http://www.red-gate.com/sql_tools.htm
Thanks
Hari
SQL Server MVP
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:15706083-2F97-4811-B33A-4A1D6547A159@.microsoft.com...
> Hi,
> Does anyone know that without the tool, how to compare table's layout
> those
> are existing development and production servers?
> Regards,
> -Chen
|||Thank you very much!
"Hari Prasad" wrote:

> Hi,
> You could use INFORMATION_SCHEMA.COLUMNS Views.Put the contents into a table
> and compare.
> SQLCompare from Redgate software has got a very good tool for data and
> schema comparison.
> http://www.red-gate.com/sql_tools.htm
>
> Thanks
> Hari
> SQL Server MVP
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:15706083-2F97-4811-B33A-4A1D6547A159@.microsoft.com...
>
>

Compare tables layout in dev and prod

Hi,
Does anyone know that without the tool, how to compare table's layout those
are existing development and production servers?
Regards,
-ChenHi,
You could use INFORMATION_SCHEMA.COLUMNS Views.Put the contents into a table
and compare.
SQLCompare from Redgate software has got a very good tool for data and
schema comparison.
http://www.red-gate.com/sql_tools.htm
Thanks
Hari
SQL Server MVP
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:15706083-2F97-4811-B33A-4A1D6547A159@.microsoft.com...
> Hi,
> Does anyone know that without the tool, how to compare table's layout
> those
> are existing development and production servers?
> Regards,
> -Chen|||Thank you very much!
"Hari Prasad" wrote:
> Hi,
> You could use INFORMATION_SCHEMA.COLUMNS Views.Put the contents into a table
> and compare.
> SQLCompare from Redgate software has got a very good tool for data and
> schema comparison.
> http://www.red-gate.com/sql_tools.htm
>
> Thanks
> Hari
> SQL Server MVP
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:15706083-2F97-4811-B33A-4A1D6547A159@.microsoft.com...
> > Hi,
> >
> > Does anyone know that without the tool, how to compare table's layout
> > those
> > are existing development and production servers?
> >
> > Regards,
> > -Chen
>
>

Compare tables layout in dev and prod

Hi,
Does anyone know that without the tool, how to compare table's layout those
are existing development and production servers?
Regards,
-ChenHi,
You could use INFORMATION_SCHEMA.COLUMNS Views.Put the contents into a table
and compare.
SQLCompare from Redgate software has got a very good tool for data and
schema comparison.
http://www.red-gate.com/sql_tools.htm
Thanks
Hari
SQL Server MVP
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:15706083-2F97-4811-B33A-4A1D6547A159@.microsoft.com...
> Hi,
> Does anyone know that without the tool, how to compare table's layout
> those
> are existing development and production servers?
> Regards,
> -Chen|||Thank you very much!
"Hari Prasad" wrote:

> Hi,
> You could use INFORMATION_SCHEMA.COLUMNS Views.Put the contents into a tab
le
> and compare.
> SQLCompare from Redgate software has got a very good tool for data and
> schema comparison.
> http://www.red-gate.com/sql_tools.htm
>
> Thanks
> Hari
> SQL Server MVP
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:15706083-2F97-4811-B33A-4A1D6547A159@.microsoft.com...
>
>

Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!
Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQL...pare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegr oups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>
|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQ...mpare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegroups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQL_Data_Compare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegroups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Compare data in Tables

I am trying to determine the changes an application makes to a database.
The plan is to copy the existing schema (active) to a reference schema, run
the application and then diff the table data between the reference and the
a active schema. I have found one software vendor who has a tool to do
this, but it will only do one table at a time (interactively); I have more
then 300 and will run this a few times.

One other way of determining the changes, I guess, would be to log all sql
statements (in order), but I don't know how to do this (either).

Any pointers would be greatly appreciated.

LeoIf its changes you are looking for try running SQL Profiler against it.
Filter for where writes > 0. Another solution would to write a script to
doing the all tables comparison. Something like

Create a table with tablename, checksumbefore, checksumafter, rowsbefore,
rowsafter, numberofrowsdiff
Write a cursor of all user tables
For each table
Get count of rows with select count(*)
calc the CHECKSUM of each row and write to individual temp tables
select count(*) from checksumafter where checksum not in checksumbefore
insert/update the table

By the end of the script you should have indentified which tables change and
by how much.

"Leo" <leolist@.optushome.com.au> wrote in message
news:Xns9639579AFB3Bleolistoptushomecoma@.211.29.13 3.50...
>I am trying to determine the changes an application makes to a database.
> The plan is to copy the existing schema (active) to a reference schema,
> run
> the application and then diff the table data between the reference and the
> a active schema. I have found one software vendor who has a tool to do
> this, but it will only do one table at a time (interactively); I have more
> then 300 and will run this a few times.
> One other way of determining the changes, I guess, would be to log all sql
> statements (in order), but I don't know how to do this (either).
> Any pointers would be greatly appreciated.
> Leo|||There is a software tool that can do this for you called DB Ghost
(www.dbghost.com). Its very fast at comparing data and can be run from
the command line for a fully automated process. A single command will
do any number of tables that you desire.

It's also the cornerstone of a full change management solution for SQL
Server databases i.e. it can build, compare and synchronize the schema
AND data directly from drop/create scripts held in a source control
system.

I highly recommend you check it out.|||"Malcolm" <malcolm.leach@.innovartis.co.uk> wrote in
news:1113551521.457015.133450@.l41g2000cwc.googlegr oups.com:

> DB Ghost

DB Ghost did exactly what I needed.

Thanks for your advice

Leo

Compairing updated data base.

I do not know SQL but learning fast and furious.

I am programming an agent and working with a group of existing databases.
I would like to able to compare the database before and after an update.
The testing databases are relatively small.
I have no problem programming some compare but how do I go about it.

Should I do this in SQL duplicating the database.
I would be happy to write some SQL and dump the databases and do the compare
externally.

I would appreciate any suggestion.

AndreIf you just want to compare data between similar tables you can do so
with a JOIN:

SELECT COALESCE(A.key_col, B.key_col),
COALESCE(A.col1, B.col1), COALESCE(A.col2, B.col2), ...
FROM TableA AS A
FULL JOIN TableB AS B
ON A.key_col = B.key_col
WHERE COALESCE(A.col1,'')<>COALESCE(A.col1,'')
AND COALESCE(A.col2,'')<>COALESCE(A.col2,'')

assuming key_col is the primary key in both tables.

--
David Portas
SQL Server MVP
--|||What I would like to do is probably
1) back up the data base
2) restore it under a different name
-- run my agent
3) create a difference database ( a new database with any table which is
different)

Step 1 and 2 are easy so can be ignored
now step 3
I can create a new temporary database but how can I fill the tables in this
database using SQL

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111586542.235632.69940@.o13g2000cwo.googlegro ups.com...
> If you just want to compare data between similar tables you can do so
> with a JOIN:
> SELECT COALESCE(A.key_col, B.key_col),
> COALESCE(A.col1, B.col1), COALESCE(A.col2, B.col2), ...
> FROM TableA AS A
> FULL JOIN TableB AS B
> ON A.key_col = B.key_col
> WHERE COALESCE(A.col1,'')<>COALESCE(A.col1,'')
> AND COALESCE(A.col2,'')<>COALESCE(A.col2,'')
> assuming key_col is the primary key in both tables.
> --
> David Portas
> SQL Server MVP
> --|||Andre Arpin (arpin@.kingston.net) writes:
> What I would like to do is probably
> 1) back up the data base
> 2) restore it under a different name
> -- run my agent
> 3) create a difference database ( a new database with any table which is
> different)
> Step 1 and 2 are easy so can be ignored
> now step 3
> I can create a new temporary database but how can I fill the tables in
> this database using SQL

Red Gate has products for this, check out http://www.red-gate.com/.

If you would like to roll your own, you would have to write a query
like the one that David showed you for each table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You can easily populate a table from another in a different database:

INSERT INTO DatabaseA.dbo.TableA (col1, col2, ...)
SELECT col1, col2, ...
FROM DatabaseB.dbo.TableB
WHERE ... ?

--
David Portas
SQL Server MVP
--

Commit Transaction Gets Deleted - Unable to save SP

I've re-written a stored procedure and when I post the following code
into the existing SP in EM, is saves OK. However, when I re-edit the
SP, the last line 'Commit Transaction' has been removed.

I cannot save the remainder of the SP as it throws error 208 (Invalid
Object name #Max) about two of the temp tables I use when I post the
entire script. It shows in a message box with the header : 'Microsoft
SQL-DMO(ODBC SQLState:42S02)

I haven't posted the full SP nor the structure as it's quite large
(2000 lines), so hopefully I have given enough detail, but my questions
are :

Why does it now have problems with (temp) #Tables ? The use of these
has not changed. All I have done is wrap the script into various
transactions as this helps a lot for performance and tweaked a few
parts later in the SP again for performance.

Also, why does the line get removed once I save the SP ?

If I run this in QA, I get the same errors, so I suspect it's my
script, but don't know where I'm going wrong.

SQL2000 (Need to upgrade the service pack as recently installed on my
PC, so this may help)

Thanks in advance

Ryan

CREATE PROCEDURE [dbo].[JAG_Extract] (@.ExtractYear INTEGER,
@.ExtractMonth INTEGER) AS

BEGIN TRANSACTION

SELECT 0 AS MaxYear, 0 AS MaxMonth INTO #Max

UPDATE #Max SET MaxYear = @.ExtractYear
UPDATE #Max SET MaxMonth = @.ExtractMonth

PRINT 'Stage 1 - ' + Convert(VarChar, GetDate())
CREATE TABLE #Extract (
[DEALER_SOURCE_DATA_ID] INT,
[DSD_YEAR] INT NULL,
[DSD_MONTH] INT NULL,
[DEALER_CODE] VarChar(20),
[FranDealerCode] VarChar(20) NULL,
[Line_No] VarChar(75),
[Current] [numeric](15, 5) NULL,
[YTD] [numeric](15, 5) NULL,
[12Months] [numeric](15, 5) NULL,
[24Months] [numeric](15, 5) NULL,
[Average_YTD] [numeric](15, 5) NULL,
[Average12Months] [numeric](15, 5) NULL,
[Average24Months] [numeric](15, 5) NULL,
[Last_YTD] [numeric](15, 5) NULL,
[Current_STATUS] INT,
[PD1] [numeric](15, 5) NULL,
[PD2] [numeric](15, 5) NULL,
[PD3] [numeric](15, 5) NULL,
[PD4] [numeric](15, 5) NULL,
[PD5] [numeric](15, 5) NULL,
[PD6] [numeric](15, 5) NULL,
[PD7] [numeric](15, 5) NULL,
[PD8] [numeric](15, 5) NULL,
[PD9] [numeric](15, 5) NULL,
[PD10] [numeric](15, 5) NULL,
[PD11] [numeric](15, 5) NULL,
[PD12] [numeric](15, 5) NULL,
[PD13] [numeric](15, 5) NULL,
[PD14] [numeric](15, 5) NULL,
[PD15] [numeric](15, 5) NULL,
[PD16] [numeric](15, 5) NULL,
[PD17] [numeric](15, 5) NULL,
[PD18] [numeric](15, 5) NULL,
[PD19] [numeric](15, 5) NULL,
[PD20] [numeric](15, 5) NULL,
[PD21] [numeric](15, 5) NULL,
[PD22] [numeric](15, 5) NULL,
[PD23] [numeric](15, 5) NULL,
[PD24] [numeric](15, 5) NULL,
[PD25] [numeric](15, 5) NULL,
[PD26] [numeric](15, 5) NULL,
[PD27] [numeric](15, 5) NULL,
[PD28] [numeric](15, 5) NULL,
[PD29] [numeric](15, 5) NULL,
[PD30] [numeric](15, 5) NULL,
[PD31] [numeric](15, 5) NULL,
[PD32] [numeric](15, 5) NULL,
[PD33] [numeric](15, 5) NULL,
[PD34] [numeric](15, 5) NULL,
[PD35] [numeric](15, 5) NULL,
[PD36] [numeric](15, 5) NULL)

INSERT INTO #Extract

SELECT DISTINCT
SD.DEALER_SOURCE_DATA_ID,
SD.DSD_YEAR,
SD.DSD_MONTH,
DN.DEALER_CODE,
DN.FRAN_DEALER_CODE,
DV.FIELD_CODE,
0,
0,
0,
0,
0,
0,
0,
0,
SD.STATUS,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0

FROM
DEALER_NAW DN WITH (NOLOCK)
INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)
ON DN.DEALER_CODE = SD.DEALER_CODE
INNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)
ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_ID
AND SD.STATUS < 4096
INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)
ON DN.DEALER_CODE = GR.DEALER_CODE
AND GR.FIXED_GROUP_ID IN
(11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,
51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,
113,114,115,130,131,132,133,134,135,136,137)
GO
COMMIT TRANSACTIONYou need to remove 'GO' keyword.
Its a batch separator so you basically have two independent parts. Second
part is : COMMIT TRANSACTION.

MC

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1138113402.871088.270810@.g43g2000cwa.googlegr oups.com...
> I've re-written a stored procedure and when I post the following code
> into the existing SP in EM, is saves OK. However, when I re-edit the
> SP, the last line 'Commit Transaction' has been removed.
> I cannot save the remainder of the SP as it throws error 208 (Invalid
> Object name #Max) about two of the temp tables I use when I post the
> entire script. It shows in a message box with the header : 'Microsoft
> SQL-DMO(ODBC SQLState:42S02)
> I haven't posted the full SP nor the structure as it's quite large
> (2000 lines), so hopefully I have given enough detail, but my questions
> are :
> Why does it now have problems with (temp) #Tables ? The use of these
> has not changed. All I have done is wrap the script into various
> transactions as this helps a lot for performance and tweaked a few
> parts later in the SP again for performance.
> Also, why does the line get removed once I save the SP ?
> If I run this in QA, I get the same errors, so I suspect it's my
> script, but don't know where I'm going wrong.
> SQL2000 (Need to upgrade the service pack as recently installed on my
> PC, so this may help)
> Thanks in advance
>
> Ryan
> CREATE PROCEDURE [dbo].[JAG_Extract] (@.ExtractYear INTEGER,
> @.ExtractMonth INTEGER) AS
> BEGIN TRANSACTION
> SELECT 0 AS MaxYear, 0 AS MaxMonth INTO #Max
> UPDATE #Max SET MaxYear = @.ExtractYear
> UPDATE #Max SET MaxMonth = @.ExtractMonth
> PRINT 'Stage 1 - ' + Convert(VarChar, GetDate())
> CREATE TABLE #Extract (
> [DEALER_SOURCE_DATA_ID] INT,
> [DSD_YEAR] INT NULL,
> [DSD_MONTH] INT NULL,
> [DEALER_CODE] VarChar(20),
> [FranDealerCode] VarChar(20) NULL,
> [Line_No] VarChar(75),
> [Current] [numeric](15, 5) NULL,
> [YTD] [numeric](15, 5) NULL,
> [12Months] [numeric](15, 5) NULL,
> [24Months] [numeric](15, 5) NULL,
> [Average_YTD] [numeric](15, 5) NULL,
> [Average12Months] [numeric](15, 5) NULL,
> [Average24Months] [numeric](15, 5) NULL,
> [Last_YTD] [numeric](15, 5) NULL,
> [Current_STATUS] INT,
> [PD1] [numeric](15, 5) NULL,
> [PD2] [numeric](15, 5) NULL,
> [PD3] [numeric](15, 5) NULL,
> [PD4] [numeric](15, 5) NULL,
> [PD5] [numeric](15, 5) NULL,
> [PD6] [numeric](15, 5) NULL,
> [PD7] [numeric](15, 5) NULL,
> [PD8] [numeric](15, 5) NULL,
> [PD9] [numeric](15, 5) NULL,
> [PD10] [numeric](15, 5) NULL,
> [PD11] [numeric](15, 5) NULL,
> [PD12] [numeric](15, 5) NULL,
> [PD13] [numeric](15, 5) NULL,
> [PD14] [numeric](15, 5) NULL,
> [PD15] [numeric](15, 5) NULL,
> [PD16] [numeric](15, 5) NULL,
> [PD17] [numeric](15, 5) NULL,
> [PD18] [numeric](15, 5) NULL,
> [PD19] [numeric](15, 5) NULL,
> [PD20] [numeric](15, 5) NULL,
> [PD21] [numeric](15, 5) NULL,
> [PD22] [numeric](15, 5) NULL,
> [PD23] [numeric](15, 5) NULL,
> [PD24] [numeric](15, 5) NULL,
> [PD25] [numeric](15, 5) NULL,
> [PD26] [numeric](15, 5) NULL,
> [PD27] [numeric](15, 5) NULL,
> [PD28] [numeric](15, 5) NULL,
> [PD29] [numeric](15, 5) NULL,
> [PD30] [numeric](15, 5) NULL,
> [PD31] [numeric](15, 5) NULL,
> [PD32] [numeric](15, 5) NULL,
> [PD33] [numeric](15, 5) NULL,
> [PD34] [numeric](15, 5) NULL,
> [PD35] [numeric](15, 5) NULL,
> [PD36] [numeric](15, 5) NULL)
> INSERT INTO #Extract
> SELECT DISTINCT
> SD.DEALER_SOURCE_DATA_ID,
> SD.DSD_YEAR,
> SD.DSD_MONTH,
> DN.DEALER_CODE,
> DN.FRAN_DEALER_CODE,
> DV.FIELD_CODE,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> SD.STATUS,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0
> FROM
> DEALER_NAW DN WITH (NOLOCK)
> INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)
> ON DN.DEALER_CODE = SD.DEALER_CODE
> INNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)
> ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_ID
> AND SD.STATUS < 4096
> INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)
> ON DN.DEALER_CODE = GR.DEALER_CODE
> AND GR.FIXED_GROUP_ID IN
> (11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,
> 51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,
> 113,114,115,130,131,132,133,134,135,136,137)
> GO
> COMMIT TRANSACTION|||Sorted. Thanks for the pointer. I should have spotted that earlier.

Ryan

command to reference another table

Hi all,

I am new to this site and I hope anyone out there can help me. I was tasked to change the constraints of my existing table. Lets call it table1. This table has an attribute that needs to take the value of another attribute of another table ( let's call it tables2) and that attribute must satisfy a certain expression ( I suppose I can isolate it by using the select statement ).

Anyone know how to get this done.

Please advice.

Thanks and appreciated.Hi all,

I am new to this site and I hope anyone out there can help me. I was tasked to change the constraints of my existing table. Lets call it table1. This table has an attribute that needs to take the value of another attribute of another table ( let's call it tables2) and that attribute must satisfy a certain expression ( I suppose I can isolate it by using the select statement ).

Anyone know how to get this done.

Please advice.

Thanks and appreciated.
u have given very little information.Read the sticky (first post on this forum) first|||This sounds like homework to me. If it is, please post the URL for the assignment, or at least scan the page so we can see the "whole tamale" and solve the problem once instead of giving you what you asked for, but not what the teacher wants. If this is not homework, please see How to ask a question to get quick and correct answers? (http://www.dbforums.com/showthread.php?t=1212452#post4527530) in this forums FAQ.

-PatP|||Hi all,

I have yet to try this statement:-

alter table coursefee
add check ( course_code = course.course_code and
course.quota is not null );

coursefee :- table1
course_code :- PK of table1
course :- table2
course.course_code :- PK of table2
course.quota :- another attribute of table2

My intention is set a constraint on table1 where there will be an error if there is an insert on table2 whereby quota ( from table1) is null.

Is this plausible?

Sorry to trouble all again.|||I don't think you can reference another table in a CHECK CONSTRAINT. I suspect that you'll need to use a trigger if you really want to do this, but I wouldn't recommend doing it, for many reasons.

-PatP