display SQL Server: t-sql

Showing posts with label t-sql. Show all posts

Friday, March 23, 2012

How do I insert into existing Temp table?

Hi,

How do I insert data into an existing temporary table? Note: Im primarily a .NET programmer who has to do T-SQL to grab data from time to time.

What I am trying to do is this:
1) Put the scores for all the people who have completed a questionnaire into a temporary table called #GroupConfidence.
2) Add on a row at the end that gives an average for each score (ie the last row is an average of the column above).

I need my SP to give me a DataSet that I can throw straight to my .NET reporting engine (I dont want to do any number crunching inside .NET) - that's why I want to add on the 'average' row at the end.

If I do this (below) the temporary table (#GroupConfidence) gets created and the values inserted.

-- Insert the results into the #GroupConfidence table
SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO #GroupConfidence
FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

My problem is that #GroupConfidence already exists so in fact I have this code below:

CREATE TABLE #GroupConfidence
( [FullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SubmitID] [int] NOT NULL,
[GL_Score] [decimal](19, 10) NOT NULL,
[GP_Score] [decimal](19, 10) NOT NULL,
[GPH_Score] [decimal](19, 10) NOT NULL,
[DL_Score] [decimal](19, 10) NOT NULL,
[MP_Score] [decimal](19, 10) NOT NULL,
[Role_MI_Score] [decimal](19, 10) NOT NULL,
[Role_ASXRE_Score] [decimal](19, 10) NOT NULL,
[Role_APRA_Score] [decimal](19, 10) NOT NULL,
[AllCategories] [decimal](19, 10) NOT NULL
)

-- Insert the results into the #GroupConfidence table
SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO #GroupConfidence
FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

So I get this error: Server: Msg 2714, Level 16, State 1, Line 109
There is already an object named '#GroupConfidence' in the database.

Thanks in advance,

Ian.SELECT .... INTO <NEWTABLE> FROM <ANOTHER TABLE> will create the table and then insert the rows of the SELECT statement. You are getting the error because first you had created the table using "CREATE TABLE" statement then again are using SELECT ... INTO statment to create the table and insert the rows.

If you want to perform CREATE and INSERT operations in two statements then you can execute below statements:

CREATE TABLE #GroupConfidence ...

INSERT INTO #GroupConfidence SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories' FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID

Hope the above helps you.|||EXCELLENT - I knew it had to be simple. But unless you know it just isn't going to happen.

So thanks for this.

Ian.

:D

Monday, March 12, 2012

How do I get a list of tables in T-SQL

Hi,
Is there anything equivalent to Oracle'sSelect * from tab in MS SQL.I have got the answer


select name from sysobjects where type = 'U'

|||That will work, but try one of these 2 alternate methods to assure forward-compaitbility with future SQL Server versions:

EXEC sp_table

SELECT * FROM INFORMATION_SCHEMA.TABLES

Note that each of the 3 options yields slightly different results as they filter the information differently. Generally sys* tables should not be queried directly as they are subject to chage.

Terri

Wednesday, March 7, 2012

How do I find IDENTITY columns on Table using T-SQL

Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?

Found it, a little obscure:

SELECT obj.[name], col.[name], col.[colstat], col.*
FROM [syscolumns] col
JOIN [sysobjects] obj
ON obj.[id] = col.[id]
WHERE obj.type = 'U'
AND col.[status] = 0x80
ORDER BY obj.[name]

Does anyone know a way of doing this using an INFORMATIO_SCHEMA view?

|||I posted that sometime ago:

SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
FROM syscolumns WHERE OBJECT_NAME(id) = sometable_test'

Mit Information_schema views from
http://weblogs.asp.net/psteele/archive/2003/12/03/41051.aspx

select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1
order by TABLE_NAME

HTH, Jens Suessmeyer.

http://www.sqlserver2005.,de

|||

Here is some more ( in technicolor ;-) )

USE northwind
GO

DECLARE @.tableName VARCHAR(50)
SELECT @.tableName = 'orders'

--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @.tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO

DECLARE @.intObjectID INT
SELECT @.intObjectID =OBJECT_ID('orders')

--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@.intObjectID, 'TableHasIdentity'),0) AS HasIdentity

Denis the SQL Menace

http://sqlservercode.blogspot.com/

How do I enable this permently?

http://support.microsoft.com/default...b;en-us;328151
<The T-SQL Debugger is turned off by default for earlier clients after =
you install SQL Server 2000 Service Pack 3>
SQL 2000 SP3, Microsoft Vicual Studio 6 SP5, Microsoft Windows 2000 SP4. =
Thanks.
--=20
George Hester
_________________________________
Upgrade the clients to the same service pack levels.
Sincerely,
Anthony Thomas

"George Hester" <hesterloli@.hotmail.com> wrote in message
news:eMNKmw48EHA.3944@.TK2MSFTNGP12.phx.gbl...
http://support.microsoft.com/default...b;en-us;328151
<The T-SQL Debugger is turned off by default for earlier clients after you
install SQL Server 2000 Service Pack 3>
SQL 2000 SP3, Microsoft Vicual Studio 6 SP5, Microsoft Windows 2000 SP4.
Thanks.
George Hester
_________________________________
|||I'm not sure what you mean? What clients? I have Visual Studio 6 SP5 =
SQL 2000 SP3 and Windows 2000 SP4. I'm not really looking to change =
that configuration. Can't it be done with a startup parameters or =
something? Thanks.
--=20
George Hester
_________________________________
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =
news:uAdpeE58EHA.1204@.TK2MSFTNGP10.phx.gbl...
> Upgrade the clients to the same service pack levels.
>=20
> Sincerely,
>=20
>=20
> Anthony Thomas
>=20
>=20
> --=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:eMNKmw48EHA.3944@.TK2MSFTNGP12.phx.gbl...
> http://support.microsoft.com/default...b;en-us;328151
>=20
> <The T-SQL Debugger is turned off by default for earlier clients after =
you
> install SQL Server 2000 Service Pack 3>
>=20
> SQL 2000 SP3, Microsoft Vicual Studio 6 SP5, Microsoft Windows 2000 =
SP4.
> Thanks.
>=20
> --=20
> George Hester
> _________________________________
>=20
>=20

How do I enable this permently?

http://support.microsoft.com/defaul...kb;en-us;328151
<The T-SQL Debugger is turned off by default for earlier clients after =
you install SQL Server 2000 Service Pack 3>
SQL 2000 SP3, Microsoft Vicual Studio 6 SP5, Microsoft Windows 2000 SP4. =
Thanks.
--=20
George Hester
_________________________________Upgrade the clients to the same service pack levels.
Sincerely,
Anthony Thomas
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:eMNKmw48EHA.3944@.TK2MSFTNGP12.phx.gbl...
http://support.microsoft.com/defaul...kb;en-us;328151
<The T-SQL Debugger is turned off by default for earlier clients after you
install SQL Server 2000 Service Pack 3>
SQL 2000 SP3, Microsoft Vicual Studio 6 SP5, Microsoft Windows 2000 SP4.
Thanks.
George Hester
_________________________________|||I'm not sure what you mean? What clients? I have Visual Studio 6 SP5 =
SQL 2000 SP3 and Windows 2000 SP4. I'm not really looking to change =
that configuration. Can't it be done with a startup parameters or =
something? Thanks.
--=20
George Hester
_________________________________
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =
news:uAdpeE58EHA.1204@.TK2MSFTNGP10.phx.gbl...
> Upgrade the clients to the same service pack levels.
>=20
> Sincerely,
>=20
>=20
> Anthony Thomas
>=20
>=20
> --=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:eMNKmw48EHA.3944@.TK2MSFTNGP12.phx.gbl...
> http://support.microsoft.com/defaul...kb;en-us;328151
>=20
> <The T-SQL Debugger is turned off by default for earlier clients after =
you
> install SQL Server 2000 Service Pack 3>
>=20
> SQL 2000 SP3, Microsoft Vicual Studio 6 SP5, Microsoft Windows 2000 =
SP4.
> Thanks.
>=20
> --=20
> George Hester
> _________________________________
>=20
>=20

How do I enable this permently?

http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;328151
<The T-SQL Debugger is turned off by default for earlier clients after = you install SQL Server 2000 Service Pack 3>
SQL 2000 SP3, Microsoft Vicual Studio 6 SP5, Microsoft Windows 2000 SP4. = Thanks.
-- George Hester
_________________________________Upgrade the clients to the same service pack levels.
Sincerely,
Anthony Thomas
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:eMNKmw48EHA.3944@.TK2MSFTNGP12.phx.gbl...
http://support.microsoft.com/default.aspx?scid=kb;en-us;328151
<The T-SQL Debugger is turned off by default for earlier clients after you
install SQL Server 2000 Service Pack 3>
SQL 2000 SP3, Microsoft Vicual Studio 6 SP5, Microsoft Windows 2000 SP4.
Thanks.
--
George Hester
_________________________________|||I'm not sure what you mean? What clients? I have Visual Studio 6 SP5 =SQL 2000 SP3 and Windows 2000 SP4. I'm not really looking to change =that configuration. Can't it be done with a startup parameters or =something? Thanks.
-- George Hester
_________________________________
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:uAdpeE58EHA.1204@.TK2MSFTNGP10.phx.gbl...
> Upgrade the clients to the same service pack levels.
> > Sincerely,
> > > Anthony Thomas
> > > -- > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:eMNKmw48EHA.3944@.TK2MSFTNGP12.phx.gbl...
> http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;328151
> > <The T-SQL Debugger is turned off by default for earlier clients after =you
> install SQL Server 2000 Service Pack 3>
> > SQL 2000 SP3, Microsoft Vicual Studio 6 SP5, Microsoft Windows 2000 =SP4.
> Thanks.
> > -- > George Hester
> _________________________________
> >=20

Sunday, February 19, 2012

How do I determine if SQL Sever Login is disabled using T-Sql?

How do I determine if SQL Sever Login is disabled using T-Sql?
I can disable it and enabled it using the following T-Sql function:
ALTER LOGIN login [ENABLE | DISABLE]
I just want to know if it is enable or disabled?It's stored in the system table sys.server_principals, in the column called
is_disabled
However, in SQL Server 2005 system tables are not visible by default; you
must use the Dedicated Administrator Connection (DAC) which you can read
about in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Hi,
You can also query the system view and look into column is_disabled.1
denotes the login is disabled
select * from sys.sql_logins
Tahnks
Hari
SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Thanks Hari. I totally blew that answer. I was doing some testing, and was
in a connection that was not a sysadmin, just a regular user. So when I
couldn't see server_principals, I assumed it was because it was a system
table.
Then of course logging in using the DAC, I was sysadmin, so I could see that
view.
So sys.server_principals is the answer.
If you check the BOL for sys.sql_logins, you'll see that it is a view based
on sys.server_principals, containing all the columns from that view, plus a
view others.
--
HTH
Kalen Delaney, SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> Hi,
> You can also query the system view and look into column is_disabled.1
> denotes the login is disabled
> select * from sys.sql_logins
>
> Tahnks
> Hari
> SQL Server MVP
> <rodrigo.gloria@.gmail.com> wrote in message
> news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
>> How do I determine if SQL Sever Login is disabled using T-Sql?
>> I can disable it and enabled it using the following T-Sql function:
>> ALTER LOGIN login [ENABLE | DISABLE]
>> I just want to know if it is enable or disabled?
>|||Kalen Delaney wrote:
> Thanks Hari. I totally blew that answer. I was doing some testing, and was
> in a connection that was not a sysadmin, just a regular user. So when I
> couldn't see server_principals, I assumed it was because it was a system
> table.
> Then of course logging in using the DAC, I was sysadmin, so I could see that
> view.
> So sys.server_principals is the answer.
> If you check the BOL for sys.sql_logins, you'll see that it is a view based
> on sys.server_principals, containing all the columns from that view, plus a
> view others.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> > Hi,
> >
> > You can also query the system view and look into column is_disabled.1
> > denotes the login is disabled
> >
> > select * from sys.sql_logins
> >
> >
> >
> > Tahnks
> > Hari
> > SQL Server MVP
> >
> > <rodrigo.gloria@.gmail.com> wrote in message
> > news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> >> How do I determine if SQL Sever Login is disabled using T-Sql?
> >>
> >> I can disable it and enabled it using the following T-Sql function:
> >>
> >> ALTER LOGIN login [ENABLE | DISABLE]
> >>
> >> I just want to know if it is enable or disabled?
> >>
> >
> >
Thank you very much.

Friday, March 23, 2012

How do I insert into existing Temp table?

Monday, March 12, 2012

How do I get a list of tables in T-SQL

Wednesday, March 7, 2012

How do I find IDENTITY columns on Table using T-SQL

How do I enable this permently?

How do I enable this permently?

How do I enable this permently?

Sunday, February 19, 2012

How do I determine if SQL Sever Login is disabled using T-Sql?

How do I determine if SQL Sever Login is disabled using T-Sql?

display SQL Server

Blog Archive

About Me