(newbie) copying rows between tables via notepad

Hi
newbie question I'm sure
I have 2 databases on 2 different machines, there is no
connection between the 2. What I need is a way of
extracting particular rows from particular tables and adding them
to another database. The only viable way of doing this is via
email for unimportant reasons.
Via query analyser I can do a select and get the results in
text then manually adjust these back into an insert statement.
There must be a better way but I'm too new to this to know how
Any ideas?

Thanks
Fhttp://vyaskn.tripod.com/code.htm#inserts

--
David Portas
SQL Server MVP
--|||You can use bcp.exe or DTS to export the data to file, then the same
mechanism to import them at the destination - see Books Online for the
details. Or if you want to use INSERTs, then check this out:

http://vyaskn.tripod.com/code.htm#inserts

Simon|||Simon Hayes wrote:
> You can use bcp.exe or DTS to export the data to file, then the same
> mechanism to import them at the destination - see Books Online for
the
> details. Or if you want to use INSERTs, then check this out:
> http://vyaskn.tripod.com/code.htm#inserts
> Simon

Thanks for the reply, this looked like it would have been ideal but
this is the output I get, it gets cut off (column names changed to
protect
the innocent :-))

INSERT INTO [XXXXXXXXXXXXXX]
([XXXXXXXXXXXXX],[XXXXXXXXXXXX],[XXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXX],[XXXXXXXXXXX],[XXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXXX])VALUES('7CC60B79-1B27-4|||(foldface@.yahoo.co.uk) writes:
> Thanks for the reply, this looked like it would have been ideal but
> this is the output I get, it gets cut off (column names changed to
> protect

Sounds like you should go into to Tools->Options->Results and adjust
"Maximum characters per column".

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the reply, yes, that was it, it was in the faq for that SP
as well but I assumed it was the SP that wasn't working because I
briefly saw this comment, huge is a relative thing!
Works fine now, very handy

NOTE:This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns

"Views"(Edited)

Hai,
I have a table named "Student" which contains four columns and three rows.I want to see just the first row only from the "Student" table and the marks should be calculated.
Here is my table
sno smark1 smark2 smark3
1 60 60 60
2 70 70 70
3 90 90 90

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

This would give you the first row based on just the sno. Depending on if you would want to see row 1 or 3 would be dependent on the order by. So to get sno[1] use "order by sno" alternately for the last sno[3] "order by sno desc".

Code Snippet

Select top 1 sno from student order by sno

or

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

sql

"Views"(Edited)

Hai,
I have a table named "Student" which contains four columns and three rows.I want to see just the first row only from the "Student" table and the marks should be calculated.
Here is my table
sno smark1 smark2 smark3
1 60 60 60
2 70 70 70
3 90 90 90

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

This would give you the first row based on just the sno. Depending on if you would want to see row 1 or 3 would be dependent on the order by. So to get sno[1] use "order by sno" alternately for the last sno[3] "order by sno desc".

Code Snippet

Select top 1 sno from student order by sno

or

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

"Views"

Hai,
I have a table named "Student" which contains four columns and three rows.I want to see just the first row only from the "Student" table and the marks should be calculated.
Here is my table
sno smark1 smark2 smark3
1 60 60 60
2 70 70 70
3 90 90 90

The Ouput Should be:
sno smarks(smark1+ smark2+smark3)
1 180
2 210
3 270

I can't get ur question!!

What is the result u expecting from this table?

If you need only the first row,

Select Top 1 * from Student

|||

This would give you the first row based on just the sno. Depending on if you would want to see row 1 or 3 would be dependent on the order by. So to get sno[1] use "order by sno" alternately for the last sno[3] "order by sno desc".

Code Snippet

Select top 1 sno from student order by sno

or

Select top 1 sno from student order by sno desc

|||

You can do this by simply:

select sno, coalesce(smark1,0) + coalesce(smark2,0) + coalesce(smark3,0) as smarks

from yourTable

The coalesces are done to remove NULLS from the math.

In reality, it would be better if you build your table as:

studentMarks

============

studentNumber

sequenceNumber

dateOfScore

mark

Then you can sum any number of marks like this:

select studentNumber, sum(mark)

from studentMarks

group by studentNumber

"Slipt" rows based on datetime

Hello!

I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table:

row1:

initial_time: 2006-05-24 8:00:00

final_time: 2006-05-24 8:30:00

row2:

initial_time: 2006-05-24 8:35:00

final_time: 2006-05-24 9:15:00

I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into:

row2_a:

initial_time: 2006-05-24 8:35:00

initial_time: 2006-05-24 8:59:59

row2_b:

initial_time: 2006-05-24 9:00:00

initial_time: 2006-05-24 9:15:00

Is it possible to do it in a query, I mean, without using procedures?

Thank you!

? It gets a bit complex, but here's one way: create table #table ( initial_time datetime, final_time datetime) insert #table values( '2006-05-24 8:00:00', '2006-05-24 8:30:00') insert #table values( '2006-05-24 8:35:00', '2006-05-24 9:15:00') SELECT CASE x.N WHEN 0 THEN T.initial_time WHEN 1 THEN DATEADD(ms, ((DATEPART(minute, T.final_time) * 60000) + (DATEPART(second, T.final_time) * 1000) + DATEPART(millisecond, T.final_time)) * -1, T.final_time) END AS initial_time, CASE x.N WHEN 0 THEN CASE DATEDIFF(hour, T.initial_time, T.final_time) WHEN 0 THEN T.final_time WHEN 1 THEN DATEADD(millisecond, -3, DATEADD(millisecond, ((DATEPART(minute, T.final_time) * 60000) + (DATEPART(second, T.final_time) * 1000) + DATEPART(millisecond, T.final_time)) * -1, T.final_time)) END WHEN 1 THEN T.final_time END AS final_timeFROM #table TJOIN( SELECT 0 UNION ALL SELECT 1) x (N) ON x.N <= DATEDIFF(hour, T.initial_time, T.final_time) drop table #tablego -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <AnaC@.discussions.microsoft..com> wrote in message news:bdb330d8-5a34-409c-aaf3-57044628fc13@.discussions.microsoft.com... Hello! I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table: row1: initial_time: 2006-05-24 8:00:00 final_time: 2006-05-24 8:30:00 row2: initial_time: 2006-05-24 8:35:00 final_time: 2006-05-24 9:15:00 I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into: row2_a: initial_time: 2006-05-24 8:35:00 initial_time: 2006-05-24 8:59:59 row2_b: initial_time: 2006-05-24 9:00:00 initial_time: 2006-05-24 9:15:00 Is it possible to do it in a query, I mean, without using procedures? Thank you!|||Thank you!

"Select top (n) from <table>" missing from Management Studio

In sql 2000 one could right-click a table (under Enterprise Manager) and have it return the "top n" rows in the table. That feature seems to be missing from Management Studio. Am I correct or is it just hiding somewhere?

TIA,

Barkingdog

No, its definitely not there, you will have to tweak the SQL Statement by clicking the appropiate item in the iconbar.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

>>you will have to tweak the SQL Statement by clicking the appropiate item in the iconbar.
>>

What "item" are you referring to? The only way I can see to do this is open a new Query Editor pane and type the sql directly.

TIA,

Barkingdog

|||If you open a table you will have an icon with "SQL" written on it, you can change to the SQL Statement whcih was executed to have the data displayed, which is normally SELECT * From SomeTable and can change it the way you want to query the data.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

"Rows into Columns and Columns into Rows"

I have data in a table. I want the values in the rows to place in columns and columns into rows.
Eg:-A table. It consists of three columns and three rows.

name id dept
a 1 x
b 2 y
c 3 z

I want the resultant table should look like this

a b c
1 2 3
x y z

Whether it's possible ?

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326394&SiteID=1

You will need to add some value to the rows to uniquely identify the rows when you pivot (but you could do this in a CTE or temp table.

"OnGroupChange" event?

I find that I regularly need to evaluate all the rows in "data groups" within the pipeline and execute script based transform logic for each grouping (aggregate transform does not provide script access to the data).

I do this in script by sorting the pipelined data on some key columns to define the groupings, caching the key values as the rows pass through the ProcessInputRow procedure, and running logic that compares the current row's key values as each row passes through the transform with the previous row's key values. When they differ I know I have a new group (special casing for the very first row in the pipleine), so I perform my transforms with cached data from the previous group .

This works but I am thinking this must be a common "pattern". Also, I have experience with a competing ETL tool which has "built in" support for script processing of user defined data groupings. That product provides an "OnChange" event, where the user defines the key in the GUI (can be composite), and the event provides a container to hold script (with access to the row data values) to run when the event "fires"(any values change in the key vs. the previous). Of course the data stream again has to be sorted for this to work.

Anyone else regularly have a need to do this type of processing? If so perhaps it is a good candidate for a custom component? Or a suggestion to Microsoft to enhance the aggregate component for the next SSIS version to provide a script "hook" in the aggregate transform? Anyone else regularly have this type of processing requirement?

Ken

Yeah, this would be a useful feature, but it looks like someone will have to write a custom component!

#of rows updated

Is there a command that will tell me the number of rows that are updated in a statement. I would like to put this in an Stored Procedure and pass the #rows updated back out.you can just look at @.@.ROWCOUNT after you do a statment and it will tell you how many rows were affected by the last SQL statement.

i.e., SELECT @.@.ROWCOUNT

or better yet, put it into a local variable such as:

DECLARE @.RowCountToReturn int

SELECT * FROM dbo.MyTable
SET @.RowCountToReturn = @.@.ROWCOUNTKeep in mind that pretty much EVERY SQL statement changes the value of @.@.ROWCOUNT, so you need to save it off if you have any code that may change it before you return it to your calling procedure.