sql server db: tables

Showing posts with label tables. Show all posts

Thursday, March 29, 2012

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 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
Gav
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
>
>
|||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
|||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 = 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
|||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 = 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
|||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:
[vbcol=seagreen]
> 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:

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
GavSELECT 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 SUPPLIER
S
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to b
e
>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
>
>|||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|||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:%23
VzsIQ9ZFHA.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 t
he "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 inde
x 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|||Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
VzsIQ9ZFHA.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 t
he "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 inde
x 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|||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:
[vbcol=seagreen]
> 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:
>

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--

Copying/Transferring Tables

Is there a way to copy all tables (not the data, just the structure) and their relationship diagrams from one SQL server db to another?
What is the easiest way?
ThanksRight click on the Database,Generate Sql Script and set the condition on all the tabs.In order to transfer the permissions,roles,relationship you need to go under option in the same wizard.|||IMHO DTS is the best choice here as it will take care of all dependent objects and constraints. In this case you would elect NOT to transfer the data or probably user id and permissions. The only problem with this is it will not transfer the relationship diagram.|||I just want to transfer Tables and diagrams. I don't want other objects.
I know I can use a wizard to transfer the tables but it doesn't have an option to transfer the diagrams.

Originally posted by sqlserver2k
Right click on the Database,Generate Sql Script and set the condition on all the tabs.In order to transfer the permissions,roles,relationship you need to go under option in the same wizard.|||I am not aware of any tool or wizard that will transfer a relationship diagram, however the data is contained in a table called dtProperties and you could use the following to transfer a diagram:

set identity_insert dtproperties on
INSERT INTO <target db>.dbo.dtproperties(id, objectid, property, value, uvalue, lvalue, version)
select * from <source db>.dbo.dtproperties
set identity_insert dtproperties offsql

Copying Views and Stored Procedures

Hello All,
I have two SQL databases which have the exact same tables - just different data. In database A there are only tables. In database B there are tables, stored procedures and views. I need to transfer the stored procedures and views from database B into database A. Is there a way to do this?
Thanks in advanceDTS or Script it. Your pick.|||Thank You.

I was playing around with it and I used a script. Thanks for your reply.|||What about contraints?

Just make sure you do them in the correct order...ie if a sproc references a view...

I guess if you did you'd just get a warning message...and once it was reference it would be resolved...

Anyone experience this?|||Originally posted by Brett Kaiser
What about contraints?

Just make sure you do them in the correct order...ie if a sproc references a view...

I guess if you did you'd just get a warning message...and once it was reference it would be resolved...

Anyone experience this?

I believe no entries would be inserted into sysdepends for the sproc corresponding to the view... which would lead to the view not showing up when you do a sp_depends on the sproc ...

Sunday, March 25, 2012

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 ]
>

copying tables with SMO between different instances

Hi,

I want to be able to with SMO copy one table (with a schema other than dbo) to another instance.

I can successfully do this on eth same machine (same instance) but when I try the following script to copy accross different instances. it doesn't work as the Transfer methode in SMO doesn't have a property for instance.

would be able to help

Thanks

Public Sub Main()

Dim svr_connection As ServerConnection = New ServerConnection()

'svr_connection.ServerInstance = "(local)"

svr_connection.ConnectionString = "Data Source=TECH-2L56H2J\sql2k5;Initial Catalog=creditcard;Trusted_Connection=true"

Dim dest_svr_connection As ServerConnection = New ServerConnection()

'svr_connection.ServerInstance = "(local)"

dest_svr_connection.ConnectionString = "Data Source=TECH-2L56H2J\sql2k5_2;Initial Catalog=master;Trusted_Connection=true"

Dim srv As Server = New Server(svr_connection)

Dim dest_srv As Server = New Server(dest_svr_connection)

'Reference the source database

Dim db As Database

db = srv.Databases("creditcard")

'Create a new database that is to be destination database.

Dim dbCopy As Database

dbCopy = New Database(dest_srv, "creditcardcopy")

dbCopy.Create()

'Define a Transfer object and set the required options and properties.

Dim xfr As Transfer

'Point the transfer object to the source database (to be copied from)

xfr = New Transfer(db)

'Turn off the copying of all objects - seems to be on by default

xfr.CopyAllObjects = False

'Add the schema, role and table we want to copy (as the tables isn't in dbo, the associated schema and role needs to be moved)

'xfr.ObjectList.Add(db.Roles("test"))

xfr.ObjectList.Add(db.Schemas("test"))

xfr.ObjectList.Add(db.Tables("credittest", "test"))

'We want to copy the data as well

xfr.CopyData = True

'Set other transfer options

xfr.Options.WithDependencies = False

'Set where we are copying to

xfr.DestinationDatabase = "creditcardcopy"

xfr.DestinationServer = "TECH-2L56H2J\sql2k5_2" 'dest_srv.Name

'Do the transfer

xfr.TransferData()

'return success

' Dts.TaskResult = Dts.Results.Success

End Sub

Could you confirm the error you're getting? If you have multiple instances on the same box, you may want to specify port numbers in your connection string (get this from SQL Configuration Manager)

eg Data Source=TECH-2L56H2J\sql2k5_2, 1434

HTH!|||Thanks but tried that already, you see that part works fine and it can connect to both instances and creates the database on the target machine.it fails in the process of transfering objects(tables and schemas). therefore I think it should be down to xfr.DestinationServer ( the transfer object properties )

Thanks

sql

copying tables with SMO between different instances

Hi,

I want to be able to with SMO copy one table (with a schema other than dbo) to another instance.

would be able to help

Thanks

Public Sub Main()

Dim svr_connection As ServerConnection = New ServerConnection()

'svr_connection.ServerInstance = "(local)"

svr_connection.ConnectionString = "Data Source=TECH-2L56H2J\sql2k5;Initial Catalog=creditcard;Trusted_Connection=true"

Dim dest_svr_connection As ServerConnection = New ServerConnection()

'svr_connection.ServerInstance = "(local)"

dest_svr_connection.ConnectionString = "Data Source=TECH-2L56H2J\sql2k5_2;Initial Catalog=master;Trusted_Connection=true"

Dim srv As Server = New Server(svr_connection)

Dim dest_srv As Server = New Server(dest_svr_connection)

'Reference the source database

Dim db As Database

db = srv.Databases("creditcard")

'Create a new database that is to be destination database.

Dim dbCopy As Database

dbCopy = New Database(dest_srv, "creditcardcopy")

dbCopy.Create()

'Define a Transfer object and set the required options and properties.

Dim xfr As Transfer

'Point the transfer object to the source database (to be copied from)

xfr = New Transfer(db)

'Turn off the copying of all objects - seems to be on by default

xfr.CopyAllObjects = False

'Add the schema, role and table we want to copy (as the tables isn't in dbo, the associated schema and role needs to be moved)

'xfr.ObjectList.Add(db.Roles("test"))

xfr.ObjectList.Add(db.Schemas("test"))

xfr.ObjectList.Add(db.Tables("credittest", "test"))

'We want to copy the data as well

xfr.CopyData = True

'Set other transfer options

xfr.Options.WithDependencies = False

'Set where we are copying to

xfr.DestinationDatabase = "creditcardcopy"

xfr.DestinationServer = "TECH-2L56H2J\sql2k5_2" 'dest_srv.Name

'Do the transfer

xfr.TransferData()

'return success

' Dts.TaskResult = Dts.Results.Success

End Sub

Could you confirm the error you're getting? If you have multiple instances on the same box, you may want to specify port numbers in your connection string (get this from SQL Configuration Manager)

eg Data Source=TECH-2L56H2J\sql2k5_2, 1434

Thanks

Copying tables using SSIS package

I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.

I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.

I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.

I'm wondering if I need to do a For Each loop looking at an ADO object?

Any suggestions?

Stephanie

SBowe wrote:

I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.
I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.
I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.
I'm wondering if I need to do a For Each loop looking at an ADO object?
Any suggestions?
Stephanie

I suggest you use the Import Wizard to do this for you. I think you can configure it to build the tables for you if they are not already there.

-Jamie

|||

Jamie,

Thanks for the suggestion. However, that won't work for my environment. Specifically, my company requires that I create a scheduled job. So I then must utilize a package.

Here's where I get the flaky results using the SQL transfer object: I only want the non-system tables. So I set the All Tables property to False and then select the tables I want from the Tables Collection property. However, the package then fails when I run it and tells me it cannot find the tables from the source. This is mind-boggling since it allowed me to pick the tables from a list of tables.

I'll keep digging.

Thanks again,

Stephanie

|||

Stephanie,

If you use the import/export wizard in SSMS todo this; you can choose to save it as an SSIS package; then you can open that package and make the specific changes you need (eg renaming the 4 tables).

Rafael Salas

|||Stephanie,

I would advocate NOT using the transfer objects task.

This has some "features" (apparently to preserve sql 2000 compatability) that means the tables will not be transferred over accurately.

Specifically, you may find your transferred tables lose default values or identities

see my thread on this here|||

Rafael Salas wrote:
Stephanie,
If you use the import/export wizard in SSMS todo this; you can choose to save it as an SSIS package; then you can open that package and make the specific changes you need (eg renaming the 4 tables).
Rafael Salas

The import / export wizard will also not setup the tables correctly - see the post I linked to in my post above

copying tables in SQL Server

Hi,
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.
did you hear "=?Utf-8?B?c2hhcm1hbg==?="
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:

> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.
|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY if
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easiest
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:

> Hi,
> I am a newbie. I want to copy a table from the production database and paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.
|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:

> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
>
>
|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
[vbcol=seagreen]
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:

copying tables in SQL SERVER

I have a 100m row table that I need to come from one database to another database in SQL SERVER.

The bulkcopy feature in DTS is nice -- however is there a stored procedure or external software that will be able to do this outside of DTS.

Right now I am doing a
SELECT *
INTO
(table name)
FROM (table name)

and on a 100m row table it is taking around 52 hours. Not acceptable.Is it on the same server?

bcp out in native format and bcp in is probably the fastest

But the SELECT * INTO is a minimally logged operation...and if it's on the same server...

I'm not so sure bcp would beat it since it's 1 operation as compared to 2.|||Yes it is on the same server.|||For 100 million rows, I think I'd partition it up anyway...

What's the DDL of the table...is it a heap or does it have a pk?

If it's got something unique, I'd split it up in to 10 tables and thread out the SELECT Collist INTO Table1
SELECT Collist INTO Table2
SELECT Collist INTO Table3
SELECT Collist INTO Table4
ect

And run all 10 at the same time from 10 separate osql bat files...

thats 1 select per file....

How long does a backup take?|||Hello Everyone,

If you would like to copy records within the same database between the different databases the you can use this query,

insert into <table Name> select * from <Destination Table>|||insert into <table Name> select * from <Destination Table>The only problem is that this syntax is fully logged, while the SELECT INTO syntax is only minimally logged. Since the SELECT INTO appears to be too slow and I'd expect this to be even slower, I don't think it would be a good solution.

-PatP|||Damn...I wonder if that belongs here...

http://thedailywtf.com/archive/2004/09/01/1511.aspx

copying tables in SQL Server

Hi,
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.did you hear "examnotes"
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:

> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY i
f
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easies
t
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:

> Hi,
> I am a newbie. I want to copy a table from the production database and pas
te
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:

> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See t
he
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
>
>|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
[vbcol=seagreen]
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:
>sql

copying tables in SQL Server

Hi,
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.did you hear "=?Utf-8?B?c2hhcm1hbg==?="
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:
> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY if
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easiest
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:
> Hi,
> I am a newbie. I want to copy a table from the production database and paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:
> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> > Hi,
> >
> > I am a newbie. I want to copy a table from the production database and
> > paste
> > it in the test database thus replacing the old copy in the test database.
> > What's the best way to do it? Thanks in advance.
>
>|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:
> > Sharman,
> >
> > Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> > SQL Books Online for more information.
> >
> > HTH
> >
> > Jerry
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> > > Hi,
> > >
> > > I am a newbie. I want to copy a table from the production database and
> > > paste
> > > it in the test database thus replacing the old copy in the test database.
> > > What's the best way to do it? Thanks in advance.
> >
> >
> >

copying tables from one server to another

This is a little complicated, so let me explain what I'm after.
My client runs my app against sql server 2000. I can connect via vpn and
tcp/ip. However, I may need to test from time to time against the current
data. I maintain a copy of the database on my system, but the tables are,
by definition, not up to date. I could of course transfer the database to
my server, but this is practically impossible, as the database is over 3
gigs and via tcp/ip it will take 15 hours.
Is there a way that I can copy individual tables to my server, replacing my
old tables, so I can run tests against current data without having to do so
on the active sql server at my client's location?
Thanks for any help.
Bernie YaegerHi,
You could use either one:-
1. BCP OUT the data, copy the file to destination and in destination use
BCP IN to load it into table
2. USE DTS - Export and Import wizard.
I recommend you to
a. BCP OUT the table data from source server
b. copy the file to your test machine
c. BCP IN the data into your machine
Thanks
Hari
MCDBA
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:V_QNc.9183$09.1208788@.news4.srv.hcvlny.cv.net...
> This is a little complicated, so let me explain what I'm after.
> My client runs my app against sql server 2000. I can connect via vpn and
> tcp/ip. However, I may need to test from time to time against the current
> data. I maintain a copy of the database on my system, but the tables are,
> by definition, not up to date. I could of course transfer the database to
> my server, but this is practically impossible, as the database is over 3
> gigs and via tcp/ip it will take 15 hours.
> Is there a way that I can copy individual tables to my server, replacing
my
> old tables, so I can run tests against current data without having to do
so
> on the active sql server at my client's location?
> Thanks for any help.
> Bernie Yaeger
>|||Hi Hari,
Tx for your reply. I did try to work with dts, but I got a bit bogged
down - I think I need to understand its features a bit better. But BCP -
good idea! I'll try it.
Thanks,
Bernie
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uynQ5bMdEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> You could use either one:-
> 1. BCP OUT the data, copy the file to destination and in destination use
> BCP IN to load it into table
> 2. USE DTS - Export and Import wizard.
> I recommend you to
> a. BCP OUT the table data from source server
> b. copy the file to your test machine
> c. BCP IN the data into your machine
>
> Thanks
> Hari
> MCDBA
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:V_QNc.9183$09.1208788@.news4.srv.hcvlny.cv.net...
> > This is a little complicated, so let me explain what I'm after.
> >
> > My client runs my app against sql server 2000. I can connect via vpn
and
> > tcp/ip. However, I may need to test from time to time against the
current
> > data. I maintain a copy of the database on my system, but the tables
are,
> > by definition, not up to date. I could of course transfer the database
to
> > my server, but this is practically impossible, as the database is over 3
> > gigs and via tcp/ip it will take 15 hours.
> >
> > Is there a way that I can copy individual tables to my server, replacing
> my
> > old tables, so I can run tests against current data without having to do
> so
> > on the active sql server at my client's location?
> >
> > Thanks for any help.
> >
> > Bernie Yaeger
> >
> >
>

copying tables from one server to another

This is a little complicated, so let me explain what I'm after.
My client runs my app against sql server 2000. I can connect via vpn and
tcp/ip. However, I may need to test from time to time against the current
data. I maintain a copy of the database on my system, but the tables are,
by definition, not up to date. I could of course transfer the database to
my server, but this is practically impossible, as the database is over 3
gigs and via tcp/ip it will take 15 hours.
Is there a way that I can copy individual tables to my server, replacing my
old tables, so I can run tests against current data without having to do so
on the active sql server at my client's location?
Thanks for any help.
Bernie YaegerHi,
You could use either one:-
1. BCP OUT the data, copy the file to destination and in destination use
BCP IN to load it into table
2. USE DTS - Export and Import wizard.
I recommend you to
a. BCP OUT the table data from source server
b. copy the file to your test machine
c. BCP IN the data into your machine
Thanks
Hari
MCDBA
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:V_QNc.9183$09.1208788@.news4.srv.hcvlny.cv.net...
> This is a little complicated, so let me explain what I'm after.
> My client runs my app against sql server 2000. I can connect via vpn and
> tcp/ip. However, I may need to test from time to time against the current
> data. I maintain a copy of the database on my system, but the tables are,
> by definition, not up to date. I could of course transfer the database to
> my server, but this is practically impossible, as the database is over 3
> gigs and via tcp/ip it will take 15 hours.
> Is there a way that I can copy individual tables to my server, replacing
my
> old tables, so I can run tests against current data without having to do
so
> on the active sql server at my client's location?
> Thanks for any help.
> Bernie Yaeger
>|||Hi Hari,
Tx for your reply. I did try to work with dts, but I got a bit bogged
down - I think I need to understand its features a bit better. But BCP -
good idea! I'll try it.
Thanks,
Bernie
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uynQ5bMdEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> You could use either one:-
> 1. BCP OUT the data, copy the file to destination and in destination use
> BCP IN to load it into table
> 2. USE DTS - Export and Import wizard.
> I recommend you to
> a. BCP OUT the table data from source server
> b. copy the file to your test machine
> c. BCP IN the data into your machine
>
> Thanks
> Hari
> MCDBA
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:V_QNc.9183$09.1208788@.news4.srv.hcvlny.cv.net...
and[vbcol=seagreen]
current[vbcol=seagreen]
are,[vbcol=seagreen]
to[vbcol=seagreen]
> my
> so
>

Thursday, March 29, 2012

Correct way of finding a tables primary keys?

Correct SELECT Statement via joins

Tuesday, March 27, 2012

Correct SELECT Statement via joins

Correct SELECT Statement via joins

Copying/Transferring Tables

Copying Views and Stored Procedures

Sunday, March 25, 2012

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

copying tables with SMO between different instances

copying tables with SMO between different instances

Copying tables using SSIS package

copying tables in SQL Server

copying tables in SQL SERVER

copying tables in SQL Server

copying tables in SQL Server

copying tables from one server to another

copying tables from one server to another

sql server db

Blog Archive

About Me