Correct Table Structure - Optional Values

Hello,

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

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

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

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

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

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

Copying Text Field

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

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

Copying Tables and generating new keys

I have a large table that I need to copy, but I need to generate a new value for my id field using a SPROC and replace my existing ID value. I also have a few mapping tables I need to copy, so I need to store this new ID for later use. I currently have a SPROC that performs all these actions, but it takes about 3 or 4 minutes to complete and completely hogs the CPU time. Thus, I can't perform any actions until it finishes.

I'm looking for a way to run this procedure in the background. Unfortunately, my ID field value is not a GUID nor an IDENTITY column. I've researched Integration Services, but I was unable to find any DataFlow Tranformations to call a SPROC to retreive a new id nor could I find anything that would let me store my new id to update my mapping tables. SQLBulkCopy wasn't a good solution either.

If anyone has any insight to this, it would be greatly appreciated. Thanks,

You can use the OLE DB Command to call a stored proc and get an output value, but it tends to slow down execution. Any way to change this to a set based operation?

Copying from one field to another in the same row

Is there a simple method within SQL to copy the contents of one field to
another field in the same row?
TGSYes,
UPDATE Sometable Set DestColumn = SourceColumn
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Good Son" <good-son@.freeuk.com> schrieb im Newsbeitrag
news:Xns964C68ECC509Dgoodsonfreeukcom@.13
0.133.1.4...
> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS|||UPDATE <Table>
SET column1 = column2
thanks and regards
Chandra
"The Good Son" wrote:

> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS
>|||UPDATE Table SET col1=col2
WHERE ..........
"The Good Son" <good-son@.freeuk.com> wrote in message
news:Xns964C68ECC509Dgoodsonfreeukcom@.13
0.133.1.4...
> Is there a simple method within SQL to copy the contents of one field to
> another field in the same row?
>
> TGS|||Many thanks to all those who replied.
TGS

Copying Data From One DataBase To Another?

Yet another very much newbie question I suspect….

I want to know how I copy data from one field in one

DataBase to another field in another DataBase?

Basically I have an ASP driven forum, and want to upgrade to

a new ASP.NET forum… Problem is obviously the DataBase structure is different

and I don’t want to loose all the data from my current forum…So I would have to pick which table field

needs to be copied to where in the new DataBase..

Is this easy to do?

I will be using SQLExpress 2005?

Thanks

You can use a statement with a three-part name:

INSERT INTO YourTable
( ColumnsHere)
SELECT ColumnsFromtheOtherDb here
FROM Databasename.SchemaName.TableName

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?
Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:

> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?
|||> Hope that following can help you:
Thanks for the reply!!

> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?
|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:

> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!

> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:
> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!
> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+

copying Access data to SQL. Cropping occurs.

I am copying data from MS access 2003 which is in a Memo field to ...
an SQL Server 7 sp4 varchar(8000)...
but only 255 characters copy over.
Any ideas! Your help is appreciated!
It's most likely the setting in Query Analyzer that is limiting your
results. Go to options dialog in QA and change the # of chars in the
results tab to 8000. YOu can use DATALENGTH() function to see how large
they are.
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>I am copying data from MS access 2003 which is in a Memo field to ...
> an SQL Server 7 sp4 varchar(8000)...
> but only 255 characters copy over.
> Any ideas! Your help is appreciated!
|||I am using Access to copy records.
"Andrew J. Kelly" wrote:

> It's most likely the setting in Query Analyzer that is limiting your
> results. Go to options dialog in QA and change the # of chars in the
> results tab to 8000. YOu can use DATALENGTH() function to see how large
> they are.
>
> --
> Andrew J. Kelly SQL MVP
>
> "BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
> news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>
>
|||But how are you determining that the data is only 255 chars long?
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:4A87A3C1-D16A-44F5-A55A-530AB7BD5278@.microsoft.com...[vbcol=seagreen]
>I am using Access to copy records.
> "Andrew J. Kelly" wrote:

copying Access data to SQL. Cropping occurs.

I am copying data from MS access 2003 which is in a Memo field to ...
an SQL Server 7 sp4 varchar(8000)...
but only 255 characters copy over.
Any ideas! Your help is appreciated!It's most likely the setting in Query Analyzer that is limiting your
results. Go to options dialog in QA and change the # of chars in the
results tab to 8000. YOu can use DATALENGTH() function to see how large
they are.
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>I am copying data from MS access 2003 which is in a Memo field to ...
> an SQL Server 7 sp4 varchar(8000)...
> but only 255 characters copy over.
> Any ideas! Your help is appreciated!|||I am using Access to copy records.
"Andrew J. Kelly" wrote:

> It's most likely the setting in Query Analyzer that is limiting your
> results. Go to options dialog in QA and change the # of chars in the
> results tab to 8000. YOu can use DATALENGTH() function to see how large
> they are.
>
> --
> Andrew J. Kelly SQL MVP
>
> "BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
> news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>
>|||But how are you determining that the data is only 255 chars long?
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:4A87A3C1-D16A-44F5-A55A-530AB7BD5278@.microsoft.com...[vbcol=seagreen]
>I am using Access to copy records.
> "Andrew J. Kelly" wrote:
>

copying Access data to SQL. Cropping occurs.

I am copying data from MS access 2003 which is in a Memo field to ...
an SQL Server 7 sp4 varchar(8000)...
but only 255 characters copy over.
Any ideas! Your help is appreciated!It's most likely the setting in Query Analyzer that is limiting your
results. Go to options dialog in QA and change the # of chars in the
results tab to 8000. YOu can use DATALENGTH() function to see how large
they are.
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>I am copying data from MS access 2003 which is in a Memo field to ...
> an SQL Server 7 sp4 varchar(8000)...
> but only 255 characters copy over.
> Any ideas! Your help is appreciated!|||I am using Access to copy records.
"Andrew J. Kelly" wrote:
> It's most likely the setting in Query Analyzer that is limiting your
> results. Go to options dialog in QA and change the # of chars in the
> results tab to 8000. YOu can use DATALENGTH() function to see how large
> they are.
>
> --
> Andrew J. Kelly SQL MVP
>
> "BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
> news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
> >I am copying data from MS access 2003 which is in a Memo field to ...
> > an SQL Server 7 sp4 varchar(8000)...
> > but only 255 characters copy over.
> > Any ideas! Your help is appreciated!
>
>|||But how are you determining that the data is only 255 chars long?
--
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:4A87A3C1-D16A-44F5-A55A-530AB7BD5278@.microsoft.com...
>I am using Access to copy records.
> "Andrew J. Kelly" wrote:
>> It's most likely the setting in Query Analyzer that is limiting your
>> results. Go to options dialog in QA and change the # of chars in the
>> results tab to 8000. YOu can use DATALENGTH() function to see how large
>> they are.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
>> news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>> >I am copying data from MS access 2003 which is in a Memo field to ...
>> > an SQL Server 7 sp4 varchar(8000)...
>> > but only 255 characters copy over.
>> > Any ideas! Your help is appreciated!
>>

Copy/Paste text into a field

I've encountered a strange problem with the Microsoft SQL Server Management Studio.

When I manually edit a record in a table and want to insert text with more lines, only the first line is copied into the field.

The field type I use is nvarchar(max) but it doesn't really matter what type it is.

The problem only exists in the Management studio. When I perform the same task in the "old" Entreprise Manager it works fine.

Am I doing something wrong or does anybody have a key to whats wrong?

Thanks!

Ken

I don't think you are doing anything wrong. I suspect the edit control in the grid just doesn't support multi-line text.

Please report this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

We use defect reports and feature requests filed at the feedback center to make decisions about future versions and service packs of SQL Server.

As a work around, you could write a T-SQL update statement to make the change you want. That's how the grid control updates the table behind the scenes.

Thanks,
Steve

|||It's been some time since this was originally posted; I wonder if there's been any action taken on this?
I'm also having a similar problem. I often copy/paste text between database tables (usually a development table and later a production table).
This works fine in Enterprise Manager, but Management Stuido(2005), cells that contain multi-lines are not copied (it seems to be "blowing up" on the crlf; it dosen't seem to be an issue with the length of the text copied).

Its possible that there is a setting somewhere to change the behavior of the Results grid to allow this, but thus far I haven't found anything.
I followed the link provided above, but eventually decided it would be easier to write my own interface than to follow all the trails now necessary submit feedback. :)
At least I can complain here. :)|||

I just did a quick search on Microsoft Connect for any bug that was reported by customers, but did not find anything.

https://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=copy+paste

Could you this suggestion on http://connect.microsoft.com/SQLServer/?

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Copy/Paste text into a field

I've encountered a strange problem with the Microsoft SQL Server Management Studio.

When I manually edit a record in a table and want to insert text with more lines, only the first line is copied into the field.

The field type I use is nvarchar(max) but it doesn't really matter what type it is.

The problem only exists in the Management studio. When I perform the same task in the "old" Entreprise Manager it works fine.

Am I doing something wrong or does anybody have a key to whats wrong?

Thanks!

Ken

I don't think you are doing anything wrong. I suspect the edit control in the grid just doesn't support multi-line text.

Please report this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

We use defect reports and feature requests filed at the feedback center to make decisions about future versions and service packs of SQL Server.

As a work around, you could write a T-SQL update statement to make the change you want. That's how the grid control updates the table behind the scenes.

Thanks,
Steve

|||It's been some time since this was originally posted; I wonder if there's been any action taken on this?
I'm also having a similar problem. I often copy/paste text between database tables (usually a development table and later a production table).
This works fine in Enterprise Manager, but Management Stuido(2005), cells that contain multi-lines are not copied (it seems to be "blowing up" on the crlf; it dosen't seem to be an issue with the length of the text copied).

Its possible that there is a setting somewhere to change the behavior of the Results grid to allow this, but thus far I haven't found anything.
I followed the link provided above, but eventually decided it would be easier to write my own interface than to follow all the trails now necessary submit feedback. :)
At least I can complain here. :)|||

I just did a quick search on Microsoft Connect for any bug that was reported by customers, but did not find anything.

https://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=copy+paste

Could you this suggestion on http://connect.microsoft.com/SQLServer/?

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

copy/paste grid from QA - no field names!

for debug purposes i tried to copy paste some Query Analyser output into Excel... the data pastes fine, but i can't get the field-names to copy/paste.

is there a trick?

izyNot by itself...

YOu could save it as text...you get the column names that way...

Or you can Go

SELECT 'Col1', 'Col2', 'Ect'
UNION ALL
SELECT Col1, Col2, Ect
FROM your Table|||text! stupidly simple really.
thanks for the help,

izy|||Try results to Text & Tab delimited.
Under Tools --> Options --> Results.

It works OK for me.

Tim S

copy the data from one field of one row to the same field of another row

I have been puzzling over this for nearly two days now and I'm about ready to dispair.

The task seems simple:

There are a few rows in a table that looks like this:

RecipientID, Name, FreshUpload, Removed
info@.test.com,Tester, 1, NULL
info@.test.com,Tester, 0, 07/18/2002
info2@.another.com,Other2,0,NULL
info3@.another.com, Other3,0,NULL
...

After uploading a bunch of records they got inserted into this table. The new records are marked with a "1" in the field "FreshUpload". The task is to find the matching record (#2 above) with new=0 and to save that "Removed" date into the record with new=1. so my record #1 would look like this

info@.test.com,Tester, 1, 07/18/2002

The closest I got to a solution was this:

CREATE PROCEDURE [eWW].[REAddKeepNew]
(@.UserID nvarchar (50),

AS

UPDATE Recipients
SET RERemoved =
(SELECT RERemoved
FROM Recipients
WHERE (REFreshUpload = 0) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 1 AND REUserID = @.UserID)) AND (REUserID = @.UserID))
WHERE (REFreshUpload = 1) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 0 AND REUserID = @.UserID)) AND (REUserID = @.UserID)

GO

This works if the table holds only one incident where a new record matches an old record that carries the Removed Flag.

Can anyone see how to copy selectable data from one row to another?

eWWThe procedure accepts a recipientID to check - did you want it to process all of the recipients in the table at once?|||Not too clear what you want but something like this maybe

update tbl
set Removed =
(select max(Removed) from tbl t2 where t2.RecipientID = tbl.RecipientID and t2.Name = tbl.Name and t2.FreshUpload = 0)
where tbl.FreshUpLoad = 1

If there can only be one rec with FreshUpload - 0 then

update tbl
set Removed = t2.Removed
from tbl, tbl t2
where t2.RecipientID = tbl.RecipientID
and t2.Name = tbl.Name and t2.FreshUpload = 0
and tbl.FreshUpLoad = 1|||Hi,

We can use a cursor and instead of updating the "Removed" field, y cant we jus update the "FreshLoad" field with a 1 when the match is found.
Does this query help??
************************************************** ******
declare updaterec cursor
for
select * from <Tablename> where FreshUpload=1

open updaterec

fetch next from updaterec into @.varRecipientID,@.varName,@.varFreshUpload,@.varRemov ed

while @.@.fetch_status=0
begin
Update <Tablename> set FreshLoad=1 where RecipientID=@.varRecipientID
fetch next from updaterec into @.varRecipientID,@.varName,@.varFreshUpload,@.varRemov ed
end

close updaterec
deallocate updaterec
************************************************** *****
Please tell me if this helps.
Regards,
Ramya

Originally posted by eWW
I have been puzzling over this for nearly two days now and I'm about ready to dispair.

The task seems simple:

There are a few rows in a table that looks like this:

RecipientID, Name, FreshUpload, Removed
info@.test.com,Tester, 1, NULL
info@.test.com,Tester, 0, 07/18/2002
info2@.another.com,Other2,0,NULL
info3@.another.com, Other3,0,NULL
...

After uploading a bunch of records they got inserted into this table. The new records are marked with a "1" in the field "FreshUpload". The task is to find the matching record (#2 above) with new=0 and to save that "Removed" date into the record with new=1. so my record #1 would look like this

info@.test.com,Tester, 1, 07/18/2002

The closest I got to a solution was this:

CREATE PROCEDURE [eWW].[REAddKeepNew]
(@.UserID nvarchar (50),

AS

UPDATE Recipients
SET RERemoved =
(SELECT RERemoved
FROM Recipients
WHERE (REFreshUpload = 0) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 1 AND REUserID = @.UserID)) AND (REUserID = @.UserID))
WHERE (REFreshUpload = 1) AND (RecipientID IN
(SELECT RecipientID
FROM recipients
WHERE REFreshUpload = 0 AND REUserID = @.UserID)) AND (REUserID = @.UserID)

GO

This works if the table holds only one incident where a new record matches an old record that carries the Removed Flag.

Can anyone see how to copy selectable data from one row to another?

eWW|||Nigelrivett: I tried your solution first as it looked the leanest and BINGO!
That's it. You solved my riddle. Amazing how simple this can be when you know what you're doing.

Thank you very much nigelrivett!

and thank all of you who have helped.

eWW

[QUOTE][SIZE=1]Originally posted by nigelrivett
Not too clear what you want but something like this maybe

update tbl
set Removed =
(select max(Removed) from tbl t2 where t2.RecipientID = tbl.RecipientID and t2.Name = tbl.Name and t2.FreshUpload = 0)
where tbl.FreshUpLoad = 1

copy som data from one field split it up into 3 new fields

Hey there
I have to copy som data from one field and split it up into 3 seperat new
fields in the same table...
Eks.:
---
Data column Artis (Old field)
---
Artist
Larsen, Kim & Kjukken
Larsen, Kim
Larsen, Kim - Kjukken
---
Three new data columns
---
Firstname Lastname Band
Kim Larsen & Kjukken
Kim Larsen
Kim Larsen - Kjukken
My SQL Statement looks like this now, I just have to add the Band column and
the other - and & checks:
UPDATE Test_Products
SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
LEFT(artist, CHARINDEX(' ',artist) - 1)
ELSE
RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
END,
Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
ELSE
LEFT(artist, CHARINDEX(',',artist) - 1)
END
I hope hearing from you soon, because my project is totally jamed up until I
get this working and done.
Regards,
Lucien
Mvh
DC
--
Greetings ecoderHi
I'd prefer doing such thing on the client
create table #test
(
col varchar (50)
)
insert into #test values ('Larsen, Kim & Kjukken')
insert into #test values ('Larsen, Kim')
insert into #test values ('Larsen, Kim - Kjukken')
select LastName, FirstName, coalesce(Band,FirstName) Band
from (
select
col,
substring(col,1,Comma-1) LastName,
substring(col,Comma+1,Spce-4) FirstName,
nullif(substring(col,Spce+4,40),'') Band
from (
select
col,
charindex(',',col) Comma,
charindex(' ',col+space(1),charindex(',',col)) Spce
from #test
) D
) SplitNames
"ecoder" <ecoder@.discussions.microsoft.com> wrote in message
news:1B244528-6EBE-42DF-B1DB-3194BB9A2D7E@.microsoft.com...
> Hey there
> I have to copy som data from one field and split it up into 3 seperat new
> fields in the same table...
> Eks.:
> ---
> Data column Artis (Old field)
> ---
> Artist
> Larsen, Kim & Kjukken
> Larsen, Kim
> Larsen, Kim - Kjukken
> ---
> Three new data columns
> ---
> Firstname Lastname Band
> Kim Larsen & Kjukken
> Kim Larsen
> Kim Larsen - Kjukken
> --
> My SQL Statement looks like this now, I just have to add the Band column
> and
> the other - and & checks:
> UPDATE Test_Products
> SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> LEFT(artist, CHARINDEX(' ',artist) - 1)
> ELSE
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> END,
> Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> ELSE
> LEFT(artist, CHARINDEX(',',artist) - 1)
> END
> I hope hearing from you soon, because my project is totally jamed up until
> I
> get this working and done.
> Regards,
> Lucien
> Mvh
> DC
> --
> Greetings ecoder|||See if this helps:
select
c1,
left(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' ') - 1) as firstname,
left(c1, charindex(',', c1) - 1) as lastname,
parsename(stuff(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' '), 1, '.'), 1) as band
from
(
select 'Larsen, Kim & Kjukken'
union all
select 'Larsen, Kim'
union all
select 'Larsen, Kim - Kjukken'
) as t1(c1)
go
AMB
"ecoder" wrote:

> Hey there
> I have to copy som data from one field and split it up into 3 seperat new
> fields in the same table...
> Eks.:
> ---
> Data column Artis (Old field)
> ---
> Artist
> Larsen, Kim & Kjukken
> Larsen, Kim
> Larsen, Kim - Kjukken
> ---
> Three new data columns
> ---
> Firstname Lastname Band
> Kim Larsen & Kjukken
> Kim Larsen
> Kim Larsen - Kjukken
> --
> My SQL Statement looks like this now, I just have to add the Band column a
nd
> the other - and & checks:
> UPDATE Test_Products
> SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> LEFT(artist, CHARINDEX(' ',artist) - 1)
> ELSE
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> END,
> Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> ELSE
> LEFT(artist, CHARINDEX(',',artist) - 1)
> END
> I hope hearing from you soon, because my project is totally jamed up until
I
> get this working and done.
> Regards,
> Lucien
> Mvh
> DC
> --
> Greetings ecoder|||Hi ,Alejandro
Hehe, I have faced the same "problem". A Band column for Kim is NULL.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:22AF2E13-05F9-4DCA-BA56-D50D2A2A1031@.microsoft.com...
> See if this helps:
> select
> c1,
> left(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
> ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' ') - 1) as firstname,
> left(c1, charindex(',', c1) - 1) as lastname,
> parsename(stuff(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex('
> ',
> ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' '), 1, '.'), 1) as band
> from
> (
> select 'Larsen, Kim & Kjukken'
> union all
> select 'Larsen, Kim'
> union all
> select 'Larsen, Kim - Kjukken'
> ) as t1(c1)
> go
>
> AMB
> "ecoder" wrote:
>|||Hi Uri
When I run it in query analyzer the field without a band looks like this:
Firstname Lastname Band
Kim Larsen Kim
Why is that and what should I change to correct this?
Regards,
ecoder
"Uri Dimant" wrote:

> Hi
> I'd prefer doing such thing on the client
>
> create table #test
> (
> col varchar (50)
> )
> insert into #test values ('Larsen, Kim & Kjukken')
> insert into #test values ('Larsen, Kim')
> insert into #test values ('Larsen, Kim - Kjukken')
>
> select LastName, FirstName, coalesce(Band,FirstName) Band
> from (
> select
> col,
> substring(col,1,Comma-1) LastName,
> substring(col,Comma+1,Spce-4) FirstName,
> nullif(substring(col,Spce+4,40),'') Band
> from (
> select
> col,
> charindex(',',col) Comma,
> charindex(' ',col+space(1),charindex(',',col)) Spce
> from #test
> ) D
> ) SplitNames
>
> "ecoder" <ecoder@.discussions.microsoft.com> wrote in message
> news:1B244528-6EBE-42DF-B1DB-3194BB9A2D7E@.microsoft.com...
>
>|||Uri,
Let him / her to decide what to do with null values. The help you provided
is a good start, the rest is up to him / her.
AMB
"Uri Dimant" wrote:

> Hi ,Alejandro
> Hehe, I have faced the same "problem". A Band column for Kim is NULL.
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:22AF2E13-05F9-4DCA-BA56-D50D2A2A1031@.microsoft.com...
>
>|||Lookup LTRIM(),RTRIM() functions in the BOL
"ecoder" <ecoder@.discussions.microsoft.com> wrote in message
news:EABB8DE8-1DD3-44E0-875E-51B775A85038@.microsoft.com...
> Hi Uri
> When I run it in query analyzer the field without a band looks like this:
> Firstname Lastname Band
> Kim Larsen Kim
> Why is that and what should I change to correct this?
> Regards,
> ecoder
>
> "Uri Dimant" wrote:
>|||It's all working well becides when to band column is null
This is a urgent one...
If the band column is empty it copies the firstname Kim into the band column
.
What should I change....?
And thanks Uri and Alejandro for your help.
Greetings ecoder
"Alejandro Mesa" wrote:
> Uri,
> Let him / her to decide what to do with null values. The help you provided
> is a good start, the rest is up to him / her.
>
> AMB
> "Uri Dimant" wrote:
>|||If you are talking about Uri's solution, then use an empty string for the
second argument of the "coalesce" function or do not use the "coalesce"
function at all.

> select LastName, FirstName, coalesce(Band,FirstName) Band
select LastName, FirstName, coalesce(Band,'') Band
...
-- or
select LastName, FirstName, Band
...
AMB
"ecoder" wrote:
> It's all working well becides when to band column is null
> This is a urgent one...
> If the band column is empty it copies the firstname Kim into the band colu
mn.
> What should I change....?
> And thanks Uri and Alejandro for your help.
> --
> Greetings ecoder
>
> "Alejandro Mesa" wrote:
>|||God morning...
I have one last question though. I have to update an existing database table
with lots off records.
How do I change your or Uri examples to UPDATE instead of INSERT.
Sorry I'm not that hardcore in T-SQL!
:-)
But i'm trying...
Hope hearing from you.
Regards,
ecoder
"Alejandro Mesa" wrote:
> If you are talking about Uri's solution, then use an empty string for the
> second argument of the "coalesce" function or do not use the "coalesce"
> function at all.
>
> select LastName, FirstName, coalesce(Band,'') Band
> ...
> -- or
> select LastName, FirstName, Band
> ...
>
> AMB
>
> "ecoder" wrote:
>