sql server db: table

Showing posts with label table. Show all posts

Thursday, March 29, 2012

Correlated Subquery

Wonder if anyone can help

If I wanted 2 Values from a Correlated Subquery (same table join etc) would i have to have 2 subqueries or is there a better way to do it ?

ie.
SELECT t1.MyMainCode,
(SELECT SUM(t2.Qty) FROM t2 WHERE t2.MyMainCode = t1.MyMainCode) tQty,
(SELECT SUM(t2.Qty2) FROM t2 WHERE t2.MyMainCode = t1.MyMainCode) tQty2
FROM t1

Any Help appreciated

GWselect t1.MyMainCode
, sum(t2.Qty)
, sum(t2.Qty2)
from t1
left outer
join t2
on t1.MyMainCode = t2.MyMainCode
group
by t1.MyMainCode
rudy
http://r937.com/

Correlated Subquery

Can you use a correlated subquery in a join expression, when you reference
the table your joining to?
Hi Ben
An example of what you are trying to do would be useful! See
http://www.aspfaq.com/etiquette.asp?id=5006
I assume that you are soing something like:
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1 ) d where a.col1 = d.col1
and want to do
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE a.col1 = c.col1 ) d
Then the answer is no.
In SQL 2005 there is the CROSS APPLY operator where you could write your
derived table as a function
CREATE FUNCTION dbo.fn_derivedtbl(@.col1 AS INT) RETURNS TABLE
AS
RETURNS ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE c.col1 = @.col1 )
SELECT a.col1, a.col2, d.col1
FROM tablea a
CROSS APPLY dbo.fn_derivedtbl(a.col1) AS d
N.B None of the scripts are tested!
John
"Ben UK" wrote:

> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?
|||To my knowledge, no, you cannot. Correlated subqueries can be used only in
the Select and the Where statements.
However, if you make a new post explaining exactly what you are trying to
achieve, maybe someone will be able to provide you with an alternate
solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E2A87171-A1E4-4209-A056-E577B7A8326C@.microsoft.com...
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?

Correlated Subquery

Can you use a correlated subquery in a join expression, when you reference
the table your joining to?Hi Ben
An example of what you are trying to do would be useful! See
http://www.aspfaq.com/etiquette.asp?id=5006
I assume that you are soing something like:
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1 ) d where a.col1 = d.col1
and want to do
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE a.col1 = c.col1 ) d
Then the answer is no.
In SQL 2005 there is the CROSS APPLY operator where you could write your
derived table as a function
CREATE FUNCTION dbo.fn_derivedtbl(@.col1 AS INT) RETURNS TABLE
AS
RETURNS ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE c.col1 = @.col1 )
SELECT a.col1, a.col2, d.col1
FROM tablea a
CROSS APPLY dbo.fn_derivedtbl(a.col1) AS d
N.B None of the scripts are tested!
John
"Ben UK" wrote:

> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?|||To my knowledge, no, you cannot. Correlated subqueries can be used only in
the Select and the Where statements.
However, if you make a new post explaining exactly what you are trying to
achieve, maybe someone will be able to provide you with an alternate
solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E2A87171-A1E4-4209-A056-E577B7A8326C@.microsoft.com...
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?

Correlated and aggregate sql query

I have a table similar to the following:

-----------------------

Important - Each person can have more than one entry.

What I am trying to do is get the last percentage that each person obtained.

The only way I have been able to do this is by the following:

SELECT * FROM myTable

LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1

ON myTable.NameID = t1.NameID

WHERE myTable.PeriodTo = t1.PeriodTo

I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip

Big Smile

SELECT * FROM myTable where PeriodTo in (select Max(PeriodTo) from myTable group by NameID)

|||This does not work. It finds the rows where the periodTo is equivalent to the maximum periodTo. I need to find the maximum periodTofor each nameID. Its is equivalent toSELECT ID, NameID, ..., MAX(PeriodTo)FROM myTableGROUP BY ID, NameID, ...|||

You can use ROW_Number function if you use SQL Server 2005:

SELECT NameID, PeriodFrom, PeriodTo, PercentageFROM(SELECT NameID, PeriodFrom, PeriodTo, Percentage,ROW_Number()OVER(PARTITIONBY NameIDORDERBY PeriodToDESC)as RowNum

FROM mytable)AS t

WHERE RowNum=1

|||

Can you post some sample data from the table and expected output? This makes it easier for us to see what should be achieved.

|||

if PeriodTo unique then it works Huh?

(

SELECT * FROM myTable where PeriodTo in (select Max(PeriodTo) from myTable group by NameID)

in the inner query it selects all the periodto which is max to the nameid >>>then it matches the PeriodTo from the outer query.
select Max(PeriodTo) from myTable group by NameIDthis portion returns the Max PeriodTo for each NameID which is unique

)

|||

Sorry Kamrul, but that is not what I am looking for. Maybe I have not explained the problem properly, so here is some sample data (obviously simplified).

An example of the table is (note that this is british dates - i.e. dd/mm/yy):

 ID | NameID | PeriodTo | Comments |---------------------- 1 | Mark | 01/01/01 | ComMark1 | 2 | Mark | 01/06/01 | ComMark2 | 3 | Mark | 01/11/01 | ComMark3 | 4 | Ken | 01/01/01 | ComKen1 | 5 | Ken | 01/06/01 | ComKen2 | 6 | John | 01/01/01 | ComJohn1 |---------------------

And the result I am trying to get is :

 ID | NameID | PeriodTo | Comments |---------------------- 3 | Mark | 01/11/01 | ComMark3 | 5 | Ken | 01/06/01 | ComKen2 | 6 | John | 01/01/01 | ComJohn |----------------------

Kamrul's code doesn't work because it will return every row.

The code I wrote does return this, but I was wondering whether there was a nice way of doing this kind of correlated aggregated query. I'm looking for the most efficient method. I should also point out that I am using sql server 2000.

Jagdip

|||

Hi,

Please try this.

SELECT *FROM MyTable OutMyTable
where PeriodTo = (select Max(PeriodTo)from MyTable InMyTableWHERE InMyTable.NameID=OutMyTable.NameID group by NameID)

Regards,

Gaurang Majithiya

|||

For SQL Server 2000:

SELECT NameID, PeriodFrom, PeriodTo, PercentageFROM(

SELECT NameID, PeriodFrom, PeriodTo, Percentage,(SELECTCOUNT(*)FROM mytable aWHERE a.NameID=b.NameIDAND a.PeriodTo>=b.PeriodTo)as RowNum

FROM mytable b)AS t

WHERE RowNum=1

Correct way to create indices? SQLServer Express 2005

Hi,

Apologies if this has been asked before, i've done a search but can't find a definitive answer.

I've created a table in an SQLExpress 2005 db using Server Managment Studio Express.

My intention is to use GUID fields as surrogate PK's. I therefore wanted to add a additional index to prevent duplicate records being added to the table. Not having used SQLServer before could someone confirm or deny that this is the correct way to do this. The PK field [EPISODEID{unique identifier}] is set as a non-clustered index. And i've created a second clustered index using the two fields that create a unique record. I've added a screen shot if that is any help.

Thanks

Chris

You can do that, yes. But I would normally leave the PK as the clustered index, since that should be how the majority of your records are located. If it isn't then you need to sit down and think what purpose your surrogate key is supposed to provide.

|||

Motley wrote:
You can do that, yes. But I would normally leave the PK as the clustered index, since that should be how the majority of your records are located. If it isn't then you need to sit down and think what purpose your surrogate key is supposed to provide.

Thanks very much for your reply Smile [:)]

I've read up quite a lot of posts and articles regarding the use of surrogate vs natural keys. My primary reason for choosing surrogate keys is the inability to guarantee access to values which would create a natural key that didn't need changing. For example a head injured patient arrives at hospital and is assessed by the team i am working for. This is common as i work for critical care services which include Neurologically impared patients. They need a unique id for that patient but have no access to a possible natural key, the patients NHS number. In this case it's easier to generate a surrogate PK like a GUID which can be used on all the joins required in the database and use an incremental value from a store to replace the NHS number and keep the record unique until the NHS number can be assertained. Even then if the patient is non-uk resident they will never have an NHS number. However if the NHS number is found at a later date the PK can remain intact it's just a case of updating the NHS number field rather than cascading a changing PK through the database.

Anyways,

If i've got this correct the purpose of clustering an index is to create some form of sorting on sequentially related values which helps with retrieving records i.e. dates

If that's correct then as a surrogate key doesn't [or shouldn't] contain data relating to the contents of the record it indentifies it wouldn't matter if it is clustered or not. I think that this would particularly be the case with GUIDs as there is no logical sequence to their creation that would be worth clustering as compared to a sequential integer that might provide a pseudo order of entry index.

The second index i've created i've set as a clustered index as that is the one that holds the data which is used for retrieval i.e. finding all patient episodes within a given date range, so sorting would be beneficial and the unique index constraint should prevent duplication of records.

I've tried to follow the advice given on msdn regarding creating indicies i just wasn't sure if i'd actually done it correctly in Express05. Of course i may have missed the point entirely Embarrassed [:$]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8185.asp

Thanks again

Chris.

Correct way of finding a tables primary keys?

Hope this is in the right thread, sorry if not!

I have run into a problem, i need to find out that column(s) in a table that makes the primary key.

I thought that this code did the trick.

***

DECLARE @.c varchar(4000), @.t varchar(128)
SET @.c = ''
SET @.t='contact_pmc_contact_relations'
Select @.c = @.c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @.t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@.c, 1, Datalength(@.c) - 1)

***

This works in most of my cases. But i have encounterd tabels where this code doesn't work.

Here is a dump from one of the tabels where it doesn't work.

SELECT *
FROM sysindexkeys
WHERE (id = 933578364) <--id of the table

***

id indid colid keyno

933578364 1 1 1
933578364 1 2 2
933578364 2 1 1
933578364 3 2 1
933578364 4 3 1
933578364 5 4 1
933578364 6 5 1
933578364 7 6 1
933578364 8 7 1

Not sure if that dump made any sense, but i hope it did.

If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).

So, anyone know how i could solve this problem?

Regards

/Anders

to get the pk one of the best and recommended method will be

select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type='PRIMARY KEY'

Madhu

|||

SQL Server 2000 ou 2005 ?

|||Currently we'r using sql server 2000, but if there is a way that works on both 2000 and 2005 it would be really great!|||

Madhu K Nair wrote:

to get the pk one of the best and recommended method will be

select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type='PRIMARY KEY'

Madhu

Either im missunderstanding or that doesn't work. I want to get the columns that compose the primarykey. Not the primary key itself.

|||

i got the issue. Since there is no direct method (Single select statement), I will give u a workaround. If u check sp_helpconstraint , it give u the details. U can use sp_helptext to find the sourcecode behind this SP and do the required modification adn create u r own SP.

Madhu

|||

select * from INFORMATION_SCHEMA.[CONSTRAINT_COLUMN_USAGE]

where constraint_type='PRIMARY KEY'

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Is this you are looking for?

Code Snippet

SELECT CCU.TABLE_NAME

, CCU.COLUMN_NAME

, ORDINAL_POSITION

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

JOIN INFORMATION_SCHEMA.[KEY_COLUMN_USAGE] CCU

ON TC.TABLE_NAME = CCU.TABLE_NAME

AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

ORDER BY CCU.TABLE_NAME, ORDINAL_POSITION

Just expanding to what others meant to answer to your question.

Correct Table Structure - Optional Values

Hello,

I have 3 optional text boxes. I don't know if the best way to set up
the table would be a field for each box, since this would leave gaps in
the table if the user only filled in one box. Is there a good method
to use?? This is kind of like storing check box values, in that there
could be multiple answers.traceyburger@.sw.rr.com wrote:
> Hello,
> I have 3 optional text boxes. I don't know if the best way to set up
> the table would be a field for each box, since this would leave gaps in
> the table if the user only filled in one box. Is there a good method
> to use?? This is kind of like storing check box values, in that there
> could be multiple answers.

You don't give us much to go on but based off what you said, it sounds
as if the data that goes in these text boxes should be in their own table.

Zach|||(traceyburger@.sw.rr.com) writes:
> I have 3 optional text boxes. I don't know if the best way to set up
> the table would be a field for each box, since this would leave gaps in
> the table if the user only filled in one box. Is there a good method
> to use?? This is kind of like storing check box values, in that there
> could be multiple answers.

What do you mean with gaps? With this miniscule information, it sounds
to me that the columns mapping to these text boxes should be nullable.
Thus if a user only enters value in one box, you store NULL in the other
columns.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Tuesday, March 27, 2012

Correct SELECT Statement via joins

Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
GavThis is a multi-part message in MIME format.
--090105020707090300000908
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or current
supplier. Also the "LIKE '%something%'" is not a fantastic search
because it will require a complete index scan, ie. it will have to
search every single row in the index from start to end, which is not
terribly efficient.
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
>Hi all,
>I cannot quite get my head around these outer/inner joins (if this is what i
>need).
>Basically I have 2 tables ie
>TABLE ITEMS
>PRIMARYKEY ITEMNUMBER
>FOREIGNKEY LASTSUPPLIER
>FOREIGNKEY CURRENTSUPPLIER
>TABLE SUPPLIERS
>PRIMARYKEY SUPPLIERNUMBER
>SUPPLIERNAME
>Currently I can get the information I need by using this sort of statement:
>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to be
>able to something like:
>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>Could anyone help me with this please?
>Thanks
>Gav
>
>
--090105020707090300000908
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<blockquote><tt>SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME</tt> 
<tt>FROM ITEMS as i</tt> 
<tt> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER</tt> 
<tt> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =i.CURRENTSUPPLIER</tt> 
<tt>WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'</tt> 
</blockquote>
<tt> 
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or
current supplier. Also the "LIKE '%something%'" is not a fantastic
search because it will require a complete index scan, ie. it will have
to search every single row in the index from start to end, which is not
terribly efficient. 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Gav wrote:
<blockquote cite="midu0PpRd8ZFHA.2520@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
</pre>
</blockquote>
</body>
</html>
--090105020707090300000908--|||Gav wrote on Fri, 3 Jun 2005 00:03:20 +0100:
> Hi all,
> I cannot quite get my head around these outer/inner joins (if this is what
> i need).
> Basically I have 2 tables ie
> TABLE ITEMS
> PRIMARYKEY ITEMNUMBER
> FOREIGNKEY LASTSUPPLIER
> FOREIGNKEY CURRENTSUPPLIER
> TABLE SUPPLIERS
> PRIMARYKEY SUPPLIERNUMBER
> SUPPLIERNAME
> Currently I can get the information I need by using this sort of statement:
> SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER => ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM
> SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS
> CURRENTSUPPLIERNAME FROM ITEMS
> However this is not how I really want to do it... because I would like to
> be able to something like:
> as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
> however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
> Could anyone help me with this please?
LASTSUPPLIERNAME is not the column name, it's the derived table alias. You'd
need to use LASTSUPPLIERNAME.SUPPLIERNAME to reference the column.
That said, you also still need to work on the joins, see the post from Mike
Hodgson.
Dan|||This is a multi-part message in MIME format.
--=_NextPart_000_000C_01C56851.48FDF610
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks for the reply... however you say the 'LIKE' is not efficient, can =you suggest a more efficient way to do this?
Thanks again
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =3D =i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER =for a given ITEM row. If you can have nulls then you'd have to change =the INNER joins to OUTER joins (left outer joins) so that you still get =the ITEMNUMBER returned even if you have a NULL last supplier or current =supplier. Also the "LIKE '%something%'" is not a fantastic search =because it will require a complete index scan, ie. it will have to =search every single row in the index from start to end, which is not =terribly efficient.
HTH
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_000C_01C56851.48FDF610
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Thanks for the reply... however you say =the 'LIKE' is not efficient, can you suggest a more efficient way to do =this?
Thanks again
Gav
"Mike Hodgson" wrote in message news:%23VzsIQ9ZFHA.=2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAMEFROM ITEMS as =i INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER INNER JOIN SUPPLIERS =as c on c.SUPPLIERNUMBER =3D i.CURRENTSUPPLIERWHERE =l.SUPPLIERNAME LIKE '%MICROSOFT%'This is assuming you =can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If =you can have nulls then you'd have to change the INNER joins to OUTER joins =(left outer joins) so that you still get the ITEMNUMBER returned even if you =have a NULL last supplier or current supplier. Also the "LIKE ='%something%'" is not a fantastic search because it will require a complete index scan, =ie. it will have to search every single row in the index from start to end, =which is not terribly efficient.HTH
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav

--=_NextPart_000_000C_01C56851.48FDF610--|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C56854.400BBDA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =3D =i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER =for a given ITEM row. If you can have nulls then you'd have to change =the INNER joins to OUTER joins (left outer joins) so that you still get =the ITEMNUMBER returned even if you have a NULL last supplier or current =supplier. Also the "LIKE '%something%'" is not a fantastic search =because it will require a complete index scan, ie. it will have to =search every single row in the index from start to end, which is not =terribly efficient.
HTH
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_001D_01C56854.400BBDA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Just tried it out and performance aside =it's working great thanks
Gav
"Mike Hodgson" wrote in message news:%23VzsIQ9ZFHA.=2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAMEFROM ITEMS as =i INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER INNER JOIN SUPPLIERS =as c on c.SUPPLIERNUMBER =3D i.CURRENTSUPPLIERWHERE =l.SUPPLIERNAME LIKE '%MICROSOFT%'This is assuming you =can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If =you can have nulls then you'd have to change the INNER joins to OUTER joins =(left outer joins) so that you still get the ITEMNUMBER returned even if you =have a NULL last supplier or current supplier. Also the "LIKE ='%something%'" is not a fantastic search because it will require a complete index scan, =ie. it will have to search every single row in the index from start to end, =which is not terribly efficient.HTH
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav

--=_NextPart_000_001D_01C56854.400BBDA0--|||This is a multi-part message in MIME format.
--040807050503000601030701
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
There's nothing wrong with the LIKE operator per se. It's just when you
you compare it to a string that begins with '%' (such as '%MICROSOFT%'),
which basically means this data could start with anything and so could
be anywhere in the index so I'd better scan the entire index so we find
all occurrences. Just make the search more specific.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
> Thanks for the reply... however you say the 'LIKE' is not efficient,
> can you suggest a more efficient way to do this?
> Thanks again
> Gav
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
> SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
> FROM ITEMS as i
> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER => i.CURRENTSUPPLIER
> WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
>
> This is assuming you can't have a NULL LASTSUPPLIER or
> CURRENTSUPPLIER for a given ITEM row. If you can have nulls then
> you'd have to change the INNER joins to OUTER joins (left outer
> joins) so that you still get the ITEMNUMBER returned even if you
> have a NULL last supplier or current supplier. Also the "LIKE
> '%something%'" is not a fantastic search because it will require a
> complete index scan, ie. it will have to search every single row
> in the index from start to end, which is not terribly efficient.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Gav wrote:
>>Hi all,
>>I cannot quite get my head around these outer/inner joins (if this is what i
>>need).
>>Basically I have 2 tables ie
>>TABLE ITEMS
>>PRIMARYKEY ITEMNUMBER
>>FOREIGNKEY LASTSUPPLIER
>>FOREIGNKEY CURRENTSUPPLIER
>>TABLE SUPPLIERS
>>PRIMARYKEY SUPPLIERNUMBER
>>SUPPLIERNAME
>>Currently I can get the information I need by using this sort of statement:
>>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =>>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>>ITEMS
>>However this is not how I really want to do it... because I would like to be
>>able to something like:
>>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>>Could anyone help me with this please?
>>Thanks
>>Gav
>>
>>
--040807050503000601030701
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>There's nothing wrong with the LIKE operator per se. It's just
when you you compare it to a string that begins with '%' (such as
'%MICROSOFT%'), which basically means this data could start with
anything and so could be anywhere in the index so I'd better scan the
entire index so we find all occurrences. Just make the search more
specific.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Gav wrote:
<blockquote cite="miduaIEsjEaFHA.3384@.TK2MSFTNGP09.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2627" name="GENERATOR">
<style></style>
<div>Thanks for the reply... however you
say the 'LIKE' is not efficient, can you suggest a more efficient way
to do this?</div>
<div> </div>
<div>Thanks again</div>
<div>Gav</div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl">news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl</a>...</div>
<blockquote><tt>SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME</tt> 
<tt>FROM ITEMS as i</tt> 
<tt> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =i.LASTSUPPLIER</tt> 
<tt> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =i.CURRENTSUPPLIER</tt> 
<tt>WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'</tt> 
</blockquote>
<tt> 
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or
current supplier. Also the "LIKE '%something%'" is not a fantastic
search because it will require a complete index scan, ie. it will have
to search every single row in the index from start to end, which is not
terribly efficient. 
 
HTH 
</tt>
<div class="moz-signature">
-- 
mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907 
E <a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mailto:mike.hodgson@.mallesons.nospam.com</a>
| W <a href="http://links.10026.com/?link=http://www.mallesons.com</a>">http://www.mallesons.com">http://www.mallesons.com</a>

</div>
 
 
Gav wrote:
<blockquote cite="midu0PpRd8ZFHA.2520@.TK2MSFTNGP09.phx.gbl"
type="cite">
<pre wrap="">Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--040807050503000601030701--

Correct invalid SID

I migrated my SQL 2000 secuity from one NT Domain to another. In the process of changing the login names in the master..sysxlogins table, the SID did not get updated.

Is there an easy way to correct the SID entry without dropping and recreating each user?

Do you mean that you have manually changed the login names in sysxlogins and you wish to update the SID entries as well? Can you explain what you meant by "the process of changing the login names in the master..sysxlogins table"?

Thanks
Laurentiu

|||

I ran the following in the master db:

UPDATE sysxlogins

Set [name] = 'NEWDOMAIN\' + substring([name], patindex('%\%', [name])+1, 200)

WHERE [name] like 'OLDDOMAIN\%'

It changed all the login names to point to the new domain. The problem was the SIDs changed (which I didn't think about) in the new domain. Users can get in, but when we try to use the function suser_sid(), the correct network sid is being returned and we cant compare it to the one in sysxlogins or sysusers because it doesn't match.

|||

If you move from domain A to domain B, the Windows logins from domain A are normally invalidated. An exception to this would be if domain B was trusted by domain A, then you could still use the A logins even though the server runs in domain B.

This kind of domain change is not a supported operation. There is no supported solution for fixing this. It's not only the logins that you would need to fix, but all the database users as well. If you plan to change the domain for your server often, then you should use only SQL authentication.

Thanks
Laurentiu

sql

Correalated sub...

i have a historical tale with seven different columns in it. I would like to join to this table and get the latest data for a particular key in it, the problem is one row in the historical table may only have one updated column in which case i need a compilatoun of several different rows to make the 'latest' row.(i.e. one updated row may only update one field...) i think i know what i want to do code follows... but it doesnt work... any ideas?
SELECT
CTRP.CUSIP as CUSIP,
CTRP.PORTFOLIO_NAME as PORTFOLIO,
CF.FACTOR * TRCURORIGFACE as CUR_FACE,
CF.FACTOR as CUR_FACTOR,
CPA.PX_ASK as CUR_PX_ASK,
CPS.PX_SPD AS CUR_PX_SPD,
CPSD.PX_SPD_DT AS CUR_PX_SPD_DT,
DMB.DISC_MRGN_BID AS DISC_MRGN_BID,
CWC.WAL_CALL AS CUR_WAL_CALL,
CT.TRMODDUR AS CUR_TRMODDUR,
CPA.RECORD_DATE AS CPA_RT,
CPS.RECORD_DATE AS CPS_RT,
CPSD.RECORD_DATE AS CPSD_RT,
DMB.RECORD_DATE AS DMB_RT,
CMC.RECORD_DATE AS CMC_RT,
CWC.RECORD_DATE AS CWC_RT,
CT.RECORD_DATE AS CT_RT,
CF.RECORD_DATE AS CF_RT

FROM
(SELECT
DEAL_BOND_NAME AS CUSIP,
DEAL_BOND_ID,
sum(trcurorigface * case tran_deal_type_name when 'Buy' then 1 when 'Sell' then -1 end) AS trcurorigface,
PORTFOLIO_NAME
FROM
VIEW_TRAN_DEAL_BOND
LEFT OUTER JOIN DEAL_BOND DB ON DB.ID = VIEW_TRAN_DEAL_BOND.DEAL_BOND_ID
WHERE
hedge = 0 OR hedge is null
GROUP BY
PORTFOLIO_NAME,
DEAL_BOND_NAME,
DEAL_BOND_ID) CTRP
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CF ON CF.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPA ON CPA.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPS ON CPS.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CPSD ON CPSD.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY DMB ON DMB.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CMC ON CMC.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CWC ON CWC.ID = CTRP.DEAL_BOND_ID
RIGHT OUTER JOIN VIEW_DEAL_BOND_HISTORY CT ON CT.ID = CTRP.DEAL_BOND_ID
WHERE
CPA.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH1
WHERE PX_ASK IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH1.DEAL_BOND_ID) and
CF.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH2
WHERE FACTOR IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH2.DEAL_BOND_ID) and
CPS.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH3
WHERE PX_SPD IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH3.DEAL_BOND_ID) and
CPSD.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH4
WHERE PX_SPD_DT IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH4.DEAL_BOND_ID) and
DMB.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH5
WHERE DISC_MRGN_BID IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH5.DEAL_BOND_ID) and
CWC.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH6
WHERE WAL_CALL IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH6.DEAL_BOND_ID) and
CT.RECORD_DATE = (SELECT MAX(RECORD_DATE)
FROM VIEW_DEAL_BOND_HISTORY VDBH7
WHERE TRMODDUR IS NOT NULL AND
CTRP.DEAL_BOND_ID = VDBH7.DEAL_BOND_ID)

You should be able to create sub-queries that select the latest data for each of the seven fields by the key and join those seven via the key using a where clause in each of the sub-queries to filter out where your respective field is not NULL. In the sub-queries, be sure to order by the record date and only select top 1. An extremely simplified example follows:

select
t.Key,
t1.Field1,
t2.Field2
from
Table t
inner join
(
select top 1
t.Field1
from
Table t
where
t.Key = <Parameter Value>
and Field1 is not null
order by
t.RecordDate
) t1
on
t1.Key = t.Key
inner join
(
select top 1
t.Field2
from
Table t
where
t.Key = <Parameter Value>
and t.Field2 is not null
order by
t.RecordDate
) t2
on t2.Key = t.Key
where
t.Key = <Parameter Value>

I don't think your example is working because you're asking for a certain row where the Record_Date field is equal to (possibly) seven different values at the same time, which is impossible.

Copying/ Deleting Huge Data from a table

Hi,
I have a problem about "userlogs" table . this table contains some
information about users actions .The table has nearly 800.000.000 record in
it and ~100Gb data , 50GB index.
i have tried to copy some data ( like 1M records) to a temporary database, i
couldnt manage it
This is the query , the table has a index contains only "id"
declare @.start_index int, @.end_index int
set @.start_index = 800000050
set @.end_index = 800000250
insert into userlogsbackup select * from userlogs where id > @.start_index
and id < @.end_index
but the select fails unless specifying "top 10" for example. What else can
i try ?
Thanks in advanceIf the index is not clustered then it can't do a range scan without also
doing bookmark lookups for each row found. So the more rows you will touch
the less the chances of using the index. But in your case the optimizer does
not know how many rows will be affected since it does not know the values of
the two variables. As such it guesses and the guess for a range is quite a
large percentage (can't remember off hand) which will negate the use of an
index s

. Do you get the proper plan if you hard code the two values?
You can try creating a stored procedure that has the two values as
parameters. That way the optimizer will use the actual values passed the
first time to create the plan. Does it work with a TOP 250?
Andrew J. Kelly SQL MVP
"Hasan O." <hozavalsiz@.gmail.com> wrote in message
news:esQBHkKgGHA.1264@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a problem about "userlogs" table . this table contains some
> information about users actions .The table has nearly 800.000.000 record
> in it and ~100Gb data , 50GB index.
> i have tried to copy some data ( like 1M records) to a temporary database,
> i couldnt manage it
> This is the query , the table has a index contains only "id"
> declare @.start_index int, @.end_index int
> set @.start_index = 800000050
> set @.end_index = 800000250
>
> insert into userlogsbackup select * from userlogs where id >
> @.start_index and id < @.end_index
> but the select fails unless specifying "top 10" for example. What else
> can i try ?
> Thanks in advance
>|||Hi Andrew ,
thanks for your answer.
yes index is not clustered . it doesnt work with top 70 .
I think i am gonna write while loop for selecting , and inserting one by one
like this
while @.start_index and < @.end_index
begin
1
2 insert into userlogsbackup select * from userlogs where id =
@.start_index
3 delete from userlogs where id = @.start_index
4 set @.start_index = @.start_index + 1
5
end
i am also thinking to use "begin transaction and commit transaction" . Can
you tell me if which lines transactions contains
thanks again
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uaZyrgLgGHA.4940@.TK2MSFTNGP05.phx.gbl...
> If the index is not clustered then it can't do a range scan without also
> doing bookmark lookups for each row found. So the more rows you will touch
> the less the chances of using the index. But in your case the optimizer
> does not know how many rows will be affected since it does not know the
> values of the two variables. As such it guesses and the guess for a range
> is quite a large percentage (can't remember off hand) which will negate
> the use of an index s

. Do you get the proper plan if you hard code the
> two values? You can try creating a stored procedure that has the two
> values as parameters. That way the optimizer will use the actual values
> passed the first time to create the plan. Does it work with a TOP 250?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hasan O." <hozavalsiz@.gmail.com> wrote in message
> news:esQBHkKgGHA.1264@.TK2MSFTNGP05.phx.gbl...
>|||Maybe if you post the actual DDL for the table including all the indexes and
tell us exactly what you are tyring to accomplish we can suggest something
better. Do you want to move any 1 million rows or a specific set? Is this a
one time thing or will it be repeated? Can it be done in off hours?
Andrew J. Kelly SQL MVP
"Hasan O." <hozavalsiz@.gmail.com> wrote in message
news:eJs5eNMgGHA.1264@.TK2MSFTNGP05.phx.gbl...
> Hi Andrew ,
> thanks for your answer.
> yes index is not clustered . it doesnt work with top 70 .
> I think i am gonna write while loop for selecting , and inserting one by
> one like this
> while @.start_index and < @.end_index
> begin
> 1
> 2 insert into userlogsbackup select * from userlogs where id =
> @.start_index
> 3 delete from userlogs where id = @.start_index
> 4 set @.start_index = @.start_index + 1
> 5
> end
> i am also thinking to use "begin transaction and commit transaction" . Can
> you tell me if which lines transactions contains
> thanks again
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uaZyrgLgGHA.4940@.TK2MSFTNGP05.phx.gbl...
>|||>but the select fails unless specifying "top 10" for example.
What is the exact nature of the failure? What error code and message
is returned? Or is it simply taking a long time?
Roy Harvey
Beacon Falls, CT
On Fri, 26 May 2006 12:44:41 +0300, "Hasan O." <hozavalsiz@.gmail.com>
wrote:

>Hi,
>I have a problem about "userlogs" table . this table contains some
>information about users actions .The table has nearly 800.000.000 record in
>it and ~100Gb data , 50GB index.
>i have tried to copy some data ( like 1M records) to a temporary database,
i
>couldnt manage it
>This is the query , the table has a index contains only "id"
>declare @.start_index int, @.end_index int
>set @.start_index = 800000050
>set @.end_index = 800000250
>
>insert into userlogsbackup select * from userlogs where id > @.start_index
>and id < @.end_index
>but the select fails unless specifying "top 10" for example. What else can
>i try ?
>Thanks in advance
>|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:cu3e72d978qc99vfo1mclnqhudtgh31r71@.
4ax.com...
> What is the exact nature of the failure? What error code and message
> is returned? Or is it simply taking a long time?
taking too much time so i cancel the query.
i am using one by one move operation now .
thanks for the answer.

Copying Views

Is there an easy way to copy all views from one database to another? Using
the import/export wizard tries to copy the data in a View to a Table in the
destination table. I don't want to create new tables just copy the view
object over (the databases are equivalent, one production, one for testing).
I use Views for pre-defined queries often used in the production
applications to give the end-user views of the data that makes more sense.
Am I using Views wrong?
By the way I have found a work around by right clicking on the view and go
to Script As -> CREATE TO -> New Query. Then change the first line from:
USE [SourceDatabaseName]
to
USE [DestinationDatabaseName]
This process is unyieldy though because I have to repeat the multi-step
process for every view I want to copy.
Thanks,
RyanRyan
You can use SQLDMO object library to script out all views and then run it on
destination server
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
Const SQLDMOScript2_NoCollation As Long = 8388608
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Views, ignoring system views and informational schemas
For Each genObj In db.Views
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next
MsgBox "Finished generating SQL scripts."
End Sub
--usage
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
> Is there an easy way to copy all views from one database to another?
> Using the import/export wizard tries to copy the data in a View to a Table
> in the destination table. I don't want to create new tables just copy the
> view object over (the databases are equivalent, one production, one for
> testing). I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>|||Ryan wrote:
> Is there an easy way to copy all views from one database to another? Usin
g
> the import/export wizard tries to copy the data in a View to a Table in th
e
> destination table. I don't want to create new tables just copy the view
> object over (the databases are equivalent, one production, one for testing
).
> I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>
I wouldn't say you're "using" them wrong, I'd say you're "creating" them
wrong. The scripting method that you discovered is not a workaround,
it's the accepted method of working with database objects. You should
be creating your views with a script, and archiving that script in a
version control system, like Visual SourceSafe or Perforce. This allows
you to track the revision history of your objects, gives you a
"rollback" method for undo-ing changes, AND it makes your objects more
portable. To "copy" and object to a new location, you simply run the
CREATE script again, in the new location.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zxv31XOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Ryan
> You can use SQLDMO object library to script out all views and then run it
> on destination server
>
> Sub ScriptDB(strLogin As String, strPwd As String, _
> strDataBase As String, StrFilePath As String)
> Dim sql As Object
> Dim db As Object
> Dim objTrigger As Object
> Dim intOptions As Long
> Dim genObj
> Set sql = CreateObject("SQLDMO.SQLServer")
> Set db = CreateObject("SQLDMO.Database")
> Set objTrigger = CreateObject("SQLDMO.Trigger")
> Const sDrops As Integer = 1
> Const sIncludeHeaders As Long = 131072
> Const sDefault As Integer = 4
> Const sAppendToFile As Integer = 256
> Const sBindings As Integer = 128
> Const SQLDMOScript2_NoCollation As Long = 8388608
> ' Set scripting options. Because you need to specify multiple behaviors
> ' for the ScriptType argument, you use "Or" to combine these.
> intOptions = sDrops Or sIncludeHeaders Or _
> sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
> ' Connect to local server
> sql.Connect "(local)", strLogin, strPwd
> Set db = sql.Databases(strDataBase, "dbo")
>
> ' Script Views, ignoring system views and informational schemas
> For Each genObj In db.Views
> If genObj.SystemObject = False Then
> genObj.Script intOptions, StrFilePath
> End If
> Next
> MsgBox "Finished generating SQL scripts."
> End Sub
> --usage
> Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
>|||Sure, that makes sense. The thing is this isn't a usual process. I am
needing to create a non-production (test) database off of the production
database so I thought there would be a way to simply copy the entire
database (including objects such as views, stored procedures, etc) in one
fell swoop. I guess another option would be to detach the production
database and make a copy of the db files before reattaching it. The only
downside to this is downtime on the production side. Thanks for the info.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACE89E.2020400@.realsqlguy.com...
> Ryan wrote:
> I wouldn't say you're "using" them wrong, I'd say you're "creating" them
> wrong. The scripting method that you discovered is not a workaround, it's
> the accepted method of working with database objects. You should be
> creating your views with a script, and archiving that script in a version
> control system, like Visual SourceSafe or Perforce. This allows you to
> track the revision history of your objects, gives you a "rollback" method
> for undo-ing changes, AND it makes your objects more portable. To "copy"
> and object to a new location, you simply run the CREATE script again, in
> the new location.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Sure, that makes sense. The thing is this isn't a usual process. I am
> needing to create a non-production (test) database off of the production
> database so I thought there would be a way to simply copy the entire
> database (including objects such as views, stored procedures, etc) in one
> fell swoop. I guess another option would be to detach the production
> database and make a copy of the db files before reattaching it. The only
> downside to this is downtime on the production side. Thanks for the info.
>
Why not just do a backup of the production DB, and restore that onto
your Dev server?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Because the database structure doesn't exist on the Dev server. To do a
restore doesn't the database structure have to already exist? At least I
couldn't find a way to do it otherwise. I just did a detach, copy file,
reattach to both the dev and production server. This seems to be the
easiest solution.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD3AEC.3080907@.realsqlguy.com...
> Ryan wrote:
> Why not just do a backup of the production DB, and restore that onto your
> Dev server?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Because the database structure doesn't exist on the Dev server. To do a
> restore doesn't the database structure have to already exist? At least I
> couldn't find a way to do it otherwise. I just did a detach, copy file,
> reattach to both the dev and production server. This seems to be the
> easiest solution.
>
Nope, definately not true. You can restore a backup, give it a new
database name, and it will create the database "on the fly".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ah.. ok thanks for the info. I will definitely use this for future
solutions.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE2D47.8030504@.realsqlguy.com...
> Ryan wrote:
> Nope, definately not true. You can restore a backup, give it a new
> database name, and it will create the database "on the fly".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Sunday, March 25, 2012

Copying Views

Is there an easy way to copy all views from one database to another? Using
the import/export wizard tries to copy the data in a View to a Table in the
destination table. I don't want to create new tables just copy the view
object over (the databases are equivalent, one production, one for testing).
I use Views for pre-defined queries often used in the production
applications to give the end-user views of the data that makes more sense.
Am I using Views wrong?
By the way I have found a work around by right clicking on the view and go
to Script As -> CREATE TO -> New Query. Then change the first line from:
USE [SourceDatabaseName]
to
USE [DestinationDatabaseName]
This process is unyieldy though because I have to repeat the multi-step
process for every view I want to copy.
Thanks,
RyanRyan
You can use SQLDMO object library to script out all views and then run it on
destination server
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
Const SQLDMOScript2_NoCollation As Long = 8388608
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Views, ignoring system views and informational schemas
For Each genObj In db.Views
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next
MsgBox "Finished generating SQL scripts."
End Sub
--usage
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
> Is there an easy way to copy all views from one database to another?
> Using the import/export wizard tries to copy the data in a View to a Table
> in the destination table. I don't want to create new tables just copy the
> view object over (the databases are equivalent, one production, one for
> testing). I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>|||Ryan wrote:
> Is there an easy way to copy all views from one database to another? Using
> the import/export wizard tries to copy the data in a View to a Table in the
> destination table. I don't want to create new tables just copy the view
> object over (the databases are equivalent, one production, one for testing).
> I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>
I wouldn't say you're "using" them wrong, I'd say you're "creating" them
wrong. The scripting method that you discovered is not a workaround,
it's the accepted method of working with database objects. You should
be creating your views with a script, and archiving that script in a
version control system, like Visual SourceSafe or Perforce. This allows
you to track the revision history of your objects, gives you a
"rollback" method for undo-ing changes, AND it makes your objects more
portable. To "copy" and object to a new location, you simply run the
CREATE script again, in the new location.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zxv31XOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Ryan
> You can use SQLDMO object library to script out all views and then run it
> on destination server
>
> Sub ScriptDB(strLogin As String, strPwd As String, _
> strDataBase As String, StrFilePath As String)
> Dim sql As Object
> Dim db As Object
> Dim objTrigger As Object
> Dim intOptions As Long
> Dim genObj
> Set sql = CreateObject("SQLDMO.SQLServer")
> Set db = CreateObject("SQLDMO.Database")
> Set objTrigger = CreateObject("SQLDMO.Trigger")
> Const sDrops As Integer = 1
> Const sIncludeHeaders As Long = 131072
> Const sDefault As Integer = 4
> Const sAppendToFile As Integer = 256
> Const sBindings As Integer = 128
> Const SQLDMOScript2_NoCollation As Long = 8388608
> ' Set scripting options. Because you need to specify multiple behaviors
> ' for the ScriptType argument, you use "Or" to combine these.
> intOptions = sDrops Or sIncludeHeaders Or _
> sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
> ' Connect to local server
> sql.Connect "(local)", strLogin, strPwd
> Set db = sql.Databases(strDataBase, "dbo")
>
> ' Script Views, ignoring system views and informational schemas
> For Each genObj In db.Views
> If genObj.SystemObject = False Then
> genObj.Script intOptions, StrFilePath
> End If
> Next
> MsgBox "Finished generating SQL scripts."
> End Sub
> --usage
> Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
>> Is there an easy way to copy all views from one database to another?
>> Using the import/export wizard tries to copy the data in a View to a
>> Table in the destination table. I don't want to create new tables just
>> copy the view object over (the databases are equivalent, one production,
>> one for testing). I use Views for pre-defined queries often used in the
>> production applications to give the end-user views of the data that makes
>> more sense. Am I using Views wrong?
>> By the way I have found a work around by right clicking on the view and
>> go to Script As -> CREATE TO -> New Query. Then change the first line
>> from:
>> USE [SourceDatabaseName]
>> to
>> USE [DestinationDatabaseName]
>> This process is unyieldy though because I have to repeat the multi-step
>> process for every view I want to copy.
>> Thanks,
>> Ryan
>|||Sure, that makes sense. The thing is this isn't a usual process. I am
needing to create a non-production (test) database off of the production
database so I thought there would be a way to simply copy the entire
database (including objects such as views, stored procedures, etc) in one
fell swoop. I guess another option would be to detach the production
database and make a copy of the db files before reattaching it. The only
downside to this is downtime on the production side. Thanks for the info.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACE89E.2020400@.realsqlguy.com...
> Ryan wrote:
>> Is there an easy way to copy all views from one database to another?
>> Using the import/export wizard tries to copy the data in a View to a
>> Table in the destination table. I don't want to create new tables just
>> copy the view object over (the databases are equivalent, one production,
>> one for testing). I use Views for pre-defined queries often used in the
>> production applications to give the end-user views of the data that makes
>> more sense. Am I using Views wrong?
>> By the way I have found a work around by right clicking on the view and
>> go to Script As -> CREATE TO -> New Query. Then change the first line
>> from:
>> USE [SourceDatabaseName]
>> to
>> USE [DestinationDatabaseName]
>> This process is unyieldy though because I have to repeat the multi-step
>> process for every view I want to copy.
>> Thanks,
>> Ryan
> I wouldn't say you're "using" them wrong, I'd say you're "creating" them
> wrong. The scripting method that you discovered is not a workaround, it's
> the accepted method of working with database objects. You should be
> creating your views with a script, and archiving that script in a version
> control system, like Visual SourceSafe or Perforce. This allows you to
> track the revision history of your objects, gives you a "rollback" method
> for undo-ing changes, AND it makes your objects more portable. To "copy"
> and object to a new location, you simply run the CREATE script again, in
> the new location.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Sure, that makes sense. The thing is this isn't a usual process. I am
> needing to create a non-production (test) database off of the production
> database so I thought there would be a way to simply copy the entire
> database (including objects such as views, stored procedures, etc) in one
> fell swoop. I guess another option would be to detach the production
> database and make a copy of the db files before reattaching it. The only
> downside to this is downtime on the production side. Thanks for the info.
>
Why not just do a backup of the production DB, and restore that onto
your Dev server?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Because the database structure doesn't exist on the Dev server. To do a
restore doesn't the database structure have to already exist? At least I
couldn't find a way to do it otherwise. I just did a detach, copy file,
reattach to both the dev and production server. This seems to be the
easiest solution.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD3AEC.3080907@.realsqlguy.com...
> Ryan wrote:
>> Sure, that makes sense. The thing is this isn't a usual process. I am
>> needing to create a non-production (test) database off of the production
>> database so I thought there would be a way to simply copy the entire
>> database (including objects such as views, stored procedures, etc) in one
>> fell swoop. I guess another option would be to detach the production
>> database and make a copy of the db files before reattaching it. The only
>> downside to this is downtime on the production side. Thanks for the
>> info.
> Why not just do a backup of the production DB, and restore that onto your
> Dev server?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Because the database structure doesn't exist on the Dev server. To do a
> restore doesn't the database structure have to already exist? At least I
> couldn't find a way to do it otherwise. I just did a detach, copy file,
> reattach to both the dev and production server. This seems to be the
> easiest solution.
>
Nope, definately not true. You can restore a backup, give it a new
database name, and it will create the database "on the fly".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ah.. ok thanks for the info. I will definitely use this for future
solutions.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE2D47.8030504@.realsqlguy.com...
> Ryan wrote:
>> Because the database structure doesn't exist on the Dev server. To do a
>> restore doesn't the database structure have to already exist? At least I
>> couldn't find a way to do it otherwise. I just did a detach, copy file,
>> reattach to both the dev and production server. This seems to be the
>> easiest solution.
> Nope, definately not true. You can restore a backup, give it a new
> database name, and it will create the database "on the fly".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copying Text Field

I am refreshing records on a target table from a similar source table (two
databases, same server) and having problems with the Text fields. I am usin
g
INSERTas follows:
delete from Trackpad4..Archived
set identity_insert Trackpad4..Archived on
insert into Trackpad4..Archived
(... DEL_RECIP_NAME, DEL_RECIP_LEN, DEL_RECIP_SIG, ...)
select ... DEL_RECIP_NAME, DEL_RECIP_LEN, null, ...
from Trackpad..Archived
set identity_insert Trackpad4..Archived off
The problem we noticed (and why I've got null populating in DEL_RECIP_SIG)
is that a simple SELECT would return three rows but a rowcount of four. A
modification of the query picked up all the rows, but the DEL_RECIP_SIG fiel
d
was blank and none of the following fields displayed. Using null at least
allows the SELECTs to perform as expected and the later columns to be handle
d
properly.
My thought was to use a UPDATETEXT wrapped in a cursor to finish the job.
But the test displayed below, while it did populate the target text field,
again rendered the later columns apparently non-existent. Also, the
gobbledygook (some sort of encoded representation of a person's signature) i
n
the source and target DEL_RECIP_SIG fields don't match.
DECLARE @.ptrval_source varbinary(16)
, @.ptrval_target varbinary(16)
, @.Length_Source int
, @.Length_Target int
SELECT @.ptrval_source = TEXTPTR(DEL_RECIP_SIG)
, @.Length_Source = DEL_RECIP_LEN
FROM trackpad..old_data
where PKG_NUM = 'W44287093174'
print 'Source'
print @.ptrval_source
print @.Length_Source
/*
update Trackpad4..Old_Data
set DEL_RECIP_SIG = 'placeholder'
where PKG_NUM = 'W44287093174'
*/
select @.ptrval_target = TEXTPTR(DEL_RECIP_SIG)
, @.Length_Target = len(cast(DEL_RECIP_SIG as varchar(8000)))
FROM trackpad4..old_data
where PKG_NUM = 'W44287093174'
print 'Target'
print @.ptrval_target
print @.Length_Target
print 'source'
readtext trackpad..old_data.DEL_RECIP_SIG @.ptrval_source 0 @.Length_Source
print 'target'
readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
print 'update'
UPDATETEXT trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0
@.Length_Target trackpad..old_data.DEL_RECIP_SIG @.ptrval_source
print 'target'
readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
I tried copying the source table over through DTS, but target text fields
were empty. I was able to restore from a backup. All I can think is that
I'm not properly delineating the field going into the target record and its
violating SQL's internal rules for terminating fields and records.
Thanks for any insight,
KevinHi Kevin
You don't say what SQL Server versions you are using?
This was a problem with DTS!
http://support.microsoft.com/defaul...kb;en-us;257425
If you set the value for specific DEL_RECIP_SIG to NULL you will find out
the one that is causing this issue.
John
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:08CADCB0-8B74-4987-B78F-7F375C1BB7C8@.microsoft.com...
>I am refreshing records on a target table from a similar source table (two
> databases, same server) and having problems with the Text fields. I am
> using
> INSERTas follows:
>
> delete from Trackpad4..Archived
> set identity_insert Trackpad4..Archived on
> insert into Trackpad4..Archived
> (... DEL_RECIP_NAME, DEL_RECIP_LEN, DEL_RECIP_SIG, ...)
> select ... DEL_RECIP_NAME, DEL_RECIP_LEN, null, ...
> from Trackpad..Archived
> set identity_insert Trackpad4..Archived off
>
> The problem we noticed (and why I've got null populating in DEL_RECIP_SIG)
> is that a simple SELECT would return three rows but a rowcount of four. A
> modification of the query picked up all the rows, but the DEL_RECIP_SIG
> field
> was blank and none of the following fields displayed. Using null at least
> allows the SELECTs to perform as expected and the later columns to be
> handled
> properly.
> My thought was to use a UPDATETEXT wrapped in a cursor to finish the job.
> But the test displayed below, while it did populate the target text field,
> again rendered the later columns apparently non-existent. Also, the
> gobbledygook (some sort of encoded representation of a person's signature)
> in
> the source and target DEL_RECIP_SIG fields don't match.
>
> DECLARE @.ptrval_source varbinary(16)
> , @.ptrval_target varbinary(16)
> , @.Length_Source int
> , @.Length_Target int
> SELECT @.ptrval_source = TEXTPTR(DEL_RECIP_SIG)
> , @.Length_Source = DEL_RECIP_LEN
> FROM trackpad..old_data
> where PKG_NUM = 'W44287093174'
> print 'Source'
> print @.ptrval_source
> print @.Length_Source
> /*
> update Trackpad4..Old_Data
> set DEL_RECIP_SIG = 'placeholder'
> where PKG_NUM = 'W44287093174'
> */
> select @.ptrval_target = TEXTPTR(DEL_RECIP_SIG)
> , @.Length_Target = len(cast(DEL_RECIP_SIG as varchar(8000)))
> FROM trackpad4..old_data
> where PKG_NUM = 'W44287093174'
> print 'Target'
> print @.ptrval_target
> print @.Length_Target
> print 'source'
> readtext trackpad..old_data.DEL_RECIP_SIG @.ptrval_source 0 @.Length_Source
> print 'target'
> readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
> print 'update'
> UPDATETEXT trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0
> @.Length_Target trackpad..old_data.DEL_RECIP_SIG @.ptrval_source
> print 'target'
> readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
>
> I tried copying the source table over through DTS, but target text fields
> were empty. I was able to restore from a backup. All I can think is that
> I'm not properly delineating the field going into the target record and
> its
> violating SQL's internal rules for terminating fields and records.
> Thanks for any insight,
> Kevin|||Hi, John,
Thanks for the reply. I'm using SQL 2000, SP3. I've tried this operation
in TSQL and DTS - same results. The UpdateText operation also didn't work -
seemingly the stored text data wasn't terminated properly, and following
fields in the record were not rendered at all. In any event, the target tex
t
did not resemble the source text.
If you can think of anything else, thanks in advance.
- Kevin
"John Bell" wrote:

> Hi Kevin
> You don't say what SQL Server versions you are using?
> This was a problem with DTS!
> http://support.microsoft.com/defaul...kb;en-us;257425
> If you set the value for specific DEL_RECIP_SIG to NULL you will find out
> the one that is causing this issue.
> John
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:08CADCB0-8B74-4987-B78F-7F375C1BB7C8@.microsoft.com...
>
>

Copying tables, MSDE

Hi!

I've got a very simple problem I can't find an answere to.

I've got an MSDE database and I want to copy a table.

I've tried something like:

create table2 as select * from table1

with and without the "as", but I can't get it to work and I can't find a good answere on the internet.

very thankful for an answere!

/Jon

hello..

use SELECT INTO statement:
from MSDN:

The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.
sample code:
SELECT * INTO table2
FROM table1

|||ok, almost there...
it works, except for the keys.
how do I make the primary keys be primary keys in the copied table aswell?
Thanx!
/J|||

(sorry if this reply got posted twice)

thanx,

is it possible to get the old primary keys to be primary keys in the new table aswell?

The newly created table does not contain any kesy at this moment.

/jon

Copying tables, finding number of columns in a table, & size of data in selected colum

I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all
its indexes and data in the same database as table A_copy.
2) I know how to figure out how many rows a table has, how do you figure
out how many columns?
3) Where does it say how large in terms of disk space (data) a table is?
4) Can you determine how large in terms of disk space only selected
columns within a table are?
[ Sugapablo ]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--music ]
[ http://www.2ra.org <--political | http://www.subuse.net <--discuss ]
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
SELECT * INTO A_copy
FROM A
Note: PK and FK and any indexed columns will migrate their data, but the
PK, FK and indexes themselves will not be recreated. You will have to do
that yourself.

> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
Take a look at the INFORMATION_SCHEMA views.
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<tablename>'

> 3) Where does it say how large in terms of disk space (data) a table is?
You can start with sp_spaceused 'objname'
EXEC sp_spaceused 'SomeTable'

> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
Not easily. You would need to get the width of the column (average width
for variable length columns) and multiply that by the number of rows in the
table. There are other factors to include, however, this should get you
reasonably close.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi,
I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all its
indexes and data in the same database as table A_copy.
Generate Script with dependant objects using enterprise manager, execute
that in destination
database and use DTS to transfer the data.
2) I know how to figure out how many rows a table has, how do you figure out
how many columns?
select count(*) from syscolumns where object_name(id)='table_name'
or
Query the same on INFORMATION_SCHEMA.COLUMNS VIEW.
3) Where does it say how large in terms of disk space (data) a table is?
sp_spaceused <table_name>
4) Can you determine how large in terms of disk space only selected columns
within a table are?
You have to manually calculate based on usage and alloctions for each field.
Thanks
Hari
SQL Server MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.23.16.12.37.486877@.REMOVEsugapabl o.com...
> I'm trying to figure out how to do a few things in MS SQL Server but can't
> Google up what I need or find it in the docs that I have.
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
> 3) Where does it say how large in terms of disk space (data) a table is?
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>

Copying tables, finding number of columns in a table, & size of data in selected c

I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all
its indexes and data in the same database as table A_copy.
2) I know how to figure out how many rows a table has, how do you figure
out how many columns?
3) Where does it say how large in terms of disk space (data) a table is?
4) Can you determine how large in terms of disk space only selected
columns within a table are?
[ Sugapablo
]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--mu
sic ]
[ http://www.2ra.org <--political | http://www.subuse.net <--di
scuss ]> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
SELECT * INTO A_copy
FROM A
Note: PK and FK and any indexed columns will migrate their data, but the
PK, FK and indexes themselves will not be recreated. You will have to do
that yourself.

> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
Take a look at the INFORMATION_SCHEMA views.
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<tablename>'

> 3) Where does it say how large in terms of disk space (data) a table is?
You can start with sp_spaceused 'objname'
EXEC sp_spaceused 'SomeTable'

> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
Not easily. You would need to get the width of the column (average width
for variable length columns) and multiply that by the number of rows in the
table. There are other factors to include, however, this should get you
reasonably close.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi,
I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all its
indexes and data in the same database as table A_copy.
Generate Script with dependant objects using enterprise manager, execute
that in destination
database and use DTS to transfer the data.
2) I know how to figure out how many rows a table has, how do you figure out
how many columns?
select count(*) from syscolumns where object_name(id)='table_name'
or
Query the same on INFORMATION_SCHEMA.COLUMNS VIEW.
3) Where does it say how large in terms of disk space (data) a table is?
sp_spaceused <table_name>
4) Can you determine how large in terms of disk space only selected columns
within a table are?
You have to manually calculate based on usage and alloctions for each field.
Thanks
Hari
SQL Server MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.23.16.12.37.486877@.REMOVEsugapablo.com...
> I'm trying to figure out how to do a few things in MS SQL Server but can't
> Google up what I need or find it in the docs that I have.
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
> 3) Where does it say how large in terms of disk space (data) a table is?
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>

Thursday, March 29, 2012

Correlated Subquery

Correlated Subquery

Correlated Subquery

Correlated and aggregate sql query

Correct way to create indices? SQLServer Express 2005

Correct way of finding a tables primary keys?

Correct Table Structure - Optional Values

Tuesday, March 27, 2012

Correct SELECT Statement via joins

Correct invalid SID

Correalated sub...

Copying/ Deleting Huge Data from a table

Copying Views

Sunday, March 25, 2012

Copying Views

Copying Text Field

Copying tables, MSDE

Copying tables, finding number of columns in a table, & size of data in selected colum

Copying tables, finding number of columns in a table, & size of data in selected c

sql server db

Blog Archive

About Me