A couple of questions

Well, I had a questions, but I figured I'd include all of the things I've been trying to figure out.

I remember reading in books online about transformnig the data returned by a SQL query. For example, the database has a boolean 1 or 0, but I want to return a Yes / No or True / False. You could even doa number mapping for example, 1 becomes "one", 2 becomes "two" etc. Can anyone point me in the right direction?

Is there a way to check a data field for a specific format? SELECT IDNumber from table WHERE IDNumber is not 6 numberic digits. (IDNumber is nvarchar with a length of 50 and stores IDNUmbers that also contain letters and special characters)

Query returns 123456789, 12345678, 2-0152, A5487, but not 123456 or 101254, etc.

How do I insert several lines if I have the data as text? I can insert multiple lines from another table, but how do I insert multiple lines if I want to type in the data?

How do I insert the same line into several SQL database on different computers using query analyzer? I basically have to enter a line for each user into the user table. I can do it with osql, but I don't know haw to change the server in query analyzer. With query analyzer I can create a script and just substitute the variables defined at the beginning.

Thanks,

Cory

1. To 'transform' data, use the CASE structure.

Code Snippet

SELECT Gender = CASE Sex

WHEN 1 THEN 'M'

WHEN 0 THEN 'F'

END

FROM MyTable.

2. To check for specific format, i.e., 6 character number, something like this 'should' work for you (it's not perfect, but I think it may give you what you desire):

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( IDNumber varchar(50) )

INSERT INTO @.MyTable VALUES ( '123456789' )
INSERT INTO @.MyTable VALUES ( '12345678' )
INSERT INTO @.MyTable VALUES ( '2-0152' )
INSERT INTO @.MyTable VALUES ( 'A5487' )
INSERT INTO @.MyTable VALUES ( 'A54871' )
INSERT INTO @.MyTable VALUES ( '123456' )
INSERT INTO @.MyTable VALUES ( '101254' )

SELECT IDNumber
FROM @.MyTable
WHERE ( len( IDNumber ) <> 6
OR isnumeric( IDNumber ) = 0
)

IDNumber
--
123456789
12345678
2-0152
A5487
A54871

3.

How do I insert several lines if I have the data as text?

I don't understand what you want to do here, please give more information.

4. Click on [File], [Connect], to connect to a different server. Open your script file, or cut/paste from the previous connection window.

(OR)

Set the additional Servers up as 'linked servers'. Refer to Books Online, Topic: 'Linked Server', sp_addlinkedserver.

Then you can use four object part naming to designate the server.

|||

Hi Arnie,

May be we can use following statement for point number 2.

select

*

from

@.MyTable

where

IDNumber notlike'[0-9][0-9][0-9][0-9][0-9][0-9]'

AMB

|||

Thank you for your quick reply.

For #3. In your example you used 7 insert statements to insert the rows into the table. Is there a way to insert them using one statement?

For #4. Is there a way to do it without adding linked servers? In osql, I type

osql -S 192.168.1.123 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")

osql -S 192.168.1.124 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")

osql -S 192.168.1.125 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")

osql -S 192.168.1.126 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")

Is there a way to do it in query analyzer without linked servers.

Thanks,

Cory