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
)
--
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
