Database

<<thashif>> wrote:

I neva asked u 2 work or complete the task for me

<code>SELECT</code> MyTable.Column1 , S.Column2,S.Column3, S.Column4
<code>FROM</code> MyTable , (
<code>Select</code> MyTable.T2 , MyTable.T3, MyTable.T4
<code>From</code> MyTable
<code>Group By</code> MyTable.T2 , MyTable.T3, MyTable.T4
<code>Having</code> ( Count(Sheet1.T2)>1 and Count(Sheet1.T3)>1 and Count(Sheet1.T4)>1 )
) as S
<code>Where</code> MyTable.T2=S.T2 and MyTable.T3=S.T3 and MyTable.T4=S.T4

SELECT 
(SELECT top 1 m2.Clmn1 FROM mytable m2 WHERE m2.Clmn2= m.Clmn2) Clmn1 ,--by this subquery you get first value of clmn1 column 
clmn2,clmn3,clmn7
FROM mytable m
GROUP BY m.clmn2,m.clmn3,m.clm7
HAVING 
COUNT (col2) > 0 AND
COUNT (col3) > 0 AND
COUNT (col7) > 0 

DbConnection.Open();

OdbcCommand DbCommand = DbConnection.CreateCommand();

DbCommand.CommandText = ("SELECT * FROM sqlite_master WHERE type='table' ORDER BY name");

DbReader = DbCommand.ExecuteReader();

List TableNames = new List();

while(DbReader.Read()){
    TableNames.Add(DbReader["name"]);
    Trace.WriteLine(DbReader["name"]);
}

DbReader.Close();
DbConnection.Close();

OdbcCommand DbCommand = DbConnection.CreateCommand();

//SQL query to return all table names
//http://www.sqlite.org/faq.html#q7
DbCommand.CommandText = ("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name");

DbReader = DbCommand.ExecuteReader();


List<string> TableNames = new List<string>();

while (DbReader.Read())
{
    TableNames.Add(DbReader["name"].ToString());
    Trace.WriteLine(DbReader["name"]);
}


DbReader.Close();

if (TableNames.Contains("bandwidth_records"))
{
    //check to make sure column names are present
    DbConnection.Close();
}
else
{
    //create table & columns
)

declare @t table(completeaddress varchar(100))
insert into @t
	select '1234 McKean Street' union all select '1234 McKean Street' union all
	select '1234 E McKean Street' union all select '1234 E McKean Street' 

select 
	StreetNum = 'StreetNum : ' + SUBSTRING(completeaddress,1,4) +',' 
	,Direction = 'Direction : ' + 
	
				case when ltrim(rtrim(SUBSTRING(completeaddress,5,2))) In('N','S','E','W') 
					 then SUBSTRING(completeaddress,5,2) else '' end + ','
	,StreetName = 'StreetName : ' + 
	
	case when ltrim(rtrim(SUBSTRING(completeaddress,5,2))) In('N','S','E','W')
	then SUBSTRING(completeaddress,7,LEN(completeaddress)) 
	else SUBSTRING(completeaddress,6,LEN(completeaddress)) end	
from @t

StreetNum : 1234,	Direction : ,	StreetName : McKean Street
StreetNum : 1234,	Direction : ,	StreetName : McKean Street
StreetNum : 1234,	Direction :  E,	StreetName :  McKean Street
StreetNum : 1234,	Direction :  E,	StreetName :  McKean Street

AndyInUK wrote:

The above cursor works good,

Ashfield wrote:

you should be able to add your set just before the insert

Ashfield wrote:

I think you lose your first record.

Ashfield wrote:

Why select the ID in your cursor if you don't use it.

OPEN Customer
FETCH Customer INTO ......
WHILE @@Fetch_Status = 0   
BEGIN   
     Insert into test values (.....)         
     FETCH Customer INTO ....
END   

insert into test (id, vfirstname, vlastname)
select newid(), vFirstname, vLastName from customer

3B311450-65BB-4AF8-898B-0CD02C5086E2

declare @tblSource table(id int identity, FirstName varchar(50),LastName varchar(50))
declare @tblDest table(id varchar(max) , FirstName varchar(50),LastName varchar(50))
insert into @tblSource 
	select 'firstname1', 'lastname1' union all
	select 'firstname2', 'lastname2' union all
	select 'firstname3', 'lastname3' union all
	select 'firstname4', 'lastname4' union all
	select 'firstname5', 'lastname5' 

insert into @tblDest (id,FirstName,LastName)
select NEWID(),FirstName,LastName 
from @tblSource 

select * from @tblDest

id	FirstName	LastName
D2A3D81C-F76E-44A4-9D47-83FBE4DDB76B	firstname1	lastname1
0B827C0E-EB68-43EA-A423-B10575EAF572	firstname2	lastname2
EB4DB402-3D8F-4C09-9E37-004CE37DE1FD	firstname3	lastname3
E6CE9239-1E95-4660-B5AC-00DBFEE28474	firstname4	lastname4
345F70CC-B957-41CC-977C-9FBA7A81E912	firstname5	lastname5

declare @str varchar(max)
set @str = 'D2A3D81C-F76E-44A4-9D47-83FBE4DDB76B'
;with cte as(
select 1 as rn 
union all
select rn+1 from cte where rn<LEN(@str)),
cte2 as(
select rn,chars
from cte
cross apply(select SUBSTRING(@str,rn,1) chars)X
where chars like '%[0-9]%'
)
select numericData from (
select cast(chars as varchar(max))from cte2
for xml path(''))X(numericData)

numericData
23817644494783476