|
I'm creating a query to transform one DB to another, but it's getting uncontrollably big! I know, I know I should use SSIS packages, but they just don't do it for me. Most of the time it's faster to just write the code than model it in SSIS. Truth be told, I REALLY miss SQL200's DTS packages! Maybe I'm just to stubborn to accept change.
But anyway...
Is there no way that I can make my query simpler by moving some code out and simply referencing it in my main query? (And I don't think SPROCS is what I want here)
In Oracle I used to be able to write statements which execute text files... does SQL2005 have such functionality?
modified on Thursday, August 28, 2008 5:25 AM
|
|
|
|
|
If you want to break logic into several pieces you can use for example sqlcmd command line utility to run a file (a bit like in Oracle SQL*Plus). However you cannot break a single statement, but I guess that is not your intention.
Mika
|
|
|
|
|
You might want to go after the SSIS package approach. It might take longer to create, but it is a lot easier to maintain with larger projects. You might need to ask yourself will you be saving the time in the future by using SSIS?
|
|
|
|
|
i have a string data in a column in an msqsql database table and the values are separated by commas (,)eg 01,02,M,1,045,12,1948 i want to remove the commas in that column so that the data looks like 0102M1045148.
can any one help me .
thanks members 
phokojoe
|
|
|
|
|
select replace(Columnname,',','') from TableName
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
thanks Blue_Boy, i am going to try it, but by the look of the syntax, it will work.
thanks once more 
phokojoe
|
|
|
|
|
I have tested that query and it has worked for me, and sure will work for you too.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
i have just tested it, but the problem is, i have so many entries in that column. when i write a column name, it does not replace the commas, instead it returns the column name not even the entries in that column. eg.
like i said, the values in that column are of this shape:
01,02,M,045,1948,2,21 and the column name (field) is say column1 i will write:
select replace('column1',',','') it only returns column1 not 0102M0451948221.
please help me on this. 
phokojoe
|
|
|
|
|
phokojoe wrote: select replace('column1',',','')
is not correct bcause you have write columnename inside single qutes
Try again code down below
<br />
Select replace(columname,',','') from tablename
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
yah yah yah, without you i could have not survived this stress.
i have done it! it works.
thanks once more.
phokojoe
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi friends, I would like to query with a string variable:
For example if using
select * from Members
I get
ID(bigint) Name(varchar(max)) Owner(varchar(max))
1 N1 a000001
2 N2 a000002
3 N3 a000003
4 N4 a000003,a000004
5 N5 a000001,a000002
Then if using
select * from Members where Owner like '%a000001%'
I would get
ID(bigint) Name(varchar(max)) Owner(varchar(max))
1 N1 a000001
5 N5 a000001,a000002
Then I use a string variable test to query as follows:
System.String test="a000001";
select * from Members where Owner like '%+test+%'
I get nothing.
So could you please help me that:
how can I use the test to query with the same result as
select * from Members where Owner like '%a000001%'
when test="a000001"?
Thanks for any feedback.
|
|
|
|
|
chenayang wrote: select * from Members where Owner like '%+test+%'
This query looks for 'test' not 'a000001'. Since 'test' is a variable, you should append it.
System.String test="a000001";
string query = string.Concat("select * from Members where Owner like '%", test, "%'");Note, this method is open to SQL injection. Use parameterized queries always.
|
|
|
|
|
Thanks a lot, N a v a n e e t h
I solve the problem as follows:
System.String test="a000001";
string query = String.Concat("select * from Members where Owner like '%", test, "%'");
|
|
|
|
|
I'm running the following query and it is giving me a major problem.
SELECT Listings.ListingID, Listings.Title, Listings.HasPhoto, Listings.IsTaken, Listings.IsPickedUp, Listings.TakenDate, Listings.PickedUp, Listings.TimesViewed, Categories.CategoryName, Members.dbo.Members.LoginID, Listings.Submitted, Listings.Approved, GetTakenMemberName.LoginID AS TakenMemberName
FROM Listings INNER JOIN
Categories ON Listings.CategoryID = Categories.CategoryID INNER JOIN
Members.dbo.Members ON Listings.MemberID = Members.dbo.Members.MemberID INNER JOIN
Members.dbo.Members AS GetTakenMemberName ON Listings.TakenBy = GetTakenMemberName.MemberID
WHERE (Listings.MemberID = @MemberID)
If I remove
INNER JOIN Members.dbo.Members AS GetTakenMemberName ON Listings.TakenBy = GetTakenMemberName.MemberID
which is the last join and
GetTakenMemberName.LoginID AS TakenMemberName
from the SELECT portion, the query runs without a problem and gives a result. With those statements in however the result is an empty table. Currently there is only one record in the Listings and Members table, so as far as I'm concerned TakenMemberName should be returning as null or empty.
I even tried using an all 0 guid in the TakenBy field so at least the query would have something to look for during the SELECT. However, that didn't work either.
[Modified]
On further investigation I have found that if I put an actual member in the TakenBy field that the query executes with out problem, but if the query doesn't match a member then the entire select fails. How can I prevent that from happening?
modified on Wednesday, August 27, 2008 10:08 PM
|
|
|
|
|
Sounds like you need an LEFT OUTER JOIN instead.
|
|
|
|
|
Thank you very much. Changed the join and it worked perfectly.
I have so much to learn about SQL it unbelievable to me somedays. Thanks again for the help. Been stuck on this for about 4 hours.
|
|
|
|
|
I created a Stored Procedure to create random strings of the length and complexity desired. It is based on the widely published spt_values query for near random numbers.
It is functional, but I'm not entirely happy with the script. There has to be more elegant way to handle different complexity settings, other than the IF...ELSE clauses I used.
CODE:
<br />
-- Random String Generator<br />
-- By: Jesse Wimberley<br />
-- 27 August 2008<br />
-- Description: Creates Random strings of <br />
-- variable length and complexity.<br />
CREATE PROCEDURE [dbo].[up_RanGen]<br />
@Return varchar(200) = '' OUTPUT,<br />
-- @Len for the length of the output<br />
@Len int = 8,<br />
--Complexity set with @Complex<br />
-- 1 = Uppercase Letters<br />
-- 2 = Uppercase and Lowercase Letters<br />
-- 3 = Uppercase, Lowercase and Numbers<br />
-- 4 = Complex Password<br />
@Complex int = 4<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
declare @Random varchar(200)<br />
-- @a thru @f set the ranges of acceptable values for each character<br />
declare @a int, @b int, @c int, @d int, @e int, @f int<br />
If @Complex = 1<br />
BEGIN<br />
set @a = 65<br />
set @b = 80<br />
set @c = 81<br />
set @d = 82<br />
set @e = 83<br />
set @f = 90<br />
END<br />
ELSE<br />
If @Complex = 2<br />
BEGIN<br />
set @a = 65<br />
set @b = 90<br />
set @c = 97<br />
set @d = 100<br />
set @e = 101<br />
set @f = 122<br />
END<br />
ELSE<br />
If @Complex = 3<br />
BEGIN<br />
set @a = 65<br />
set @b = 90<br />
set @c = 97<br />
set @d = 122<br />
set @e = 48<br />
set @f = 57<br />
END<br />
ELSE<br />
BEGIN<br />
set @a = 48<br />
set @b = 60<br />
set @c = 61<br />
set @d = 75<br />
set @e = 76<br />
set @f = 122<br />
END<br />
set @Random=''<br />
select @Random=@Random+char(n) from<br />
(<br />
select top (@Len) number as n from master.dbo.spt_values <br />
where (type='p' and number between (@a) and (@b)) or (type='p' and number between (@c) and (@d)) or (type='p' and number between (@e) and (@f))<br />
order by newid()<br />
) as t<br />
set @Return = @Random<br />
END<br />
The four complexity settings are based on ranges of values returned as their ascii equivalents. Upper/Lower/Numbers has three ranges of numbers. For simplicity, I stretched all four settings across three ranges, even when only one or two ranges were needed.
|
|
|
|
|
Hi,
how can i get info on the 2nd max(id) or 3rd min(money) record on a table.
I only know how to get min(X) or max(X) record on a table
Thanks 
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Select Max(id)
From <code>table</code>
where id != (Select Max(id) From <code>table</code>)
The above will give you the second maximum id.
|
|
|
|
|
Another way:
<br />
select top 1 ColumName from TableName<br />
where ColumName < (select max(ColumName ) from TableName)<br />
order by TableName desc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
To select the 5th lowest (or n-th lowest) replace the "5" in this query with the number you want.
Select max(X) from
(Select top 5 id as X from idTable order by id asc)t
To select the n-th highest replace the "5" in this query with the number you want.
Select min(X) from
(Select top 5 id as X from idTable order by id desc) t
Cheers!
|
|
|
|
|
Hi,
i need some intel on arrays and loops. I only know how to use the while loop. I sometimes write store_procedures that have more 100lines.
Some tutorial would be a good help to me (on how to perform loops or array)
declare @id_user1 as int
declare @id_user2 as int
declare @id_user3 as int
...
declare @id_user10 as int
i can´t do this
while @a < 10 + 1
begin
set @id_user[@a] = 'whatever'
end
thanks 
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Thats because you can't do sql like that, there is no concept of an array. To do it like that you need to create and execute the sql dynamically
declare @id_user1 as int
declare @id_user2 as int
declare @id_user3 as int
declare @sql nvarchar(2000)
declare @a int
set @a =1
while @a < 11
begin
select @sql = 'set @id_user' + convert(varchar,@a) + ' = 1'
exec (@sql)
select @a = @a +1
end
try Google for dynamic sql, there are lots of examples.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
The best way to simulate arrays in SQL is to use the table variable (which I see you posted about just before this post)
So to do the above, you would have something like this:
DECLARE @table TABLE
( id INT, id_user varchar(200))
declare @i INT
Set @i = 1
While @i <= 10
BEGIN
insert into @table(id, id_user)
values(@i,'whatever' + cast(@i as varchar))
Set @i = @i + 1
END
-- Test it:
Select * from @table
Hope this answers your previous question as well!
Cheers!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
