INSERT using SELECT and variables?

[Ian W]

Hi,

I've written a INSERT INTO statement to add a new row to a table.

I want to try and expand on this as the table has 16 colunms, 10 of these I could pull direct from another table but the remaining fields I must insert using VALUES...(variable1, variable2..) etc

Just to add to this, the table columns are labelled differently if that will make a difference.

Can this be done? Can I use SELECT and set some variables to be written to the table in the same row?

 

[r3plica]

sure you can

DECLARE @Column1 VARCHAR(100)
DECLARE @Column2 VARCHAR(100)
SET @Column1 = 'Test'
SET @Column2 = 'Test'

INSERT dbo.tTestTable
SELECT 
	product,
	[description],
	@Column1,
	order_no,
	andothercolums,
	@Column2
FROM dbo.tTestTable2

it doesn't matter if the columns are different names as long as you select the columns in the right order and they are not conflicting datatypes (trying to insert a date into an int column for example).