Viewing a unigueidentifier key field in MS SQL

I have a table defined as follows in the code window.  I can insert values into the table, but cannot do a select * from the table and see the primary key uniqueidentifier column.

Please advise.

Thanks,
Howard


CREATE TABLE [dbo].[ClientLogin](
	[ClientGuid] [uniqueidentifier] NOT NULL DEFAULT (newid()),
	[FName] [varchar](50) NULL,
	[LName] [varchar](50) NULL,
	[CreateDateTime] [datetime] NOT NULL DEFAULT (getdate()),
	[EditDateTime] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_ClientLogin] PRIMARY KEY CLUSTERED 
(
	[ClientGuid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Open in new window

LVL 1
Howard BashSenior Software EngineerAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
So where do we stand?  Has the problem been resolved and if not can you post the query and table structure that is causing you grief?
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
can you tell/show us how you actually:
* INSERT into the table
* SELECT from the table
* the results from the select after the insert
0
 
Howard BashSenior Software EngineerAuthor Commented:
(1)
select
      FName,
      LName,
      CreateDateTime,
      EditDateTime
into PHONES
From OLD_PHONES

(2)
  select ClientGuid from Phones <--fails with "Invalid column name 'PhoneNumberGuid"





0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
SharathConnect With a Mentor Data EngineerCommented:

In the original question, you have provided script to create table with name "ClientLogin".
But in your post (24127635), you mentioned some other table in the INTO clause (PHONES).
Do you want to extract the records from OLD_PHONES table and insert them into your "ClientLogin" table?
If yes, you can try like this.
-- You can create your table like this.
create TABLE [dbo].[ClientLogin](
	[ClientGuid] int identity,
	[FName] [varchar](50) NULL,
	[LName] [varchar](50) NULL,
	[CreateDateTime] [datetime] NOT NULL DEFAULT (getdate()),
	[EditDateTime] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_ClientLogin] PRIMARY KEY CLUSTERED 
(
	[ClientGuid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
 
-- Insert the data into your table ClientLogin with the below statement
insert ClientLogin 
select Fname,Lname,CreateDateTime,EditDateTime from Old_Phones
 
-- Now query the table ClientLogin
select ClientGuid from ClientLogin

Open in new window

0
 
Howard BashSenior Software EngineerAuthor Commented:
My mistake,  the table that I should have included the DDL for is the Phones table and the guid is of type "uniqueidentifier"  not  "int" and the select does not show this column.


0
 
Howard BashSenior Software EngineerAuthor Commented:
The problem has not been resolved.  As I stated,  the table with a uniqueidentifier for it's primary key does not show that column on a select * from tablename query.

I have posted the DDL and the query above.  The only differerence is that the table name is Phone not as listed above.

Thanks,
Howard
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, you write:

select
      FName,
      LName,
      CreateDateTime,
      EditDateTime
into PHONES
From OLD_PHONES

and you complain about the table "phones" not do have a ClientGuid column?

please clarify
0
 
SharathData EngineerCommented:

If you have table in hand and want to insert the data then use INSERT INTO TableName SELECT ... FROM OtherTable
If you don't have the table, and want to create and insert data from other table, use SELECT * INTO ... FROM ...
Here, you provided DDL script(ClientLogin) to create the table and, you are trying to insert data into some other table (PHONES).
The PHONES table does not have ClientGuid column, check your SELECT <columnlist> INTO PHONES FROM OLD_PHONES statement.
Please check the below script.
-- Create your table with your DDL script. I have changed the table name from ClientLogin to PHONES 
create TABLE [dbo].[PHONES](
	[ClientGuid] [uniqueidentifier] NOT NULL DEFAULT (newid()),
	[FName] [varchar](50) NULL,
	[LName] [varchar](50) NULL,
	[CreateDateTime] [datetime] NOT NULL DEFAULT (getdate()),
	[EditDateTime] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_ClientLogin] PRIMARY KEY CLUSTERED 
(
	[ClientGuid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
 
-- Insert the data into your table PHONES with the below statement. Mention a value for ClientGuid field also.
insert PHONES 
select NEWID(),Fname,Lname,CreateDateTime,EditDateTime from Old_Phones
 
-- Now query the table PHONES
select ClientGuid from PHONES

Open in new window

0
 
Anthony PerkinsCommented:
>>The problem has not been resolved.<<
Than I am afraid I have no idea what you are talking about.
0
 
Howard BashSenior Software EngineerAuthor Commented:
Ok.  Allow me to try again.  After I create that table named Phones with a pk uniqueidentifier and several other fields,  my select * from that table shows every column except the PK.

If you wanted to do that how would you?


sqlprob.docx
0
 
Howard BashSenior Software EngineerAuthor Commented:
Here is the DDL for the table too:


USE [somedb]
GO
/****** Object:  Table [dbo].[Phones]    Script Date: 04/13/2009 21:41:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Phones](
	[PhoneNumberGuid] [uniqueidentifier] NOT NULL DEFAULT (newid()),
	[LocationGuid] [bigint] NULL,
	[CompanyGuid] [bigint] NULL,
	[CountryCode] [varchar](10) NULL,
	[AreaPrefixCode] [varchar](12) NULL,
	[DestinationPage] [varchar](50) NULL,
	[PublicFlag] [bit] NULL,
	[PhoneNumber] [nvarchar](50) NULL,
	[PhoneType] [varchar](12) NULL,
	[PhoneFormatName] [varchar](50) NULL,
	[PhoneDefault] [varchar](50) NULL,
	[PhoneURL] [nchar](128) NULL,
	[CreateDateTime] [datetime] NOT NULL DEFAULT (getdate()),
	[EditDateTime] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_PhoneReference] PRIMARY KEY CLUSTERED 
(
	[PhoneNumberGuid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Open in new window

0
 
Howard BashSenior Software EngineerAuthor Commented:
sorry for the sloppy post.
0
 
SharathData EngineerCommented:

That is strange. Can you run these queries and check whether any record is returned.
USE [somedb]
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Phones' and COLUMN_NAME = 'PhoneNumberGuid'
select *
  from sys.tables T
  join sys.columns C on T.object_id = C.object_id
 where T.name = 'Phones' and C.name = 'PhoneNumberGuid'

From the screenshot, i come to know that, you are working in Express edition, but as per my knowledge,that will not be a problem.

0
 
Anthony PerkinsConnect With a Mentor Commented:
CREATE TABLE [dbo].[Phones](
      [PhoneNumberGuid] [uniqueidentifier] NOT NULL DEFAULT (newid()),
      [LocationGuid] [bigint] NULL,
      [CompanyGuid] [bigint] NULL,
      [CountryCode] [varchar](10) NULL,
      [AreaPrefixCode] [varchar](12) NULL,
      [DestinationPage] [varchar](50) NULL,
      [PublicFlag] [bit] NULL,
      [PhoneNumber] [nvarchar](50) NULL,
      [PhoneType] [varchar](12) NULL,
      [PhoneFormatName] [varchar](50) NULL,
      [PhoneDefault] [varchar](50) NULL,
      [PhoneURL] [nchar](128) NULL,
      [CreateDateTime] [datetime] NOT NULL DEFAULT (getdate()),
      [EditDateTime] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_PhoneReference] PRIMARY KEY CLUSTERED
(
      [PhoneNumberGuid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Insert Phones (PhoneNumberGuid) Values (NEWID())
Insert Phones (PhoneNumberGuid) Values (NEWID())
Insert Phones (PhoneNumberGuid) Values (NEWID())
Insert Phones (PhoneNumberGuid) Values (NEWID())

Select PhoneNumberGuid
From Phones

Drop table Phones

Output:
EF737BB5-3466-4B6C-9F09-2B8651BF2270
DC3DA3F3-F222-4E23-918A-B054D9C79DBD
049CF85D-E8A4-45F2-9407-C07697755607
79B4191E-4A1A-4439-ADD5-E520FA33EE2F
0
 
SharathConnect With a Mentor Data EngineerCommented:
acperkins -  even i tested the same, by creating the tabe (with the DDL provided by asker).  
When the asker said, no PK column displayed with SELECT *, wondered whether SQL server behaving differently on different machines :)
0
 
Howard BashSenior Software EngineerAuthor Commented:
how do we identify this setting(s) ?
0
 
SharathData EngineerCommented:
did you run my queries? what is the result?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.