|
|
Sql server 2008.
i have already imported the excel spreadsheet with all the users info to database. Now for instance there are different table for address field, different table for name field , differnt table for email etc. So my question is do i have to use curser so that i can insert the users value from this temp table to all the necessary table. if yes then how or if not then what's the best way ?
Thank You.
Andyyy
Reply·
|
|
|
|
|
Hai all,
I am searching one string (ex.Hello-Welcome) in text file using SQL query.
its searching Hello and Welcome seperatly and giving a result , i think bcoz hyphen(-) is a word breaker
in SQL .can any one give me the idea how to resolve the problem.
i need to use hyphen in searching string, and i need a combined string search result.
how to form the query?, if any one have sample pls send me.
thanks
mahe
|
|
|
|
|
Let me know if u r not looking for this -
declare @t table(record varchar(100))
insert into @t
select 'Hello-World' union all
select 'code - project codes are good' union all
select 'hello' union all
select ' world' union all
select ' how are u-?'
Query
select * from @t where record like '%[-]%'
Output:
record
Hello-World
code - project codes are good
how are u-?
Niladri Biswas
modified on Tuesday, December 1, 2009 8:12 AM
|
|
|
|
|
thanks 4 ur reply
its like same but i am searching the hyphenated string
in text file.
if u know any SQL query to searchin the string in any file pls let me know.
thanks
mahe
|
|
|
|
|
How are your searching?
Using Like , or some varieant of FullText searching?
|
|
|
|
|
i am searchin with CONTAINS keyword.
|
|
|
|
|
pls help me out to display a string for example 'abcd' 1000 times
using select statement in SQL
|
|
|
|
|
a)
select REPLICATE ('abcd', 1000)
b)
PRINT 'abcd'
GO 10000
c) If using sql server 2005+
with cte as
( select 1 as rn,'abcd' as data
union all
select rn+1, 'abcd' as data from cte where rn<1000)
select data from cte option (maxrecursion 0)
d)
DECLARE @x XML
SELECT @x = REPLICATE ('<x>abcd</x>', 1000)
SELECT i.value('.', 'VARCHAR(MAX)') ReplacedStrings
FROM @x.nodes('//x') x(i)
Niladri Biswas
modified on Tuesday, December 1, 2009 6:10 AM
|
|
|
|
|
thanks for the response
but this gives me an output in this manner
abcdabcdabcdabcd..................
i m looking for
abcd in 1000 records
likes this
abcd
abcd
abcd
abcd
.
.
.
.
.
.
.
.
|
|
|
|
|
Follow the third approach i.e.
recursive cte
Niladri Biswas
|
|
|
|
|
thanks
its coming
how will i be able to insert that 'abcd' 1000 times in my insert given below
insert into [dbo].[Test](NAME)
|
|
|
|
|
A) With recursive cte approach
declare @t table(name varchar(50))
;with cte as
( select 1 as rn,'abcd' as data
union all
select rn+1, 'abcd' as data from cte where rn<1000)
insert into @t
select data from cte option (maxrecursion 0)
select * from @t
B) Xquery approach
declare @t table(name varchar(50))
DECLARE @x XML
SELECT @x = REPLICATE ('<x>abcd</x>', 1000)
insert into @t
SELECT i.value('.', 'VARCHAR(MAX)') ReplacedStrings
FROM @x.nodes('//x') x(i)
select * from @t
Niladri Biswas
|
|
|
|
|
|
One of the ways is:
create table #tblname (
col1 varchar(200)
)
declare @i as int
set @i=0
while @i<=1000
begin
set @i=@i+1
insert into #tblname values ('abc')
end
select * from #tblname
drop table #tblname
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
thanks for the response i m looking for doing with out looping
|
|
|
|
|
use the same method as you were told previously, a cartesian join, and limit it
select top 1000 'a'
from dbo.sysobjects as so1, dbo.sysobjects as so2
and buy a book on SQL.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
thanks for ur advice
but u are failed to answer my q
u buy SQL basics
|
|
|
|
|
arun_pk wrote: but u are failed to answer my q
In what respect?
select top 1000 'a' from dbo.sysobjects as so1, dbo.sysobjects as so2
will do as your (vague) question asks.
arun_pk wrote: u buy SQL basics
Why? Its not me asking basic questions about sql, its you that doesn't understand what you are being told will work.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
arun_pk wrote: but u are failed to answer my q
Ashfield's query works perfectly for your requirement.
arun_pk wrote: u buy SQL basics
I just saw you being spoon fed by Niladri_Biswas and you are advising others to learn SQL basics ? Funny.
|
|
|
|
|
Cute - what if sysobjects less than 100 records, add another cross join, and another !
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: what if sysobjects less than 100 records, add another cross join
Yep. Mind you, by default sysobjects has about 40 rows, and 40 * 40 is 1600 so he should be safe.
And at the end of the day, he now has enough info to work it out for himself
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Ashfield wrote: he now has enough info to work it out for himself
Not with the spoon feeding Niladri had to do, I doubt he has the nous to research cross join !
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am new to SQL coding and having trouble grouping and generating sub totals and Grand Totals for a report generated by this query:
SELECT ISNULL(CAST(dteAccountingDate AS varchar (20)),'') AS MonthEnd,Payee, ClientName,
ISNULL (CAST (Accrual as varchar (20)),'') AS Accrual,
ISNULL (CAST (Payment as varchar (20)),'') AS Payment,
ISNULL (CAST (OverUnder as varchar (20)),'') AS OverUnder,
ISNULL (CAST (Salaries as varchar (20)),'') AS AnnualisedSalaries,
ISNULL (CAST (PercentVal as varchar (20)),'') AS Percentage,
ISNULL (CAST (CountMem as varchar (20)),'') AS NoMember,
ISNULL (CAST (CostPerMem as varchar (20)),'') AS CostPerMember
FROM #tmp INNER JOIN tblClientParents
ON #tmp.fkiClientParentID = tblClientParents.pkiClientParentID
AND Payee NOT IN ('Opening Value','Current Year','Sub Totals','Totals', '','______________','Prior Year Payment')
My primary objective is to have results grouped and summaries generated by Payee and ClientName.
I tried group by and order by but they don't give me the required results; even tried Compute By. I want to use Group by With Rollup but the problem is i only want to generate summaries of three columns Accrual, Payment and CountMem.
Is there a way to Group by all the columns in the select query but have Rollup only on the three columns? Or any other ways to generate summaries on the three columns only?
I also tried Group By Rollup, it doesn't work with SQL Server 2008 that i am using.
Need some help here please!
Thanks in advance
|
|
|
|
|