Correct syntax

Hello

I have having trouble displaying some simple columns in ascending order.

I know that the database is populated and I can get the more complex code to work if I display like this:

SELECT FName, LName, Town, '<a href="http://links.10026.com/?link=' + url + '">' + Site + '</a>' as Link
FROM Names_DB
WHERE FName = 'Tom'
And url like 'http:%'
ORDER BY LName ASC

But I need a simpler view but I can't get it to work

I have tried this:

SELECT FName, LName

FROM Names_DB

ORDER BY LName ASC

And this

SELECT FName, LName

FROM Names_DB

ORDER BY LName ASC;

And This:

SELECT FName, LName
FROM Names_DB

ORDER BY LName 'ASC'

What is wrong with this syntax?

Thanks

Lynn

Change 'ASC' to ASC,it works well.|||

Hi Jason

Thanks for the reply and the info.

I have been trying various combinations to get it to work. At last I have eventually found a syntax that works also, about the same time I got your reply.

I just put : after the ORDER BY

ORDER BY: LName ASC

How frustrating searching for the single character to make everything happen.

Lynn

Correalated sub...

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

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

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

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

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

Copying 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 specific data from table in DB1 to table in DB2

I need to copy the following columns from my Employee table in my Performance DB to my Employee table in my VacationRequest DB:
CompanyID, FacilityID, EmployeeID, FirstName, LastName,
[Password] = 'nippert', Role = 'Employee'

I tried the advice on this website but to no avail:
http://www.w3schools.com/sql/sql_select_into.aspHi Jason,INSERT INTO should fit your needs.

Copying Row Data within the same table

I have the following table:

Table name: RR

columns:

Subject (varchar (35), Null)

Topic (varchar (35), Null)

RD (text, null)

RR (text, null)

Picture (varchar (50), Null)

Video (varchar (50), Null)

RRID (int, Not Null)

TSTAMP (datetime, Null)

RRCount (int, Not Null)

This table stores common information used in resolving technical problems based on Subject and Topic. However, I've now created a Subject/Topic where I want to copy all the data that corresponds to another Subject/topic.

Example:

There are 35 rows that correspond to Subject = 'Publisher01' and Topic = 'Subcategory03'. I want to create 35 new rows that contain the same RD and RR data, but have Subject = 'Publisher02' and Topic = 'Subcategory07'. Highest current RRID = 5008

I cannot figure out how to write that query. I apologize in advance for the fact that this is, no doubt, a seriously beginner question.

Hi,

would be nice to have some DDL on hand to see your additional table information and some expected results, but anyway:

if you just want to copy these rows (I don′t know what you mean by RRID ?!) the easiest insert statement is:

INSERT INTO RR
(...collist....,Subject,Topic )
SELECT
...collist...,'Publisher02','Subcategory07'
FROM RR
WHERE = 'Publisher01' and
Topic = 'Subcategory03'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Copying database from 2005 -> 2005 - loses things like identity columns.

I am copy a database from one server to another and when I do, I lose all of
my identity column specifications. The column is still there, but it's no
longer an identity column.
Am I missing something?
TIA - Jeff.
Using the Copy Database Wizard.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45083C25.3090206@.realsqlguy.com...
> UJ wrote:
> How are you performing the copy?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copying database from 2005 -> 2005 - loses things like identity columns.

I am copy a database from one server to another and when I do, I lose all of
my identity column specifications. The column is still there, but it's no
longer an identity column.
Am I missing something?
TIA - Jeff.UJ wrote:
> I am copy a database from one server to another and when I do, I lose all of
> my identity column specifications. The column is still there, but it's no
> longer an identity column.
> Am I missing something?
> TIA - Jeff.
>
How are you performing the copy?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Using the Copy Database Wizard.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45083C25.3090206@.realsqlguy.com...
> UJ wrote:
>> I am copy a database from one server to another and when I do, I lose all
>> of my identity column specifications. The column is still there, but it's
>> no longer an identity column.
>> Am I missing something?
>> TIA - Jeff.
>>
> How are you performing the copy?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||UJ wrote:
> Using the Copy Database Wizard.
>
Ok... Hard to tell what that thing does... You'd be better off using
one of the more accepted methods:
- backup/restore
- detach/attach
Either method will guarantee that you get an exact copy of the database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Copying database from 2005 -> 2005 - loses things like identity columns.

I am copy a database from one server to another and when I do, I lose all of
my identity column specifications. The column is still there, but it's no
longer an identity column.
Am I missing something?
TIA - Jeff.Using the Copy Database Wizard.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45083C25.3090206@.realsqlguy.com...
> UJ wrote:
> How are you performing the copy?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copying columns to table with different design

I am working with a SQL database that was migrated from MS Access and adapted for full-text search which involved creating a new table with a different design.I would like to copy three columns (YearGiven, MonthGiven, DayGiven) from the Documents table to the newly-created FullDocuments table so that I can delete the Documents table and four other tables with redundant data.Here are the two tables involved in the column copy:

Documents Table

DocID (Primary Key)

DocNo

SequenceNo

YearGiven

MonthGiven

DayGiven

FullDocuments Table

FullDocID (Primary Key)

DocNo

SequenceNo

SectionText

YearGiven (empty – no data)

MonthGiven (empty – no data)

DayGiven (empty – no data)

After the copy column procedure I want the FullDocuments table structure to look like this:

FullDocuments Table

FullDocID (Primary Key)

DocNo

SequenceNo

SectionText

YearGiven (full of transferred data)

MonthGiven (full of transferred data)

DayGiven (full of transferred data)

The problem is that the FullDocuments table contains approximately 4x as many rows as the Documents table.This is because each document has four types – the primary Text document and three supplemental documents (Background, Report, and Index).Whereas the Documents table has one row for each Document, the Full Documents table has four rows per document (the four document types).Instead of simply doing a copy and paste of columns based on a primary key and foreign key relationship (as described in the MSDN online books), I need to copy and paste based on the DocNo and SequenceNo columns.Thus the same date data (Year, Month, Day) will need to occupy four consecutive rows for each document in the FullDocuments table (as is now the case with the DocNo and SequenceNo).

I will end up with one table that meets the requirements for the full-text search (primary key and all the text to be searched) and eliminate five tables (Documents and the four document type text tables).This is a static database consisting of historical records so I am not concerned about input errors that can be associated with denormalization.

I tried exporting the data using the Export Wizard using the “Copy data from one or more tables” option.The transfer failed.The most relevant lines of the Error Report are:

·Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "The statement has been terminated.".
An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Cannot insert the value NULL into column 'FullDocumentID', table 'Documents.dbo.FullDocuments'; column does not allow nulls. INSERT fails.".
(SQL Server Import and Export Wizard)

·Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - FullDocuments" (61) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)

·Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
(SQL Server Import and Export Wizard)

I then tried the “Write a query to specify the data to transfer” option focusing on just the YearGiven column using various FETCH, INSERT AND UPDATE commands without any luck.Questions:

1.Is the query approach the best way to achieve my objective, or did I miss something in the “Copy data” option in the Export Wizard?

2.If the query approach is the best way, any suggestions on what the query will look like?

Thanks for any help you can provide.

It sounds as though you have a pretty complicated requirement here and therefore I would suggest you try to build a package using BIDS rather than using the import/export wizard.

-Jamie

|||Jamie: Thanks for the suggestion. It would never have occurred to me to use the Business Intelligence Development Studio for this purpose since I think of BIDS as a report generator instead of a tool for moving data around in a database. I'll have to look into this option.|||

BIDS is the tool for building Reporting Services reports, Analysis Services cubes, and Integration Services packages. Hence its a tool for building BI components - hence its name.

-Jamie

Copying columns in one table in a DB to another Table in another DB

I am trying to copy two columns (OC_ID_ & OC_NAME) in one table (ORG_CHARGE)
in a SQL SRV 05 DB (VFM) into two columns (DEPT_NUM and DEPT) in another
table (DEPT) in another SQL SRV 05 DB (TRACK IT). I have to accomplish the
following with the data:
* Update if the record exists
* Add a new record in TrackIT if it does not exist
* Delete from TrackIT when it is Deleted in VFM
I have tried using the DTS export tool but end up with an error that says...
INSERT fails because it cannot insert NULL values into the column DEPT_ID.
I realize that this that i have to add an integer to increase the index of
the DEPT_ID if there are new records going into it and I have put the
necessary code to do so but I still get the error.
Any thoughts?
BFuenz
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200702/1
You can write stored procedure something like that
CREATE PROC dbo.mysp
AS
IF EXISTS (SELECT * FROM Table WHERE.....)
BEGIN
UPDATE Table SET ....
DELETE FROM Table....
END
ELSE
BEGIN
INSERT INTO Table......
END
"bfuenz via droptable.com" <u31754@.uwe> wrote in message
news:6e7fe74532642@.uwe...
>I am trying to copy two columns (OC_ID_ & OC_NAME) in one table
>(ORG_CHARGE)
> in a SQL SRV 05 DB (VFM) into two columns (DEPT_NUM and DEPT) in another
> table (DEPT) in another SQL SRV 05 DB (TRACK IT). I have to accomplish
> the
> following with the data:
> * Update if the record exists
> * Add a new record in TrackIT if it does not exist
> * Delete from TrackIT when it is Deleted in VFM
> I have tried using the DTS export tool but end up with an error that
> says...
> INSERT fails because it cannot insert NULL values into the column DEPT_ID.
> I realize that this that i have to add an integer to increase the index of
> the DEPT_ID if there are new records going into it and I have put the
> necessary code to do so but I still get the error.
> Any thoughts?
> BFuenz
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200702/1
>

Copying columns in one table in a DB to another Table in another DB

I am trying to copy two columns (OC_ID_ & OC_NAME) in one table (ORG_CHARGE)
in a SQL SRV 05 DB (VFM) into two columns (DEPT_NUM and DEPT) in another
table (DEPT) in another SQL SRV 05 DB (TRACK IT). I have to accomplish the
following with the data:
* Update if the record exists
* Add a new record in TrackIT if it does not exist
* Delete from TrackIT when it is Deleted in VFM
I have tried using the DTS export tool but end up with an error that says...
INSERT fails because it cannot insert NULL values into the column DEPT_ID.
I realize that this that i have to add an integer to increase the index of
the DEPT_ID if there are new records going into it and I have put the
necessary code to do so but I still get the error.
Any thoughts?
BFuenz
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200702/1You can write stored procedure something like that
CREATE PROC dbo.mysp
AS
IF EXISTS (SELECT * FROM Table WHERE.....)
BEGIN
UPDATE Table SET ....
DELETE FROM Table....
END
ELSE
BEGIN
INSERT INTO Table......
END
"bfuenz via SQLMonster.com" <u31754@.uwe> wrote in message
news:6e7fe74532642@.uwe...
>I am trying to copy two columns (OC_ID_ & OC_NAME) in one table
>(ORG_CHARGE)
> in a SQL SRV 05 DB (VFM) into two columns (DEPT_NUM and DEPT) in another
> table (DEPT) in another SQL SRV 05 DB (TRACK IT). I have to accomplish
> the
> following with the data:
> * Update if the record exists
> * Add a new record in TrackIT if it does not exist
> * Delete from TrackIT when it is Deleted in VFM
> I have tried using the DTS export tool but end up with an error that
> says...
> INSERT fails because it cannot insert NULL values into the column DEPT_ID.
> I realize that this that i have to add an integer to increase the index of
> the DEPT_ID if there are new records going into it and I have put the
> necessary code to do so but I still get the error.
> Any thoughts?
> BFuenz
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200702/1
>

Copying columns in one table in a DB to another Table in another DB

I am trying to copy two columns (OC_ID_ & OC_NAME) in one table (ORG_CHARGE)
in a SQL SRV 05 DB (VFM) into two columns (DEPT_NUM and DEPT) in another
table (DEPT) in another SQL SRV 05 DB (TRACK IT). I have to accomplish the
following with the data:
* Update if the record exists
* Add a new record in TrackIT if it does not exist
* Delete from TrackIT when it is Deleted in VFM
I have tried using the DTS export tool but end up with an error that says...
INSERT fails because it cannot insert NULL values into the column DEPT_ID.
I realize that this that i have to add an integer to increase the index of
the DEPT_ID if there are new records going into it and I have put the
necessary code to do so but I still get the error.
Any thoughts?
BFuenz
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200702/1You can write stored procedure something like that
CREATE PROC dbo.mysp
AS
IF EXISTS (SELECT * FROM Table WHERE.....)
BEGIN
UPDATE Table SET ....
DELETE FROM Table....
END
ELSE
BEGIN
INSERT INTO Table......
END
"bfuenz via droptable.com" <u31754@.uwe> wrote in message
news:6e7fe74532642@.uwe...
>I am trying to copy two columns (OC_ID_ & OC_NAME) in one table
>(ORG_CHARGE)
> in a SQL SRV 05 DB (VFM) into two columns (DEPT_NUM and DEPT) in another
> table (DEPT) in another SQL SRV 05 DB (TRACK IT). I have to accomplish
> the
> following with the data:
> * Update if the record exists
> * Add a new record in TrackIT if it does not exist
> * Delete from TrackIT when it is Deleted in VFM
> I have tried using the DTS export tool but end up with an error that
> says...
> INSERT fails because it cannot insert NULL values into the column DEPT_ID.
> I realize that this that i have to add an integer to increase the index of
> the DEPT_ID if there are new records going into it and I have put the
> necessary code to do so but I still get the error.
> Any thoughts?
> BFuenz
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200702/1
>

copy to Excel from output grid

When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..Hassan,
Unfortunately, you have to create an Excel-understood string
yourself. Here's an example of how you would do it with the
OrderDate column in Northwind's Orders table:
select
convert(varchar(30),OrderDate, 101)
+ space(1)
+ convert(varchar(30),OrderDate, 8)
from Northwind..Orders
This is for the US local month/day/year. If you need day/month/year
instead, use format 103 instead of 101. It's worth testing this a bit,
since quite a few factors can influence how dates are interpreted.
Steve Kass
Drew University
Hassan wrote:

>When i save the query analyser output to Excel, it always messes up the
>display for datetime columns ? Why is that ? and how can i copy and paste
>without do all the conversions,etc. ? Right now to show the actual datetime
>in excel, i have to format the cells to text and then paste again..
>
>
>|||Don't copy/paste. Instead, from the menu options, choose the output to be
Text, Comma Delimited instead of Grid, Column Aligned. Then save the output
as csv or just copy and paste this into Excel, which can handle this format
better than the fixed-width, Column Aligned format.
Sincerely,
Anthony Thomas
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e67sHppIFHA.2744@.tk2msftngp13.phx.gbl...
When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..

copy to Excel from output grid

When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..Hassan,
Unfortunately, you have to create an Excel-understood string
yourself. Here's an example of how you would do it with the
OrderDate column in Northwind's Orders table:
select
convert(varchar(30),OrderDate, 101)
+ space(1)
+ convert(varchar(30),OrderDate, 8)
from Northwind..Orders
This is for the US local month/day/year. If you need day/month/year
instead, use format 103 instead of 101. It's worth testing this a bit,
since quite a few factors can influence how dates are interpreted.
Steve Kass
Drew University
Hassan wrote:
>When i save the query analyser output to Excel, it always messes up the
>display for datetime columns ? Why is that ? and how can i copy and paste
>without do all the conversions,etc. ? Right now to show the actual datetime
>in excel, i have to format the cells to text and then paste again..
>
>
>|||Don't copy/paste. Instead, from the menu options, choose the output to be
Text, Comma Delimited instead of Grid, Column Aligned. Then save the output
as csv or just copy and paste this into Excel, which can handle this format
better than the fixed-width, Column Aligned format.
Sincerely,
Anthony Thomas
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e67sHppIFHA.2744@.tk2msftngp13.phx.gbl...
When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..

copy to Excel from output grid

When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..
Hassan,
Unfortunately, you have to create an Excel-understood string
yourself. Here's an example of how you would do it with the
OrderDate column in Northwind's Orders table:
select
convert(varchar(30),OrderDate, 101)
+ space(1)
+ convert(varchar(30),OrderDate, 8)
from Northwind..Orders
This is for the US local month/day/year. If you need day/month/year
instead, use format 103 instead of 101. It's worth testing this a bit,
since quite a few factors can influence how dates are interpreted.
Steve Kass
Drew University
Hassan wrote:

>When i save the query analyser output to Excel, it always messes up the
>display for datetime columns ? Why is that ? and how can i copy and paste
>without do all the conversions,etc. ? Right now to show the actual datetime
>in excel, i have to format the cells to text and then paste again..
>
>
>
|||Don't copy/paste. Instead, from the menu options, choose the output to be
Text, Comma Delimited instead of Grid, Column Aligned. Then save the output
as csv or just copy and paste this into Excel, which can handle this format
better than the fixed-width, Column Aligned format.
Sincerely,
Anthony Thomas

"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e67sHppIFHA.2744@.tk2msftngp13.phx.gbl...
When i save the query analyser output to Excel, it always messes up the
display for datetime columns ? Why is that ? and how can i copy and paste
without do all the conversions,etc. ? Right now to show the actual datetime
in excel, i have to format the cells to text and then paste again..

Copy timestamp data between columns

We have a table that was corrupted during a hardware outage and there
are no backups for the data !!
We are able to restore all the data, but there are torn pages in a
table called "WorkItem".
I wanted to copy what I could out of the workitem table and copy it to
workitem2. Noticed the timestamp column in the workitem table would
not allow me to execute:
insert into workitem2 select * from workitem where x = y
Select * into workitem2 from workitem where x= y is not good because
there are multiple runs that need to be executed to pull in workitem2
data. This statement creates the table.
FYI
To work around this - exported the data to a text file and re-
imported.
On Nov 13, 11:28 am, Justindawg <kfw...@.hotmail.com> wrote:
> We have a table that was corrupted during a hardware outage and there
> are no backups for the data !!
> We are able to restore all the data, but there are torn pages in a
> table called "WorkItem".
> I wanted to copy what I could out of the workitem table and copy it to
> workitem2. Noticed the timestamp column in the workitem table would
> not allow me to execute:
> insert into workitem2 select * from workitem where x = y
> Select * into workitem2 from workitem where x= y is not good because
> there are multiple runs that need to be executed to pull in workitem2
> data. This statement creates the table.

Copy timestamp data between columns

We have a table that was corrupted during a hardware outage and there
are no backups for the data !!
We are able to restore all the data, but there are torn pages in a
table called "WorkItem".
I wanted to copy what I could out of the workitem table and copy it to
workitem2. Noticed the timestamp column in the workitem table would
not allow me to execute:
insert into workitem2 select * from workitem where x = y
Select * into workitem2 from workitem where x= y is not good because
there are multiple runs that need to be executed to pull in workitem2
data. This statement creates the table.FYI
To work around this - exported the data to a text file and re-
imported.
On Nov 13, 11:28 am, Justindawg <kfw...@.hotmail.com> wrote:
> We have a table that was corrupted during a hardware outage and there
> are no backups for the data !!
> We are able to restore all the data, but there are torn pages in a
> table called "WorkItem".
> I wanted to copy what I could out of the workitem table and copy it to
> workitem2. Noticed the timestamp column in the workitem table would
> not allow me to execute:
> insert into workitem2 select * from workitem where x = y
> Select * into workitem2 from workitem where x= y is not good because
> there are multiple runs that need to be executed to pull in workitem2
> data. This statement creates the table.

Copy timestamp data between columns

We have a table that was corrupted during a hardware outage and there
are no backups for the data !!
We are able to restore all the data, but there are torn pages in a
table called "WorkItem".
I wanted to copy what I could out of the workitem table and copy it to
workitem2. Noticed the timestamp column in the workitem table would
not allow me to execute:
insert into workitem2 select * from workitem where x = y
Select * into workitem2 from workitem where x= y is not good because
there are multiple runs that need to be executed to pull in workitem2
data. This statement creates the table.FYI
To work around this - exported the data to a text file and re-
imported.
On Nov 13, 11:28 am, Justindawg <kfw...@.hotmail.com> wrote:
> We have a table that was corrupted during a hardware outage and there
> are no backups for the data !!
> We are able to restore all the data, but there are torn pages in a
> table called "WorkItem".
> I wanted to copy what I could out of the workitem table and copy it to
> workitem2. Noticed the timestamp column in the workitem table would
> not allow me to execute:
> insert into workitem2 select * from workitem where x = y
> Select * into workitem2 from workitem where x= y is not good because
> there are multiple runs that need to be executed to pull in workitem2
> data. This statement creates the table.

Copy text columns to nvarchar

Hi,

I have a problem, I have a table with a text type column and a
nvarchar(2000) type column on my MS SQL 2000 Server.

I know that the longest text in the text field is 1000 chars. I want to
copy the content the content of the text field into the nvarchar field.

I tried convert and cast but after the update there are only 255 chars
in the nvarchar field.

Best regards
MarcMarc Filthaut (usenet_delete_for_reply@.filthaut.com) writes:
> I have a problem, I have a table with a text type column and a
> nvarchar(2000) type column on my MS SQL 2000 Server.
> I know that the longest text in the text field is 1000 chars. I want to
> copy the content the content of the text field into the nvarchar field.
> I tried convert and cast but after the update there are only 255 chars
> in the nvarchar field.

I don't believe that. Add a len(nvarcharfield) to you query and you
will make a revelation.

The next step of this operation is to go Tools->Options->Results and
change the setting Max characters per column.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,

Erland Sommarskog wrote:
> Marc Filthaut (usenet_delete_for_reply@.filthaut.com) writes:
> The next step of this operation is to go Tools->Options->Results and
> change the setting Max characters per column.
Thanks this was one of the solutions.

Best regards
Marc