How to INSERT INTO table column with string/variable

Question

This is the data I have pulled from powershell and inserted it into a #temptable:

Name                   : SULESRKMA
Location               : Leisure Services - Technology Services
Shared                 : False
ShareName              : 
JobCountSinceLastReset : 0
PrinterState           : 131072
Status                 : Degraded
Network                : False

I'm while looping through the data and have stripped the values from the identifiers. I'd like to use these identifiers to insert the values into a table with identical Column names to the identifiers. So for example, I have a variable called @identifier = "Name" and a temp table #printers with a column name of Name. I'd like to do something like:

SELECT --select statement
INSERT INTO #printers(@identifier)

But This doesn't seem to work, unsurprisingly. Is there a way to accomplish this? (The @identifier variable will be changing to the other identifiers in the data throughout the course of the while loop.)

Any alternate suggestions that don't even involve using this sort of method are welcome. My ultimate goal is just to get this data as a row into a table.

(I'm currently using Microsoft SQL Server Management Studio if that matters)

S3S · Accepted Answer · 2016-10-14 18:42:12Z

1

First, it's unlikely you need to loop over anything in this situation. Think set based operations when you think about SQL.

INSERT INTO #temptable (Column1Name, Column2Name, Column3Name)
VALUES (@identifer, @anotherIdentifier, @someOtherIdentifier)
--optional clauses
WHERE Column1Name = 'some value' OR Column1Name = @someIdentifier

Or you can SELECT INTO

SELECT
@identifier,
@anotherIdentifer,
@someOtherIdentifier
INTO #temptable

It's important that you have a value in your SELECT INTO for each column in the table which you are trying to add the data to. So, for example, if there were 4 columns in #temptable and you only had 3 values to insert (columns 1, 2 , and 3) then you'd need to NULL column 4 or set it statically.

SELECT
@identifier,
@anotherIdentifer,
@someOtherIdentifier,
NULL
INTO #temptable
--or
    SELECT
@identifier,
@anotherIdentifer,
@someOtherIdentifier,
'static value'
INTO #temptable

EDIT

If you want to use a varible to speciy the column that you want to insert into, you have to use dynamic sql. Here is an example:

if object_id ('tempdb..#tempTable') is not null drop table #tempTable
create table #tempTable (Column1Name int, Column2Name int, Column3Name int)

declare @columnName varchar(64) = 'Column1Name'
declare @sql varchar(max)

set @sql = 
'insert into #tempTable (' + @columnName + ')
select 1'

exec(@sql)

select * from #tempTable

edited Oct 14, 2016 at 18:42

answered Oct 14, 2016 at 17:44

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

TheEggSample Over a year ago

The thing is, I don't want to specify Column1Name, Column2Name, etc. I want some way of saying "I want to put '@value' into a column specified by '@identifier'" I don't want to place '@identifier' into the table, I want to specify the column with it. Does that make sense?

TheEggSample Over a year ago

The reason for this trouble is because I don't want to have to define 10 variables or columns to be placing data in. I feel like it makes the query less reusable. This is why I'm while looping through the identifiers (such as 'Name', 'Location', 'Shared', etc. (I'll be reusing this code in an instance where there will be up to 25 different identifiers).

S3S Over a year ago

ah it does @SaniT404 i edtied it above

S3S Over a year ago

I understand @SaniT404, you can use your loop for the variable assignment, that is assigning the correct column name to @columnName or what ever you use in your cursor.

HABO Over a year ago

Tip: When using dynamic SQL, always prepare for a visit from Bobby Tables.

|

Collectives™ on Stack Overflow

How to INSERT INTO table column with string/variable

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related