Insert Data from 1 table into another (w/ a twist)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert Data from 1 table into another (w/ a twist)

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-06-01 : 11:35:44

I'd like to insert data from a temporary table into a table.

@tblTemp (first, second, third)

tblFinal (date, first, second, third)

Right now I can do the following...


INSERT INTO tblFinal (first, second, third)
SELECT first, second, third
FROM @tblTemp

Easy enough. But what if I wanted to insert today's date into tblFinal.date from the INSERT statement above?


DECALRE @Day
Set @Day=convert(varchar(10),getdate(),101)

INSERT INTO tblFinal (date, first, second, third)
/* Not sure of this part - Inserting today's date with the Select statement that pulls data from the temp table */

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-01 : 11:57:39

Something like:

INSERT INTO tblFinal (date, first, second, third)
SELECT @day, first, second, third
FROM @tblTemp

Is Date a DATETIME or a VARCHAR field?

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-06-01 : 12:00:27

datetime

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-01 : 12:08:06

In that case, i'd change your variable declaration and assignment to:

DECLARE @Day DATETIME
SET @Day = DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)

Assuming you are trying to store the date without the time.

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-06-01 : 12:13:39

Genius. That was easy enough! Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 13:07:45

in fact there's no need of variable here, you can directly use expression in select


INSERT INTO tblFinal (date, first, second, third)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0), first, second, third
FROM @tblTemp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources