Sql-server – Inserting into Microsoft SQL Server Table

sql servert-sql

I have been using SQL for a bit now but have only recently tried out Microsoft SQL server Express. I have created a database called test and a table called idk. then I have tried to run the following query

USE test;
INSERT INTO idk(Id, Name) VALUES ("1","Name")

But that just says

Msg 207, Level 16, State 1, Line 2
Invalid column name '1'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Name'.

Any help for using SQL with Microsoft SQL server will be appreciated.

Best Answer

Use single quotes (') instead of double quotes (") for your values, such as:

USE test;
INSERT INTO idk(Id, Name) VALUES ('1','Name');

Using double quotes is one way to let SQL Server know you are specifying a column name instead of a value. Be careful about the data type for the ID. If it is an integer, SQL Server will convert the value in the query from a string to integer for you but it will be better to not have it as a string unless the data type is a string. For the ID, if it's an integer data type (int, bigint, smallint, or tinyint), then change the query as follows to remove the quotes from the integer value:

USE test;
INSERT INTO idk(Id, Name) VALUES (1,'Name');

Related Solutions

Sql-server – Selecting from an excel spreadsheet into SQL Server table

Excel is 32-bit, if you are using 64-bit version of Windows it could be causing this problem. I have 32 bit drivers installed and get the same error as you. In order to install the office 64 bit drivers would have to uninstall the 32 bit drivers first.

Sql-server – Check for column in backupset table

You have to use dynamic SQL to hide that column from the parser in 2005. All columns are checked to make sure they exist before the query runs.

DECLARE @sql nvarchar(MAX);

SET @sql = N'
SELECT DatabaseName = x.database_name,
        LastBackupDate = x.backup_finish_date,
        BackupSizeKB = x.backup_size,
        LastBackupFileName = x.device_name,
        left(right(x.device_name, 21),17) as Backup_Device_Date,
        type = x.type
        FROM (  SELECT  bs.database_name, bs.type,' +
                case 
                    when exists(
                        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
                    )
                    then N'cast(round(max(bs.compressed_backup_size/1024),0) as numeric)'
                    else N'cast(round(max(bs.backup_size/1024),0) as numeric)'
                end + N' as backup_size,
                max(bs.backup_finish_date) as backup_finish_date,
                max(bmf.physical_device_name) as device_name
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.is_copy_only = 0
          GROUP BY bs.type, bs.database_name) x
WHERE x.database_name not in (''master'', ''model'', ''msdb'')
AND x.type IN (''D'')
ORDER BY DatabaseName;';

EXEC(@sql);

Best Answer

Related Solutions

Sql-server – Selecting from an excel spreadsheet into SQL Server table

Sql-server – Check for column in backupset table

Related Question