Correlated query in SELECT clause

There is a problem when I include correlated query in the SELECT clause in
the SQL statement like the following:
SELECT a.column1, (
SELECT b.column2
FROM b
WHERE b.a_id = a.a_id
) as dummy
FROM a;
(just replace a and b with any related table names from your database)
This query executed without any problems in query designer and in a report.
But when executed from inside report designer (Microsoft Developer
Environment, Data tab) I got the following error:
The column prefix 'a' doesn't match with a table name or alias name used in
the query.
Is it some kind of bug or just 'feature' of Query Designer?Try taking out all carraige returns.
VA wrote:
>There is a problem when I include correlated query in the SELECT clause in
>the SQL statement like the following:
>SELECT a.column1, (
> SELECT b.column2
> FROM b
> WHERE b.a_id = a.a_id
>) as dummy
>FROM a;
>(just replace a and b with any related table names from your database)
>This query executed without any problems in query designer and in a report.
>But when executed from inside report designer (Microsoft Developer
>Environment, Data tab) I got the following error:
>The column prefix 'a' doesn't match with a table name or alias name used in
>the query.
>Is it some kind of bug or just 'feature' of Query Designer?
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Does it work if you make it a VIEW, and do SELECT * FROM TheView in
your frontend?

Correlated and aggregate sql query

I have a table similar to the following:

ID | Name ID | Period From | Period To | Percentage |

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

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

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

(

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 syntax on this SQL INNER JOIN Query.

Hi :)

Trying to get this sql - query to run under Query Analyzer but not sure how to correct anything in it to the right :)

______INFO_______
Table : iptable
Fields : ip_start, ip_end, location

Table : PageLog
Fields : pl_ipaddress, pl_sessionid, pl_remotehost
_________________

______________CODE_______________
select
iptable.location
, count(pl_ipaddress)
from (
select distinct
pl_sessionid
, pl_ipaddress
, pl_remotehost
from PageLog
where pl_datetime
between '2003-12-25 00:00:00'
and '2003-12-25 23:59:59'
and pl_ipaddress <> ''
) as dt_pagelog
inner
join iptable
on dt_pagelog.pl_ipaddress
between iptable.ip_start
and iptable.ip_end
group
by iptable.location
order
by count(pl_ipaddress) desc
___________END CODE_________________

1) If i run this i get :
--> "Column dt_pagelog.pl_ipaddress is invalid in the select list because it's not contained in a aggregate function or in the GROUP BY clause.

2) If i include it in the GROUP BY i get :
--> The text, ntext and image datatypes cannot be used in WHERE, HAVING or ON clause, except with the LIKE or IS NULL predicate.

Soo.. how on earth should i put this right to get to use it with a INNER JOIN, since that has to have a ON to it ?

Not very familiar with INNER JOIN's so any help will be very much appreciated..

Best regards
Mirador.select a.col1, a.col2, b.col1, b.colxxxx
from table1 as a
inner join table2 as b
on a.col1 = b.col1
where a.col2 = value|||Hi Rushi and thanx for your reply..

What am i to do with the SELECT DISTINCT ......... FROM PageLog ?

Should i add PageLog.fieldname to each of the sentences in the select there to ?

Mirador.|||select distinct a.location, .........
from pagelog as a
inner join iptable as b
on a.--- = b.========

In inner join syntax
you have to join the common fields of the 2 tables in inner join clause and the actual where condition in where clause.|||the error message doesn't make sense

"dt_pagelog.pl_ipaddress is invalid in the select list because it's not contained in a aggregate function"

dt_pagelog.pl_ipaddress is contained in a aggregate function -- the COUNT()

maybe it's the subquery's DISTINCT, although i seriously doubt it

try this:select iptable.location
, count(dt_pagelog.pl_ipaddress)
from (
select pl_sessionid
, pl_ipaddress
, pl_remotehost
from PageLog
where pl_datetime
between '2003-12-25 00:00:00'
and '2003-12-25 23:59:59'
and pl_ipaddress <> ''
group
by pl_sessionid
, pl_ipaddress
, pl_remotehost
) as dt_pagelog
inner
join iptable
on dt_pagelog.pl_ipaddress
between iptable.ip_start
and iptable.ip_end
group
by iptable.location
order
by count(dt_pagelog.pl_ipaddress) desc|||Hi again Rudy :)

This is indeed a bit weird...
Tried the exact query u posted and got this errormsg :

-----Error------

Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.

Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.
------------|||okay, see if you can understand where i'm going with this...

which one of your columns is text, ntext, or image?

and just to give you a little advnace notice, my next question will be why

session id, ip address, remote host, location -- those all sound like varchars to me|||Oh my... :( i finally got it...

The ip_start and ip_end was text while the others were varchar..

As soon as i put all of them to varchar it worked...

Well.. ended up with a easy solution after all!... I thought this had to be something really really tricky stuff..

Now i know :) hehe.. varchars dont match very good with text when it comes to comparing..

Thanx for all your help Rudy...

Best regards
Terje.

Corelated Sub query

Hi,

I'm using this query, It works fine, except that i want to show PayType. When i tried to show it, I'll get duplicate records

What can i do?

Code Snippet

SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator, tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,

tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount, tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,

tbl_FundCode.FundDescription

FROM tbl_TransPayLnk LEFTOUTERJOIN

tbl_FundCode INNERJOIN

tbl_Transaction ON tbl_FundCode.FundCodeID = tbl_Transaction.FundCodeID ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo

WHEREEXISTS

(SELECT PaymentID, PayID, PayType, Amount

FROM tbl_Payment AS tbl_Payment_1

WHERE(PayID = tbl_TransPayLnk.PayID))

Thanks

Prince:

I assume by your post that to add "PayType" that you need to also join to the tbl_Payment table. I would normally expect that the amounts would vary from record to record in that table. Also, I question the use of showing the "payType" without also showing the amount. If all you truely want is the "PayType" then changing your "SELECT" to a "SELECT DISTINCT" might be in order. Also, experiment with adding the AMOUNT and possibly the "PaymentID" to the data returned and see if the data makes more "sense" that way.

Kent

|||

Prince

Try this:

Code Snippet

SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator,

tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,

tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount,

tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,

tbl_FundCode.FundDescription,

tbl_Payment_1.PayType

FROM tbl_TransPayLnk

LEFTOUTERJOIN tbl_FundCode

INNERJOIN tbl_Transaction

ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo

INNERJOIN

(

SELECTDISTINCT PayID, PayType

FROM tbl_Payment

)AS tbl_Payment_1

ON tbl_TransPayLnk.PayID = tbl_Payment_1.PayID

P.S. Some sample DDL and sample data would sure make it easier to assist you....

|||

Hi,

Right i tried your solution and got an error :

Code Snippet

Msg 102, Level 15, State 1, Line 31

Incorrect syntax near 'PayID'.

As requested here are table structure and data:

Tbl_Transaction

Code Snippet

TransactionID int Unchecked
TransNo varchar(50) Checked
RefNo nvarchar(50) Checked
FundCodeID smallint Checked
TransAmount decimal(18, 2) Checked
Description varchar(100) Checked

Data in Tbl_Transaction

Code Snippet

TransactionID TransNo RefNo FundCodeID TransAmount Description 13 1001 20013808 1 28.55 14 1001 34983249 2 9.31

Tbl_TransPayLnk

Code Snippet

TransPayID int Unchecked
PayID int Checked
TransNo varchar(50) Checked
TransDate datetime Checked
Operator char(5) Checked
TerminalID tinyint Checked
Flagged char(1) Unchecked
Remarks text Checked
RemarksDate datetime Checked
TPayment decimal(18, 2) Checked

Data Tbl_TransPayLnk

Code Snippet

TransPayID PayID TransNo TransDate Operator TerminalID Flagged Remarks RemarksDate TPayment 5 1 1001 09/05/2007 02 2 V hi there 13/06/2007 37.86 6 1003 1002 09/05/2007 02 2 N 73.33

Tbl_Payment

Code Snippet

PaymentID int Unchecked
PayID int Checked
PayType varchar(50) Checked
Amount decimal(18, 2) Checked

Data Tbl_Payment

Code Snippet

PaymentID PayID PayType Amount 17 1 Cheque 30 18 1 Cash 7.86 19 1003 Cheque 73.33

And this is what i would need in one ROW

TransPayID TransDate Operator Flagged Remarks RemarksDate RefNo TransAmount TransNo PayID FundDescription 5 09/05/2007 02 V hi there 13/06/2007 20013808 28.55 1001 1 Rent 5 09/05/2007 02 V hi there 13/06/2007 34983249 9.31 1001 1 Sundry Debtor 6 09/05/2007 02 N 20013808 73.33 1002 1003 TV Licence

the only thing i want more in there is PAY TYPE without duplicating the rows. I mean as it is.

If i would add Payment tbl regardless of the joins i would be duplicating ROW 5 2 more times. so .......

Which would be cheque or cash.

Any ideas.

Thanks

|||

Which row out of tbl_Payment do you want to be in the result?

How do you determine that that row should be it?

Do you just want one, you don't care which?

Or the highest amount?

|||

Hi,

Well

TransPayID TransDate Operator Flagged Remarks RemarksDate RefNo TransAmount TransNo PayID FundDescription 5 09/05/2007 02 V hi there 13/06/2007 20013808 28.55 1001 1 Rent 5 09/05/2007 02 V hi there 13/06/2007 34983249 9.31 1001 1 Sundry Debtor 6 09/05/2007 02 N 20013808 73.33 1002 1003 TV Licence

If you look at this i'm getting PayID which is one. Against one in tbl_payment there is PayType. I just want PayType so cheque or cash would be against record PayID = 1

Thanks

|||

Prince:

What is the complete key to the tbl_payment table? Also, doesn't the fact that the LEFT JOIN table participate in a subsequent inner join:

Code Snippet

INNERJOIN

tbl_Transaction ON tbl_FundCode.FundCodeID = tbl_Transaction.FundCodeID ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo

turn the LEFT JOIN into a defacto INNER JOIN?

|||

Hello Kent,

The primary key for tbl_Payment is PaymentID but that is not how i join. I joined it to tbl_transPayLnk using PayID which is copied in both these tables.

I don't know but what else can i do?

Read my long post and you get the idea of what i want?

Thanks

|||

Prince

I commented out the reference to the FundCode table since you didn't provide the data.

Just uncomment it and add your ON clause to include that data.

Code Snippet

createtable tbl_Transaction (

TransactionID int,

TransNo varchar(50),

RefNo nvarchar(50),

FundCodeID smallint,

TransAmount decimal(18, 2),

Description varchar(100)

)

createtable tbl_TransPayLnk (

TransPayID int,

PayID int,

TransNo varchar(50),

TransDate datetime,

Operator char(5),

TerminalID tinyint,

Flagged char(1),

Remarks text,

RemarksDate datetime,

TPayment decimal(18, 2)

)

createtable tbl_Payment (

PaymentID int,

PayID int,

PayType varchar(50),

Amount decimal(18, 2)

)

setdateformat dmy

insertinto tbl_Transaction values(13,'1001','20013808', 1, 28.55,null)

insertinto tbl_Transaction values(14,'1001','34983249', 2, 9.31,null)

insertinto tbl_TransPayLnk values(5, 1,'1001','09/05/2007','02', 2,'V','hi there','13/06/2007', 37.86)

insertinto tbl_TransPayLnk values(6, 1003,'1002','09/05/2007','02', 2,'N',null,null, 73.33)

insertinto tbl_Payment values(17, 1,'Cheque', 30)

insertinto tbl_Payment values(18, 1,'Cash', 7.86)

insertinto tbl_Payment values(19, 1003,'Cheque', 73.33)

SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator,

tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,

tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount,

tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,

--tbl_FundCode.FundDescription,

tbl_Payment_1.PayType

FROM tbl_TransPayLnk

--LEFT OUTER JOIN tbl_FundCode

INNERJOIN tbl_Transaction

ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo

INNERJOIN

(

SELECTDISTINCT PayID, PayType

FROM tbl_Payment

)AS tbl_Payment_1

ON tbl_TransPayLnk.PayID = tbl_Payment_1.PayID

and tbl_Payment_1.PayType =

(selecttop 1 paytype from tbl_Payment

where tbl_TransPayLnk.PayID = tbl_Payment_1.PayID

)

|||

Hello Dale,

Thank you very much. I checked the code and although it does bring up the records. It only brought back Cheque even for the records that have cash in tbl_payment.

If it works fine, it should have brought cash and cheque for PayID= 1 which it didn't

Any ideas why?

Thanks I really appreciate your help.

|||OK; that helps. Now, are you expecting two lines to be displayed when there are two different pay types or are you wanting something like, 'Cheque, Cash' where the results are strung together? (Please forgive me, Dale)|||

That's where I'm confused on what you're looking for.

How exactly do you want PayType to appear in the result?

|||

There are only 2 pay types so it should appear as it is:

so for PayID =1 both cheque and cash should be displayed but without repeating the record 4 times.

Because rightnow it would do this if you omitt your last query. 2 cash and 2 for cheque for PayID=1

|||

"... Now, are you expecting two lines to be displayed when there are two different pay types or are you wanting something like, 'Cheque, Cash' where the results are strung together? ..."

(Like I said: Please forgive me, Dale... *sigh* )

|||

Code Snippet

createfunction dbo.GetPayType ( @.PayID asint)

returnsvarchar(200)

as

begin

declare @.pt varchar(200)

select @.pt =coalesce( @.pt +',','')+ paytype

from tbl_Payment where payid = @.PayID

return @.pt

end

SELECT tbl_TransPayLnk.TransPayID, tbl_TransPayLnk.TransDate, tbl_TransPayLnk.Operator,

tbl_TransPayLnk.Flagged, tbl_TransPayLnk.Remarks,

tbl_TransPayLnk.RemarksDate, tbl_Transaction.RefNo, tbl_Transaction.TransAmount,

tbl_TransPayLnk.TransNo AS Expr1, tbl_TransPayLnk.PayID,

--tbl_FundCode.FundDescription,

dbo.GetPayType(tbl_TransPayLnk.PayID)as PayType

FROM tbl_TransPayLnk

--LEFT OUTER JOIN tbl_FundCode

INNER JOIN tbl_Transaction

ON tbl_TransPayLnk.TransNo = tbl_Transaction.TransNo

Copying tables

I am able to query several different msde databases on my network using
Query Analyzer from my pc. Using SQL Query Analyzer from my pc, I need to
copy a table from a database found on my local msde installation to a
database on another pc's msde installation. I was trying to find the SQL
Query syntax on Books Online but I had no luck. Can you help?
Thanks,
Ademar Nunes
Hi,
See BCP OUT and BCP IN in books online.
Thanks
Hari
SQL Server MVP
"Ademar" <Ademar@.noneofyourbusiness.com> wrote in message
news:uo2iGb6uEHA.1260@.TK2MSFTNGP12.phx.gbl...
>I am able to query several different msde databases on my network using
> Query Analyzer from my pc. Using SQL Query Analyzer from my pc, I need
> to
> copy a table from a database found on my local msde installation to a
> database on another pc's msde installation. I was trying to find the SQL
> Query syntax on Books Online but I had no luck. Can you help?
> --
> Thanks,
> Ademar Nunes
>
|||I did, but I was unable to make it work. I tried again, and I'm still
unable. Can you help?
Thanks,
Ademar Nunes
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eFagfq8uEHA.568@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> See BCP OUT and BCP IN in books online.
>
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Ademar" <Ademar@.noneofyourbusiness.com> wrote in message
> news:uo2iGb6uEHA.1260@.TK2MSFTNGP12.phx.gbl...
SQL
>
sql

copying table data from 1 dbase to another

HI just had a question on this. I was able to copy the table using the
script, create in query analizer but am not quite sure how to copy the data.
I tried the (script object to window as Select) for the source data, and then
switched to the destination dbase and table and selected script object to new
window insert. For the insert I get the error though, Incorrect syntax near
'<'.,line 3, also not quite sure if this is the correct method to use.
this is the insert code created automatically that does not compile correctly.
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
VALUES(<Arrive_Depart_ID,int,>,
<Arrive_Depart_VC,varchar(50),>)
Paul G
Software engineer.
If the table is already on the OTHER database try
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.
|||You can use DTS to move tables and data between servers.
Since you already have the table created you could use DTS to move the data
OR
you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
in to the other server
OR
you could use a linked server or openrowset to select (and insert) the data
something like this (on the server that you are trying to populate)
INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
AnotherColumnName...)
SELECT A.ColumnName, A.AnotherColumnName...
FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
/*this next step is not needed if the destination table is truly empty*/
WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.
|||Several methods would work, in your example you are missing the SELECT
statement (see BOL INSERT statement for examples). If the column names are
the same on source and target, your SQL should look something like:
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
SELECT * FROM SourceTable
Steve
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
|||Hi thanks for the response, tried this,-DMLinter is the destination dbase
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
but get the error,
Cannot insert explicit value for identity column in table
'DML$Arrive_Depart_T' when IDENTITY_INSERT is set to OFF.
just wondering if you know how to set identity_insert to on?
"Wayne Snyder" wrote:

> If the table is already on the OTHER database try
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
>
|||The following seemed to work, both dbases are on the same server.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
Have not played around with DTS yet but seems pretty useful.
thanks.
"Keith Kratochvil" wrote:

> You can use DTS to move tables and data between servers.
> Since you already have the table created you could use DTS to move the data
> OR
> you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
> in to the other server
> OR
> you could use a linked server or openrowset to select (and insert) the data
> something like this (on the server that you are trying to populate)
> INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
> AnotherColumnName...)
> SELECT A.ColumnName, A.AnotherColumnName...
> FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
> /*this next step is not needed if the destination table is truly empty*/
> WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
> A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
|||This below seems to work, had to set the Identity_insert to on.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
"Steve Thompson" wrote:

> Several methods would work, in your example you are missing the SELECT
> statement (see BOL INSERT statement for examples). If the column names are
> the same on source and target, your SQL should look something like:
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> SELECT * FROM SourceTable
> Steve
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
>

copying table data from 1 dbase to another

HI just had a question on this. I was able to copy the table using the
script, create in query analizer but am not quite sure how to copy the data.
I tried the (script object to window as Select) for the source data, and then
switched to the destination dbase and table and selected script object to new
window insert. For the insert I get the error though, Incorrect syntax near
'<'.,line 3, also not quite sure if this is the correct method to use.
this is the insert code created automatically that does not compile correctly.
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
VALUES(<Arrive_Depart_ID,int,>,
<Arrive_Depart_VC,varchar(50),>)
--
Paul G
Software engineer.If the table is already on the OTHER database try
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.|||You can use DTS to move tables and data between servers.
Since you already have the table created you could use DTS to move the data
OR
you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
in to the other server
OR
you could use a linked server or openrowset to select (and insert) the data
something like this (on the server that you are trying to populate)
INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
AnotherColumnName...)
SELECT A.ColumnName, A.AnotherColumnName...
FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
/*this next step is not needed if the destination table is truly empty*/
WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.|||Several methods would work, in your example you are missing the SELECT
statement (see BOL INSERT statement for examples). If the column names are
the same on source and target, your SQL should look something like:
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
SELECT * FROM SourceTable
Steve
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)|||Hi thanks for the response, tried this,-DMLinter is the destination dbase
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
but get the error,
Cannot insert explicit value for identity column in table
'DML$Arrive_Depart_T' when IDENTITY_INSERT is set to OFF.
just wondering if you know how to set identity_insert to on?
"Wayne Snyder" wrote:
> If the table is already on the OTHER database try
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> > HI just had a question on this. I was able to copy the table using the
> > script, create in query analizer but am not quite sure how to copy the
> data.
> > I tried the (script object to window as Select) for the source data, and
> then
> > switched to the destination dbase and table and selected script object to
> new
> > window insert. For the insert I get the error though, Incorrect syntax
> near
> > '<'.,line 3, also not quite sure if this is the correct method to use.
> > this is the insert code created automatically that does not compile
> correctly.
> > INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> > ([Arrive_Depart_ID], [Arrive_Depart_VC])
> > VALUES(<Arrive_Depart_ID,int,>,
> > <Arrive_Depart_VC,varchar(50),>)
> > --
> > Paul G
> > Software engineer.
>
>|||The following seemed to work, both dbases are on the same server.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
Have not played around with DTS yet but seems pretty useful.
thanks.
"Keith Kratochvil" wrote:
> You can use DTS to move tables and data between servers.
> Since you already have the table created you could use DTS to move the data
> OR
> you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
> in to the other server
> OR
> you could use a linked server or openrowset to select (and insert) the data
> something like this (on the server that you are trying to populate)
> INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
> AnotherColumnName...)
> SELECT A.ColumnName, A.AnotherColumnName...
> FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
> /*this next step is not needed if the destination table is truly empty*/
> WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
> A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> > HI just had a question on this. I was able to copy the table using the
> > script, create in query analizer but am not quite sure how to copy the
> data.
> > I tried the (script object to window as Select) for the source data, and
> then
> > switched to the destination dbase and table and selected script object to
> new
> > window insert. For the insert I get the error though, Incorrect syntax
> near
> > '<'.,line 3, also not quite sure if this is the correct method to use.
> > this is the insert code created automatically that does not compile
> correctly.
> > INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> > ([Arrive_Depart_ID], [Arrive_Depart_VC])
> > VALUES(<Arrive_Depart_ID,int,>,
> > <Arrive_Depart_VC,varchar(50),>)
> > --
> > Paul G
> > Software engineer.
>|||This below seems to work, had to set the Identity_insert to on.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
"Steve Thompson" wrote:
> Several methods would work, in your example you are missing the SELECT
> statement (see BOL INSERT statement for examples). If the column names are
> the same on source and target, your SQL should look something like:
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> SELECT * FROM SourceTable
> Steve
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> > HI just had a question on this. I was able to copy the table using the
> > script, create in query analizer but am not quite sure how to copy the
> data.
> > I tried the (script object to window as Select) for the source data, and
> then
> > switched to the destination dbase and table and selected script object to
> new
> > window insert. For the insert I get the error though, Incorrect syntax
> near
> > '<'.,line 3, also not quite sure if this is the correct method to use.
> > this is the insert code created automatically that does not compile
> correctly.
> > INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> > ([Arrive_Depart_ID], [Arrive_Depart_VC])
> > VALUES(<Arrive_Depart_ID,int,>,
> > <Arrive_Depart_VC,varchar(50),>)
>
>

copying table data from 1 dbase to another

HI just had a question on this. I was able to copy the table using the
script, create in query analizer but am not quite sure how to copy the data.
I tried the (script object to window as Select) for the source data, and the
n
switched to the destination dbase and table and selected script object to ne
w
window insert. For the insert I get the error though, Incorrect syntax near
'<'.,line 3, also not quite sure if this is the correct method to use.
this is the insert code created automatically that does not compile correctl
y.
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
VALUES(<Arrive_Depart_ID,int,>,
<Arrive_Depart_VC,varchar(50),> )
--
Paul G
Software engineer.If the table is already on the OTHER database try
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocald
atabase
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),> )
> --
> Paul G
> Software engineer.|||You can use DTS to move tables and data between servers.
Since you already have the table created you could use DTS to move the data
OR
you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
in to the other server
OR
you could use a linked server or openrowset to select (and insert) the data
something like this (on the server that you are trying to populate)
INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
AnotherColumnName...)
SELECT A.ColumnName, A.AnotherColumnName...
FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
/*this next step is not needed if the destination table is truly empty*/
WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),> )
> --
> Paul G
> Software engineer.|||Several methods would work, in your example you are missing the SELECT
statement (see BOL INSERT statement for examples). If the column names are
the same on source and target, your SQL should look something like:
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
SELECT * FROM SourceTable
Steve
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),> )|||Hi thanks for the response, tried this,-DMLinter is the destination dbase
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
but get the error,
Cannot insert explicit value for identity column in table
'DML$Arrive_Depart_T' when IDENTITY_INSERT is set to OFF.
just wondering if you know how to set identity_insert to on?
"Wayne Snyder" wrote:

> If the table is already on the OTHER database try
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinloca
ldatabase
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
>|||The following seemed to work, both dbases are on the same server.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
Have not played around with DTS yet but seems pretty useful.
thanks.
"Keith Kratochvil" wrote:

> You can use DTS to move tables and data between servers.
> Since you already have the table created you could use DTS to move the dat
a
> OR
> you could BCP / BULK COPY the data out of one server and BCP or BULK COPY
it
> in to the other server
> OR
> you could use a linked server or openrowset to select (and insert) the dat
a
> something like this (on the server that you are trying to populate)
> INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
> AnotherColumnName...)
> SELECT A.ColumnName, A.AnotherColumnName...
> FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
> /*this next step is not needed if the destination table is truly empty*/
> WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
> A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>|||This below seems to work, had to set the Identity_insert to on.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
"Steve Thompson" wrote:

> Several methods would work, in your example you are missing the SELECT
> statement (see BOL INSERT statement for examples). If the column names are
> the same on source and target, your SQL should look something like:
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> SELECT * FROM SourceTable
> Steve
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> data.
> then
> new
> near
> correctly.
>
>

Copying grid results with column headers

Is there any way to copy grid query results with the column headers? When I
copy and paste the results into Excel now I have to manually type the
headers so I know what I'm looking at.
David,
if you are referring to Query Analyser, you can change the output format to
"Results to File...". On Tools, Options, Results, you can select to take the
column headers and select csv, column aligned etc formats. If this is a
regular process, you might like to do this in a DTS package.
HTH,
Paul Ibison
|||Do you know if there's a way to enable copying the column headers from the
grid results though? I may want to actually see the results visually before
deciding I want to copy it, making the Resuts to File a less appealing
option. If it's a long running query, running to grid first then again to
file would double the time. I'll paste the results into Excel.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaanEGlSEHA.3768@.TK2MSFTNGP11.phx.gbl...
> David,
> if you are referring to Query Analyser, you can change the output format
to
> "Results to File...". On Tools, Options, Results, you can select to take
the
> column headers and select csv, column aligned etc formats. If this is a
> regular process, you might like to do this in a DTS package.
> HTH,
> Paul Ibison
>
|||David,
not directly as far as I know. 'Results to text' will give you visual output
with column headers, but it usually won'y format correctly in Excel.
Regards,
Paul Ibison
|||Why not go into EXCEL and IMPORT DATA - use a NEW SQL CONNECTION and get to your DATABASE.
Then from in there you can load a table, execute a query, etc.
It will load right into the EXCEL grids - headings and all - and if you like what you see, save as XLS or CSV or TDF or whatever you want - EXCEL does them all.

Copying grid results with column headers

Is there any way to copy grid query results with the column headers? When I
copy and paste the results into Excel now I have to manually type the
headers so I know what I'm looking at.David,
if you are referring to Query Analyser, you can change the output format to
"Results to File...". On Tools, Options, Results, you can select to take the
column headers and select csv, column aligned etc formats. If this is a
regular process, you might like to do this in a DTS package.
HTH,
Paul Ibison|||Do you know if there's a way to enable copying the column headers from the
grid results though? I may want to actually see the results visually before
deciding I want to copy it, making the Resuts to File a less appealing
option. If it's a long running query, running to grid first then again to
file would double the time. I'll paste the results into Excel.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaanEGlSEHA.3768@.TK2MSFTNGP11.phx.gbl...
> David,
> if you are referring to Query Analyser, you can change the output format
to
> "Results to File...". On Tools, Options, Results, you can select to take
the
> column headers and select csv, column aligned etc formats. If this is a
> regular process, you might like to do this in a DTS package.
> HTH,
> Paul Ibison
>|||David,
not directly as far as I know. 'Results to text' will give you visual output
with column headers, but it usually won'y format correctly in Excel.
Regards,
Paul Ibison|||Why not go into EXCEL and IMPORT DATA - use a NEW SQL CONNECTION and get to
your DATABASE.
Then from in there you can load a table, execute a query, etc.
It will load right into the EXCEL grids - headings and all - and if you like
what you see, save as XLS or CSV or TDF or whatever you want - EXCEL does t
hem all.

Copying grid results with column headers

Is there any way to copy grid query results with the column headers? When I
copy and paste the results into Excel now I have to manually type the
headers so I know what I'm looking at.David,
if you are referring to Query Analyser, you can change the output format to
"Results to File...". On Tools, Options, Results, you can select to take the
column headers and select csv, column aligned etc formats. If this is a
regular process, you might like to do this in a DTS package.
HTH,
Paul Ibison|||Do you know if there's a way to enable copying the column headers from the
grid results though? I may want to actually see the results visually before
deciding I want to copy it, making the Resuts to File a less appealing
option. If it's a long running query, running to grid first then again to
file would double the time. I'll paste the results into Excel.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaanEGlSEHA.3768@.TK2MSFTNGP11.phx.gbl...
> David,
> if you are referring to Query Analyser, you can change the output format
to
> "Results to File...". On Tools, Options, Results, you can select to take
the
> column headers and select csv, column aligned etc formats. If this is a
> regular process, you might like to do this in a DTS package.
> HTH,
> Paul Ibison
>|||David,
not directly as far as I know. 'Results to text' will give you visual output
with column headers, but it usually won'y format correctly in Excel.
Regards,
Paul Ibison|||Why not go into EXCEL and IMPORT DATA - use a NEW SQL CONNECTION and get to your DATABASE
Then from in there you can load a table, execute a query, etc
It will load right into the EXCEL grids - headings and all - and if you like what you see, save as XLS or CSV or TDF or whatever you want - EXCEL does them all.

Copying files between servers

I'm trying to copy files between 2 servers on a local network from within a
SQL Job (and Query Analyzer) using xp_cmdshell.xcopy but get an access
denied message returned.

I'm able to successfully do the copy from within a command window so think
the problem has something to do with using the default SQL Server account
but as yet I don't know how to resolve.

Any help/suggestions would be much appreciated.Am guessng that you are running MS-SQL using the local SYSTEM account.
System does not have access to network devices.

Your two options are to create another account and configure MS-SQL and
agent to use that account. You may beable to get away with just
configuring agent for that but depends on how you are doing the
command.

Or to go into policy editor and allowing the system account to have
netowrk priviledges. This is a major security hole and should not be
done.

Copying data from Query Analyser.

When copying data from QA, is it possible to copy the headers with the data?
I tried Select All then Copy but got just the data.
Regards
Thief_did you hear "Thief_" <thief_@.hotmail.com> say in
news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:

> When copying data from QA, is it possible to copy the headers with the
> data? I tried Select All then Copy but got just the data.
> Regards
> Thief_
>
if we're talking about column headers with the data, if you are querying
using grid view that's the best you can get. If you switch to text view
or send the results to a file (CSV or text) you will get the column
headings.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hi ,

>From QA u will not be able to get the header name but if u tranfer ur
data to text file or excel file or access file u can get the table name
as well as the table structure.
HTH
from
Doller
Neil MacMurchy wrote:
> did you hear "Thief_" <thief_@.hotmail.com> say in
> news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
>
> if we're talking about column headers with the data, if you are querying
> using grid view that's the best you can get. If you switch to text view
> or send the results to a file (CSV or text) you will get the column
> headings.
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs|||doller wrote:
> Hi ,
>
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
>
> Neil MacMurchy wrote:
>
If you use SQL Server Management Studio (Which is the "query analyzer"
that comes with SQL 2005), you can actually choose to include Column
Headers when you copy data from QA.
Regards
STeen|||did you hear "doller" <sufianarif@.gmail.com> say in
news:1127707753.325611.236790@.g43g2000cwa.googlegroups.com:

> Hi ,
>
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
I think we need to clear up the term 'header'. Are we speaking of column
headings? if we are choose "results in text" from the query menu in QA.
this will allow the full columns to be selected for editing (copy/paste)or
a report saved to a file. You can also choose the "results to file" option
to save the output to a csv file. you made need to modify the options for
your results to get them in the format you would like, but these can be
chosen from tools --> option --> results (for more details check "Managing
SQL Query Analyzer Windows" from books online).
the other type of header might be in reference to a backup. perhaps this
was in reference to extend properties of a database (the
fn_listextendedproperties), but my guess is that this is what I have
mentioned above.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
#HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:

> If you use SQL Server Management Studio (Which is the "query analyzer"
> that comes with SQL 2005), you can actually choose to include Column
> Headers when you copy data from QA.
>
you can do that in 2000 as well. just don't do a "select *" ;)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Neil MacMurchy wrote:
> did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
> #HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
>
> you can do that in 2000 as well. just don't do a "select *" ;)
>
Where can you specify that in Query Analyzer?
Regards
Steen|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in
news:urWES4zwFHA.904@.tk2msftngp13.phx.gbl:

> Where can you specify that in Query Analyzer?
select col1, col2, col3...
(it was a joke buddy...)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs

Copying data from Query Analyser.

When copying data from QA, is it possible to copy the headers with the data?
I tried Select All then Copy but got just the data.
Regards
Thief_did you hear "Thief_" <thief_@.hotmail.com> say in
news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
> When copying data from QA, is it possible to copy the headers with the
> data? I tried Select All then Copy but got just the data.
> Regards
> Thief_
>
if we're talking about column headers with the data, if you are querying
using grid view that's the best you can get. If you switch to text view
or send the results to a file (CSV or text) you will get the column
headings.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hi ,
>From QA u will not be able to get the header name but if u tranfer ur
data to text file or excel file or access file u can get the table name
as well as the table structure.
HTH
from
Doller
Neil MacMurchy wrote:
> did you hear "Thief_" <thief_@.hotmail.com> say in
> news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
> > When copying data from QA, is it possible to copy the headers with the
> > data? I tried Select All then Copy but got just the data.
> >
> > Regards
> >
> > Thief_
> >
> >
> if we're talking about column headers with the data, if you are querying
> using grid view that's the best you can get. If you switch to text view
> or send the results to a file (CSV or text) you will get the column
> headings.
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs|||doller wrote:
> Hi ,
>>From QA u will not be able to get the header name but if u tranfer ur
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
>
> Neil MacMurchy wrote:
>> did you hear "Thief_" <thief_@.hotmail.com> say in
>> news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
>> When copying data from QA, is it possible to copy the headers with the
>> data? I tried Select All then Copy but got just the data.
>> Regards
>> Thief_
>>
>> if we're talking about column headers with the data, if you are querying
>> using grid view that's the best you can get. If you switch to text view
>> or send the results to a file (CSV or text) you will get the column
>> headings.
>> --
>> Neil MacMurchy
>> http://spaces.msn.com/members/neilmacmurchy
>> http://spaces.msn.com/members/mctblogs
>
If you use SQL Server Management Studio (Which is the "query analyzer"
that comes with SQL 2005), you can actually choose to include Column
Headers when you copy data from QA.
Regards
STeen|||did you hear "doller" <sufianarif@.gmail.com> say in
news:1127707753.325611.236790@.g43g2000cwa.googlegroups.com:
> Hi ,
>>From QA u will not be able to get the header name but if u tranfer ur
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
I think we need to clear up the term 'header'. Are we speaking of column
headings? if we are choose "results in text" from the query menu in QA.
this will allow the full columns to be selected for editing (copy/paste)or
a report saved to a file. You can also choose the "results to file" option
to save the output to a csv file. you made need to modify the options for
your results to get them in the format you would like, but these can be
chosen from tools --> option --> results (for more details check "Managing
SQL Query Analyzer Windows" from books online).
the other type of header might be in reference to a backup. perhaps this
was in reference to extend properties of a database (the
fn_listextendedproperties), but my guess is that this is what I have
mentioned above.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
#HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
> If you use SQL Server Management Studio (Which is the "query analyzer"
> that comes with SQL 2005), you can actually choose to include Column
> Headers when you copy data from QA.
>
you can do that in 2000 as well. just don't do a "select *" ;)
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Neil MacMurchy wrote:
> did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
> #HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
>> If you use SQL Server Management Studio (Which is the "query analyzer"
>> that comes with SQL 2005), you can actually choose to include Column
>> Headers when you copy data from QA.
> you can do that in 2000 as well. just don't do a "select *" ;)
>
Where can you specify that in Query Analyzer?
Regards
Steen|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in
news:urWES4zwFHA.904@.tk2msftngp13.phx.gbl:
> Where can you specify that in Query Analyzer?
select col1, col2, col3...
(it was a joke buddy...)
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs

Copying data from Query Analyser.

When copying data from QA, is it possible to copy the headers with the data?
I tried Select All then Copy but got just the data.
Regards
Thief_
did you hear "Thief_" <thief_@.hotmail.com> say in
news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:

> When copying data from QA, is it possible to copy the headers with the
> data? I tried Select All then Copy but got just the data.
> Regards
> Thief_
>
if we're talking about column headers with the data, if you are querying
using grid view that's the best you can get. If you switch to text view
or send the results to a file (CSV or text) you will get the column
headings.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Hi ,

>From QA u will not be able to get the header name but if u tranfer ur
data to text file or excel file or access file u can get the table name
as well as the table structure.
HTH
from
Doller
Neil MacMurchy wrote:
> did you hear "Thief_" <thief_@.hotmail.com> say in
> news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
>
> if we're talking about column headers with the data, if you are querying
> using grid view that's the best you can get. If you switch to text view
> or send the results to a file (CSV or text) you will get the column
> headings.
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
|||doller wrote:
> Hi ,
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
>
> Neil MacMurchy wrote:
>
If you use SQL Server Management Studio (Which is the "query analyzer"
that comes with SQL 2005), you can actually choose to include Column
Headers when you copy data from QA.
Regards
STeen
|||did you hear "doller" <sufianarif@.gmail.com> say in
news:1127707753.325611.236790@.g43g2000cwa.googlegr oups.com:

> Hi ,
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
I think we need to clear up the term 'header'. Are we speaking of column
headings? if we are choose "results in text" from the query menu in QA.
this will allow the full columns to be selected for editing (copy/paste)or
a report saved to a file. You can also choose the "results to file" option
to save the output to a csv file. you made need to modify the options for
your results to get them in the format you would like, but these can be
chosen from tools --> option --> results (for more details check "Managing
SQL Query Analyzer Windows" from books online).
the other type of header might be in reference to a backup. perhaps this
was in reference to extend properties of a database (the
fn_listextendedproperties), but my guess is that this is what I have
mentioned above.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
#HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:

> If you use SQL Server Management Studio (Which is the "query analyzer"
> that comes with SQL 2005), you can actually choose to include Column
> Headers when you copy data from QA.
>
you can do that in 2000 as well. just don't do a "select *" ;)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Neil MacMurchy wrote:
> did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
> #HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
>
> you can do that in 2000 as well. just don't do a "select *" ;)
>
Where can you specify that in Query Analyzer?
Regards
Steen
|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in
news:urWES4zwFHA.904@.tk2msftngp13.phx.gbl:

> Where can you specify that in Query Analyzer?
select col1, col2, col3...
(it was a joke buddy...)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs

copying a table from one database to another

Hey

in query analyzer, how do you copy a table form one db to another db

i thort it was something like

select * into dbo.databaseA.tableNew from dbo.databaseB.tableOld

cheers

insert into databaseA..tableNewselect *From databaseb..tableOld

|||

The difference between SELECT INTO and INSERT INTO is that with INSERT the table must already exist. SELECT INTO creates a new table.

Your original query looked okay, assuming that you wanted a new table tableNew. What error were you getting? You might also have a permissions problem since you are going from one database to another.

Don

|||

hey

yeah... thats why i 'd like to use select into or otherwise i'll have to create the other table ( not as fast )

when i try to run

select * into dbo.databaseA.tableNew from dbo.databaseB.tableOld

i get

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name dbo.databaseB.tableOld

i've tripple checked the spelling and tried the same thing with other databases on other computers and got the same error so i'm sure its not a permission error or anything, must be syntax

cheers

|||Use this:select * into databaseA..tableNew from databaseB..tableOld|||

Matt-dot-net:

Use this:select * into databaseA..tableNew from databaseB..tableOld

<groan> I HATE when I miss things like that!

Don

|||

cheers bruva, just what i needed