passing a parameter for insert as a value into tbl

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

SQL Server 2000 Forums

Transact-SQL (2000)

passing a parameter for insert as a value into tbl

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-10-13 : 17:26:48

I currently have a program which reads from a table (A) and inserts selected fields (asofdate,name,address,city,state)
into another table (B). My program then needs to append 3 additional records into table B.
This process will need to be run monthly. The 3 records I append will always stay the same except for the asofdate.

The asofdate for the appended 3 records should be the same as the asofdate in table A. The asofdate in table A will
always be the same for all records.

I don't want to modify the program monthly to change the asofdate VALUES. Is there a way, where the program can store the asofdate from
one of the records inserted into the B table as a value in my insert records? Currently, its set to 7/31/2010.

Hoping someone can show me how this can be coded. Thank you for your time in advance.

Sample of my program.
---------------------
SELECT asofdate, name, address, city, state from A
INSERT into B;

INSERT into B
(asofdate, name, address, city, state)
values ('07/31/2010','mike','444 Rodeo Dr.','Des Plaines','CA'),
('07/31/2010','john','333 Apple ln.','Chicago','IL'),
('07/31/2010','dave','222 Aile St.','Prosepct','IL')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-13 : 17:46:51

if the asofdate is datatype datetime.
if there are many rows in table A.
if the monthly new needed asofdate is always the youngest date.

insert into B(asofdate, name, address, city, state)
select max(asofdate),'mike','444 Rodeo Dr.','Des Plaines','CA' from A union all
select max(asofdate),'john','333 Apple ln.','Chicago','IL' from A union all
select max(asofdate),'dave','222 Aile St.','Prosepct','IL' from A

No, you're never too old to Yak'n'Roll if you're too young to die.