October 22, 2008 at 2:05 pm
Hello,
Newbie alert 🙂
I am looking for a t-sql code example that will run a dynamic sql statement and insert the columns/data into another table (well, I know the column names and the sql is a straightforward select statement). The source and destination tables both have the same structure, except that the destination table has one more column, called purchase_tot. I will calculate the value of this column myself in the code.
I will appreciate any help you can offer.
Thanks
October 22, 2008 at 2:10 pm
You shouldn't need dynamic SQL to accomplish this goal.
Take a look at the following:
INSERT INTO NewTable(A,B,C,D,E,F,G,Total)
SELECT A,B,C,D,E,F,G,SUM(A+C)
FROM OldTable
WHERE B = Something -- Optional Where clause
GROUP BY A,B,C,D,E,F,G
October 22, 2008 at 2:25 pm
Thanks for the tip. Unfortunately, I need the T-sql code to do this one row at a time. There is some manual logic between the select's and insert's that is just not easy to code in plain SQL. (Sometimes it is null, sometimes, it is zero, sometimes it is a calculation.)
October 22, 2008 at 2:47 pm
I'm not following what your question is then. Are you just looking for an example of how to do an insert with Dynamic SQL? Any chance you could post some more specific information? (Table DDL and sample data, along with the different scenarios and how you'd like to handle them) See the link in my signature for an example of how to post this.
There is some manual logic between the select's and insert's that is just not easy to code in plain SQL.
The challenge of doing things in set based logic as opposed to RBAR, and the opportunity to learn from doing so is a big part of why a lot of the guys reading this come here(Myself included).
October 22, 2008 at 2:57 pm
I was looking for info to flesh out the following skeleton, so to speak. I am looking for info on the lines where I have "??" marks. I have not done t-sql before so I am not sure whether the variables will be column names, or if I need to declare them, etc.
-- open cursor for the select statement
-- enter the loop
-- fetch a record
-- ?? how do I access the columns??
-- do some calculation (I am OK here)
-- insert record into destination table
-- ?? how do I specify which data to insert??
-- exit the loop
The table structure is quite a simple table that has customer data with first_name, last_name, salutation, address_1, address_2, city, state, zip, purc_date, purc_amount, activ_code.
I hope this helps.
October 22, 2008 at 3:00 pm
I'd also recommend reading the following article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
It provides excellent guidelines on how to post a question to get the best response possible.
😎
October 22, 2008 at 3:04 pm
From what you've posted so far, I highly doubt you'll need a cursor for this. Read the post we're both talking about and try to give us as much of that information as you can. Don't be afraid to post the code you're working on even if it doesn't work, as it can sometimes explain to us better than words what you're doing wrong or what you're trying to do.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply