SQLTeam.com | Weblogs | Forums

Column name repeats for all rows

Transact-SQL
#1

Hello,

I imported a file called sanfranciscofilmlocations.csv and one of the column headings is "Release Year". I tried the following statement but it gives "Release Year" for all rows. Could you help me with fixing it?

SELECT "Release Year" FROM sanfranciscofilmlocations;

As far as I understood, column headings cannot include two or more words but has to be linked; eg. "Release_Year" instead of "Release Year". I also tried to change the .csv file in that way but couldn't save as a .csv.

I searched online to come up with a solution, but didn't find a method. Please note that I am new to SQL.

#2

You can use spaces but only if you include the entire column name in " or [].

You can rename the column if you want to:

EXEC sys.sp_rename 'dbo.sanfranciscofilmlocations.Release Year', 'Release_Year', 'COLUMN'

#3

Hi Scott,

Thanks for your reply. When I included the entire column name in " " as below, I still get the error as shown below the command.

SELECT "Release Year" FROM sanfranciscofilmlocations;

Release Year
Release Year
Release Year
Release Year
Release Year

Following commands give the error in the command line itself.
SELECT [Release Year] FROM sanfranciscofilmlocations;

EXEC sys.sp_rename 'dbo.sanfranciscofilmlocations.Release Year', 'Release_Year', 'COLUMN';

#4

first try this

SELECT * FROM sanfranciscofilmlocations;
#5

Your QUOTED_IDENTIFIER setting is OFF (which is not good, not standard), therefore double quotes are taken the same as single quotes.

Use brackets instead:

SELECT [Release Year] FROM sanfrancisofilmlocations;

#6

Hi Yosiasz,

The following worked.

SELECT * FROM sanfranciscofilmlocations;

But not this

SELECT "Release Year" FROM sanfranciscofilmlocations;

#7

Hi Scott,

How to turn on the QUOTED_IDENTIFIER?

I tried several options including SET QUOTED_IDENTIFIER ON

and SET QUOTED_IDENTIFIER { ON | OFF }

Both together and separately. But it gave an error.

#8

Like this:

SET QUOTED_IDENTIFIER ON;

#9

that is not a valid query. all it will do is return all rows with
"Release Year"

What are the columns in sanfranciscofilmlocations table?

#10

Doesn't work

#11

Title Release Year Locations Fun Facts Production Company Distributor

Director Writer Actor 1 Actor 2 Actor 3

#12

not really helpful as we can't tell which column ends where but try this anyways

select Title, [Release Year], Locations, [Fun Facts] from sanfranciscofilmlocations

#13

I think I downloaded csv file from searching SanFranciscoFilmLocations.csv on google.

It's the first item appears on the top (it doesn't allow me to send the link). At the bottom of the website (San Fransisco government website), you can see the table.

#14

select Title, [Release Year] , Locations, [Fun Facts] from sanfranciscofilmlocations;
gives the error code 1064

select Title, 'Release Year' , Locations, 'Fun Facts' from sanfranciscofilmlocations;
this repeats "Release Year" and "Fun Facts" for each row.

If you can suggest a way to change "Release Year" to "Release_Year" in the table, I think it would work.

#15

I think the problem is that the table was created with quoted identifiers turned off. The table would need to be rebuilt with that setting turned on.

You can modify the column names using the following:

EXEC sp_rename 'dbo.ErrorLog.ErrorTime', 'ErrorDateTime', 'COLUMN';

See here: sp_rename (Transact-SQL) - SQL Server | Microsoft Docs

#16

I did that in my very post for this q.

#17

do

SELECT "Release Year" FROM sanfranciscofilmlocations

and post an image of your SQL results here

#18

Screen Shot 2022-03-02 at 1.54.56 PM
Screen Shot 2022-03-02 at 1.54.56 PM2355×1543 497 KB

#19

oops meant to say

SELECT * FROM sanfranciscofilmlocations

Also this is a Microsoft SQL Server forum

#20

Screen Shot 2022-03-02 at 2.51.58 PM
Screen Shot 2022-03-02 at 2.51.58 PM1920×1261 151 KB