Solved

Viewing a unigueidentifier key field in MS SQL

Posted on 2009-04-12
17
381 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Howard Bash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +1
17 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 24126563
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
 
LVL 1

Author Comment

by:Howard Bash
ID: 24127635
(1)
select
      FName,
      LName,
      CreateDateTime,
      EditDateTime
into PHONES
From OLD_PHONES

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





0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 200 total points
ID: 24127701

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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 1

Author Comment

by:Howard Bash
ID: 24129417
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 24131500
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
 
LVL 1

Author Comment

by:Howard Bash
ID: 24131861
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24133330
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24133850

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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24134267
>>The problem has not been resolved.<<
Than I am afraid I have no idea what you are talking about.
0
 
LVL 1

Author Comment

by:Howard Bash
ID: 24134384
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
 
LVL 1

Author Comment

by:Howard Bash
ID: 24134395
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
 
LVL 1

Author Comment

by:Howard Bash
ID: 24134416
sorry for the sloppy post.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24134559

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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 24134596
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
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 200 total points
ID: 24134606
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
 
LVL 1

Author Comment

by:Howard Bash
ID: 24134738
how do we identify this setting(s) ?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24134746
did you run my queries? what is the result?
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeā€¦
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question