|
Hi,
I know this is very easy... I just don't know how to do it:
We have the following table named Wzip_Detail
[Wzip_Detail_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WarehouseNum] [int] NULL
Wzip_Detail_ID will be automatically populated.
CountryCode will always be 1
WarehouseNum will always be 1
HOWEVER:
PostalCode for row 1 will be:
00000
PostalCode for row 2 will be:
00001
PostalCode for row 3 will be:
00002
PostalCode for the last row will be:
99999
So there will be rows 00000 to 99999 incrementally a total of 100,000 rows (I think)
How do I do this programatically in SQL Query Analyzer.
Again, I'm sure it is very easy... I just don't know how to do it yet....
Thank you so much in advance for any help you can give,
Anne
|
|
|
|
|
Here's a way to populate the PostalCode column. I would set the default value of the columns CountryCode and WarehouseNum to 1 instead of explictly inserting the value 1 each time.
<br />
DECLARE @PostalCode varchar(10)<br />
DECLARE @Number int<br />
SET @Number = 0<br />
<br />
WHILE @Number < 100000<br />
BEGIN<br />
<br />
IF LEN(@Number) = 1<br />
BEGIN<br />
SET @PostalCode = '0000' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 2<br />
BEGIN<br />
SET @PostalCode = '000' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 3<br />
BEGIN<br />
SET @PostalCode = '00' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 4<br />
BEGIN<br />
SET @PostalCode = '0' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 5<br />
BEGIN<br />
SET @PostalCode = CAST(@Number AS varchar)<br />
END<br />
<br />
<br />
INSERT INTO Wzip_Detail(PostalCode)<br />
VALUES(@PostalCode)<br />
<br />
<br />
SET @Number = @Number + 1<br />
END<br />
|
|
|
|
|
Thank you so much John for your response and your very clean programming style!!!
Anne
|
|
|
|
|
Using REPLICATE() is a little shorter:
DECLARE @PostalCode varchar(10)
DECLARE @Number int
DECLARE @cTemp varchar(10)
SET @Number = 0
WHILE @Number < 100000
BEGIN
SET @cTemp = CAST(@Number as VARCHAR(10))
SET @PostalCode = REPLICATE('0',5-LEN(@cTemp)) + @cTemp
INSERT INTO Wzip_Detail(PostalCode)
VALUES(@PostalCode)
SET @Number = @Number + 1
END
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
|
hi all,
which one faster? ODP.NET or System.Data.OracleClient.
I do a test on my computer. the result following:
--
System.Data.OracleClient
100
00:00:03.0156250
10'000
00:00:03.0156250
100'000
00:00:04.2187500
1'000'000
00:00:13.5000000
Oracle.DataAccess.Client
100
00:00:03.1875000
10'000
00:00:13.3750000
100'000
00:01:53.0000000
1'000'000
(I don't test this item I think it will be take a long time, so I don't test it.)
--
my computer environment
os: windows 2003 server enterprise edition - sp1
computer:
Intel(R) Celeron(R) CPU
2.53 GHz
2.55 GHz, 736MB of RAM.
--
How do you think about this? I thought the ODP.NET would be faster that System.Data.OracleClient before test.
|
|
|
|
|
|
when is the SQL sentence? in a .cs file? or in the Database procedure?
if it's in a .cs file, you can using
<br />
string[] strNumbers = { "1", "2", "3", "4", "5"};<br />
string strResult = string.Join(",", strNumbers);<br />
if it's in a database procedure, you need declare a variable for it,
then execute by Execute command. and treated the whole sentence as a string variable.
hoping this help.
|
|
|
|
|
Hi,
i have one table, the columns are ParentInvId(bigint),Invids(bigint)
now iam going to update the for a set of invids whoose paretnId is 2
for this how can i write a parameterized query using in statement
update(long ParentInvId,string Invids)
{
In this method i need to write update query
}
can u send the solution for this
|
|
|
|
|
sorry, we won't send you anything. we just want to tell you how to do it.
maybe you want using "update" sentence?
this is a sample:
update tablename set invids = 'newids' where parentnId = 2
|
|
|
|
|
A parameterized query uses parameters (yeah, really! ) and it is a little different depending on the .NET data provider you use. For SQL Server, it might look like this:
using(SqlConnection conn = new SqlConnection("conn string"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("UPDATE Customers SET Phone = @Phone WHERE CustomerId = @ID",
conn);
cmd.Parameters.Add("@Phone", newPhoneNumber);
cmd.Parameters.Add("@ID", customerId);
cmd.ExecuteNonQuery();
} For the generic OLE DB Data Provider, you use ? for each parameter, and must provide them in the order they appear on the query.
I hope this gives you an idea.
Luis Alonso Ramos
Intelectix
Chihuahua, Mexico Not much here: My CP Blog!
-- modified at 1:50 Thursday 25th May, 2006
|
|
|
|
|
hi all,
i want to query that create forenkey in my second table.
pls give the query
vipin paliwal
|
|
|
|
|
you must define diagram for your database that can define foriegn keys between your tables
|
|
|
|
|
What database are you using?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
Hi,
I am trying to update a dbf table using ado. If I were working with an access database I would update the datetime field with the following string:
UPDATE MyTable SET MyDateTimeField = #2006/05/23 17:00:00# WHERE . . .
How could a do it for a dBase table, I mean, how to specify the date and time in the SQL string?
Thanks in advance,
Vinicius
|
|
|
|
|
Use braces (not parenthesis) around the date instead of the octothorpe:
UPDATE MyTable SET MyDateTimeField = {05/23/2006 17:00} WHERE ...
There are other formats too:
{05/23/2006 03:00:00 PM} or {^2006/01/01 17:00} , etc.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hey guys, need to format a column of results to display as columns in another query.... let me explain a little better *hopefully*
in the result set below notice how the empID is repeated in some cases
ID empID phone_num displayTel type
----------- ----------- ------------ ---------- -----------
159 625 123-123-4567 1 1
163 625 465-456-4657 1 1
154 626 944-789-4515 1 1
155 626 984-321-4567 1 2
150 639 984-266-3336 1 1
145 802 465-465-7894 1 5
146 802 654-798-4657 1 6
165 853 132-123-1324 1 1
i need to beable to separate the "phone_num" data based on the "type" and then display that data in-line on another query
i.e.
Not This!!!
Name empID phone_num activeUser contact
----------- ----------- ------------ ---------- -----------
Demo User 625 123-123-4567 1 1
Demo User 625 465-456-4657 1 1
Instead Like This!!
Name empID phone_num1 phone_num2 activeUser contact
----------- ----------- ------------ ------------ ---------- -----------
Demo User 625 123-123-4567 465-456-4657 1 1
I need to be able to do this all at once while i'm also quering about 15 other tables.... so i need to be able to do it in-line or subqueried...
my only idea would be to do something like this ('course it won't work))
<br />
SELECT col1, col2, (if (select phone_num from phone where empID = @empIDNum) > 1 <br />
{<br />
for i = 1 to (select count(phone_num)) <br />
select top (i) phone_num as phone_num+(i) from Phone where empID = @empIDNum <br />
Next}<br />
)<br />
else<br />
{<br />
select phone_num from phone where empID = @empIDNum<br />
}<br />
from employees<br />
thanks!!!
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
Can you use a stored procedure?
As far as I know, you cannot do a cross-tab sql statement in SQL Server. There are ways to do it in a stored procedure though.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
EricDv,
Yes I can use a SP, my ultimate goal out of all this is to setup a dataset, for select and update
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
There's probably a better way, but I had fun with this.
It isn't pretty and I don't know how it will perform, - but here is one solution:
DECLARE @iColumns INT, @iCounter INT, @sql VARCHAR(2500)
SET @iCounter = 1
SET @sql = ''
select @iColumns = (SELECT TOP 1 COUNT(*) AS cnt FROM phonenumbers GROUP BY empid ORDER BY cnt DESC)
WHILE (@iCounter <= @iColumns)
BEGIN
SET @sql = @sql + ', Phone' + CAST(@iCounter AS VARCHAR) + ' VARCHAR(12)'
SET @iCounter = @iCounter + 1
END
SET @sql = 'CREATE TABLE #CrossPhones (empid INT' + @sql + ');'
+ 'DECLARE @sql2 VARCHAR(100),@iCounter INT,@iEmpIDHolder INT, @iEmpID INT, @phone_num VARCHAR(12), @teltype INT;'
+ 'DECLARE curNumbers CURSOR FOR SELECT empid,phone_num,teltype FROM phonenumbers p ORDER BY empid,teltype;'
+ 'OPEN curNumbers;'
+ 'FETCH NEXT FROM curNumbers INTO @iEmpID, @phone_num, @teltype;'
+ 'WHILE @@FETCH_STATUS = 0'
+ 'BEGIN'
+ ' SET @iEmpIDHolder = @iEmpID;'
+ ' INSERT INTO #CrossPhones (empid) VALUES (@iEmpID);'
+ ' SET @iCounter = 0;'
+ ' WHILE @iEmpIDHolder = @iEmpID'
+ ' BEGIN'
+ ' SET @iCounter = @iCounter + 1;'
+ ' SET @sql2 = ''UPDATE #CrossPhones SET Phone'' + CAST(@iCounter AS VARCHAR) + ''='''''' + CAST(@phone_num AS VARCHAR) + '''''' WHERE empid='' + cast(@iEmpID as VARCHAR);'
+ ' EXEC(@sql2);'
+ ' FETCH NEXT FROM curNumbers INTO @iEmpID, @phone_num, @teltype;'
+ ' IF @@FETCH_STATUS != 0 BREAK;'
+ ' END;'
+ 'END;'
+ 'CLOSE curNumbers;'
+ 'DEALLOCATE curNumbers;'
+ 'SELECT e.name,c.* FROM #CrossPhones c INNER JOIN Employees e ON c.empid = e.empid;'
EXEC(@sql)
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Alias your phone table and join to employees twice. Use a left join so you are sure to get every employee even if they don't have the certain type of phone number.
SELECT
Name,
empID,
a.phone_num as phone_num1,
b.phone_num as phone_num2,
activeUser,
contact
FROM
employees
LEFT JOIN
phone a
ON (employees.empID = a.EmpID AND
a.type = 1)
LEFT JOIN
phone b
ON (employees.empID = b.EmpID AND
b.type = 2)
You can also use a correlated sub query. This method won't be as fast as the first method.
SELECT
Name,
empID,
(SELECT TOP phone_num
FROM Phone
WHERE empId = employees.empId AND
type = 1) as phone_num1,
(SELECT TOP phone_num
FROM Phone
WHERE empId = employees.empId AND
type = 2) as phone_num2,
activeUser,
contact
FROM
employees
-- modified at 14:52 Thursday 25th May, 2006
|
|
|
|
|
Michael,
Thank you for your reply,
I find your soultion of using the multiple joins interesting...
however I have a question on it, the possiblity exist that there will be an unnumerable amount of phone_num results available for each user. (while the truth is we "attempt" to limit it to 4) How would you propose doing the multiple joins for x number of results?
My inital thought would be to use a while loop to loop thru all the results. but doing that still leaves the question of how to deal with the multiple results of the intial query...
for instance when you do a left join on these tables, because there are say 4 instances of a phone_num in the phone table per user, then my list of users (say 100) will return with 400 results. even if i do the multilple joins on the same table i'll still have 400 results from the inital query, since the join doesn't consume the results in any way.
--unless there is a way to include the TOP statement.... hmm, i'll look further into that....
If I figure that out then I'll post my resolution. -- 'course if you already know the resolution I'll happly accept assistance
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
Here is another solution, but with this no employee can have two of the same phone type:
DECLARE @iColumns INT, @sql VARCHAR(2500),@cTypeID VARCHAR(10)
SET @sql = ''
DECLARE curTypes CURSOR FOR SELECT DISTINCT CAST(teltype AS VARCHAR(10)) FROM PhoneNumbers
OPEN curTypes
FETCH NEXT FROM curTypes INTO @cTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', (SELECT phone_num FROM PhoneNumbers p WHERE p.empid = e.empid AND p.teltype = ' + @cTypeID + ') as Type' + @cTypeID
FETCH NEXT FROM curTypes INTO @cTypeID
END
close curTypes
DEALLOCATE curTypes
SET @sql = 'SELECT e.name,e.empid' + @sql + ' FROM employees e'
exec(@sql)
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hi,
Does anybody knows the MS Access equivalent for IS_Member(Role) in SQl.
The Is_Member() specifies wether the db user belongs to the group Role or not. if anyBody knows how to do this with MS Access please help.Any other suggestion is welcome
Thanks all
|
|
|
|
|