|
i was trying to add the column names in the existing table using the stored procedure in sql server 2000.
My stored procedure is
CREATE PROCEDURE [dbo].[spInsert_Master] <br />
<br />
AS<br />
declare @les varchar(1000)<br />
<br />
delete tblmaster<br />
create table tblMaster(categoryname varchar(100) , casestudy varchar(100) , UnitInformation varchar(100) , locationname varchar(100)) <br />
declare les cursor for select lessonname from tblLessonMaster<br />
open les<br />
fetch next from les into @les<br />
while(@@fetch_status =0)<br />
begin<br />
fetch next from les into @les<br />
alter table tblMaster add @less varchar(500);<br />
select @les as lessonname<br />
end<br />
close les<br />
deallocate les
I got the error in the following line
alter table tblMaster add @less varchar(500);
says error near @less
i think i can't add the @ expression in the column name.
any idea for the alternative ?
I want to add the column names in the existing table using stored procedure.
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
try replacing this line
alter table tblMaster add @less varchar(500);
with
SET @Str = 'alter table tblMaster add ' + @less ' varchar(500);'<br />
Exec (@Str)
also need to declare @Str variable at the begining
Regards
KP
|
|
|
|
|
how can i check duplicate column names exist or not in the table?
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
in SQL 2000 syscolumns contains all column names of each table and sysobjects contains all table names
Value 'U' in the column sysobjects.xtype indicates the objects is user table
id column in both tables are common which to be used for joining the two tables.
Regards
KP
|
|
|
|
|
can u tell me a query which gives me the result regarding ,whether the particular column name already exist or not in the table?
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
Assuming SQL Server 2000
if exists (select 1 from syscolumns where name = 'yourcolumnname' and id = object_id('yourtablename')<br />
begin<br />
select 'It exists'<br />
end<br />
else<br />
begin<br />
select 'its new'<br />
end
this will also work with SQL Server 2005 but you should be using the sys.xxx views
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i have a table which has large amount of data.
My job is
1. Move all the data into another table which is available in different server.
2. Creating index (Clustered index)
Now i want to know when i have to create index? before insert or after insert
Right now i am following after insert, i am creating index.
Please anyone let me know about this...,
D.Willington
|
|
|
|
|
Hi,
Based on what you have described. It would be better if you create index before insert the reason being is that when a query uses a nonclustered index, it can use the index efficiently to find the rows. However when it has to go back to the clustered index for any additional columns not present in the nonclustered index (that is called a "lookup").
Lookups can be expensive, especially if needs to do them for a lot of rows -- so extremely narrow nonclustered indexes tend to be limited use except when you're only working with a single column (or the indexed column and the clustered index columns -- keep in mind that nonclustered indexes include the table's clustering key at the leaf level).
Hope this will help you in moving forward in your task  .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Create the clustered index first.
Inside the stored procedure that populates the table with the inserts sort the data in the same order as the clustered index.
This is because the clustered index will have to do a lot of page splits if you just throw all that data at it in any order (depending on how many rows you have).
Clustered indexes are primarily of use for OLAP data - so don't go crazy with them if the data is for OLTP...
Regards
Guy
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
I have a stored procedure that returns the max. number of publication views by author. I will not pass the author name, my stored procedure will select the top 1 like here.
SELECT DISTINCT TOP 1
dbo.FnGetNoOfPublicationViewsByAuthor(C.CUSTOM2) AS 'TotalPublicationsViews',
C.CUSTOM2 AS 'Author'
FROM HDS_CUSTOM C
GROUP BY C.CUSTOM2
I want to get the value of this Author seperately to use in other select statements in the same SP. how can I get it. Is there possibility?
Thanks.
|
|
|
|
|
<br />
DECLARE @TotalPublicationsViews AS VarChar(100)<br />
DECLARE @Author AS VarChar(100)<br />
<br />
SELECT DISTINCT TOP 1<br />
@TotalPublicationsViews = dbo.FnGetNoOfPublicationViewsByAuthor(C.CUSTOM2),<br />
@Author = C.CUSTOM2 <br />
FROM HDS_CUSTOM C <br />
GROUP BY C.CUSTOM2<br />
<br />
SELECT @TotalPublicationsViews, @Author<br />
Declare variables as per their respective DataTypes
Regards
KP
|
|
|
|
|
hi friends
How to disable password complexity check while creating a new user.here I am getting the error message " The password does not meet Windows policy requirements because it is not complex enough".I want to disable this check.I used the script "EXEC dbo.sp_grantdbaccess @loginame = N'name', @name_in_db = N'name'" for creating user.
Thanks in advance
thomas
|
|
|
|
|
You can't disable it. This is a policy that is enforced on either the server (the entire Windows server) or domain wide, not just SQL Server. There is no way to disable this policy just to add a user and turn it back on.
|
|
|
|
|
Hi, I am getting a problem with my MySQL 5 database table while inserting a row which is having a primary key of two fields (A,B).
<br />
A=INTEGER<br />
B=AUTOINCREMENT FIELD<br />
PRIMARY KEY(A,B)
B is having maximum entry of 561629 (checked by query SELECT MAX(B) FROM TABLE1.
Whenever i used to insert next entry into data table with any integer value for A.
It is showing error "DUPLICATE ENTRY '232-561630'
But this type of entry is not shown with the query
SELECT * FROM TABLE WHERE B=561630
which is not already exit.
Is there a problem with my db server or anything else..?
|
|
|
|
|
I am not dead sure, but the problem *may* have something to do with MYSQL as well (people around here do not use a lot of MYSQL). Have you tried asking it in the MySql forums[^]?
Besides that, we have a separate site for MYSQL[^]now.
Nobody can give you wiser advice than yourself. - Cicero
.·´¯`·->Rajesh<-·´¯`·.
Codeproject.com: Visual C++ MVP
|
|
|
|
|
You need to increment the value of B after using it
The Developer is right all the time
No Fear The Developer is Here
|
|
|
|
|
currently i m using sql server 2000.
my column consist of mixture of country values.
i need to take the count of each countries.
example....
colA : india,japan,india,china,japan,USA,india,singapore,china,india
the output should be.....
india japan china USA singapore
4 2 2 1 1
how to achieve it? - KARAN
|
|
|
|
|
SELECT ColA, Count(*) <br />
FROM TblA <br />
GROUP BY ColA
Regards
KP
|
|
|
|
|
thx dude! 
|
|
|
|
|
This will not actuall give the required results. The original question wanted
india japan china USA singapore
4 2 2 1 1
This will give
india 4
japan 2
china 2
USA 1
singapore 1
You actually need to pivot the data.
This code should show you how to do it
<br />
<br />
create table #b1(cola varchar(20))<br />
<br />
insert into #b1<br />
select 'India'<br />
union all<br />
select 'USA'<br />
union all<br />
select 'India'<br />
union all<br />
select 'UK'<br />
<br />
<br />
<br />
<br />
<br />
DECLARE @SQL nvarchar(4000)<br />
SET @SQL='' <br />
<br />
SELECT @SQL= @SQL +<br />
'SUM(CASE WHEN cola=''' + a.cola + ''' THEN 1 ELSE 0 END) AS [' + a.cola + '],'<br />
FROM (select distinct cola from #b1) as a<br />
<br />
select @SQL = left(@SQL,len(@SQL)-1)<br />
<br />
SET @SQL='SELECT ' + @SQL + ' FROM #b1'<br />
EXEC(@SQL)
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
Im having two tables in sql server 2000 with same structure in first table if the current sysdate is equal to first table BDate column value means the first table that current sysdate record should be automatically insert to the second table,How can i acheive this one..any suggestions...
First Table:
---------------
Name BDate Content
-------------------------------
Kanna 12/5/2008 Hapy birthday
Second Table:
--------------
Name BDate Content
-------------------------------
Regards
Kanna
|
|
|
|
|
Create a job to fetch all rows from first table that matches to the current date and copies to second table
Schedule the job to execute daily
Regards
KP
|
|
|
|
|
Thanks for ur reply how should i create job.. im new to this concept..can u give me the example code???
regards
Kanna
|
|
|
|
|
From SQL Enterprise Manager, select your server instance and go to
Management -> SQL Server Agent -> Jobs
right click "New Job"
write your SQL query under "step"
then schedule the job
Regards
KP
|
|
|
|
|
Hi everyone,
Does anyone know of an easy way or trick to switch between System.Data.OracleClient and System.Data.SqlClient (and later MySQL's .NET Connector)? I want to give the user the choice of database to use without writing the same database code twice - once for each type of database.
I appreciate all comments or ideas. Thanks!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
