How to create temp table with data from 1 tables and with some variables

Question

I am trying to create a temp table.

SET @pstartDate = '01-01-2013'
SET @pendDate   = '31-12-2013'
SET @cstartDate = '01-01-2014'
SET @cendDate   = '31-12-2014'

CREATE TABLE #Temp
(
     pId int, 
     pname varchar, 
     ptype varchar, 
     pstartDate date, 
     pEndDate date, 
     cStartDate date, 
     cEndDate date
)

I will get pId, pname, ptype from product table.

SELECT * 
INTO #Temp 
FROM product

INSERT INTO #TempTable (pStartDate, pEndDate, cStartDate, cEndDate) 
VALUES (@pStartDate, @pEndDate, @cStartDate, @EndDate)

I want the value of column pStartDate as '01-01-2013', pEndDate as '31-12-2013', cstartDate as '01-01-2014' and cEndDate as '12-31-2014' for all the rows in #Temp table

I am trying in this way, but not getting values inserted into temp table.

[Bad habits to kick : declaring VARCHAR without (length)](http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx) - you should **always** provide a length for any `varchar` variables and parameters that you use. Your temp table right now has `pname` and `ptype` which are both **exactly 1 character** long - usually *not* what you want! — marc_s, Aug 28 '15 at 15:13
If you want the same value for those columns in every single row you need to use an update statement. Of course I would ask why bother since you have those values in variables. Just use the variables instead. — Sean Lange, Aug 28 '15 at 15:16

score 1 · Answer 1 · answered Aug 28 '15 at 15:16

You need to do something like this:

INSERT INTO #TempTable (pId, pname, ptype, pStartDate, pEndDate, cStartDate, cEndDate) 
select pId, pname, ptype, @pStartDate, @pEndDate, @cStartDate, @EndDate
from product

You can combine columns and variables in the select clause, to get the result you want.

How to create temp table with data from 1 tables and with some variables

1 Answers1