Complex Insert with conditionals

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

SQL Server Development (2000)

Complex Insert with conditionals

Author

Topic

rashley
Starting Member

3 Posts

Posted - 2005-10-10 : 17:02:28

Greetings. I'm fairly new to SQL and am having some trouble.

I'm trying to construct an insert statement that puts multiple rows into a table with 3 columns.

the first column is the result of a subquery from another table. I've already got the subquery working.

The second rows is basically a number to refer to "last month".

So, if month = 1, then we insert the value 12, otherwise we use month-1.

There are basically 3 elements here I don't understand.

1. How do I do a single insert statement that combines data from another table with data I'm deriving?

2. How do I pull the "current" month from sysdate? (I'll also need the year)

3. How do I build the conditional based on those values?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-10 : 17:45:49

Please post some table create scripts and some sample data. See this posting for some guidelines:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Nathan Skerl

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 05:11:57


INSERT INTO MyDestinationTable (Col1, Col2, ...)
SELECT Col1,
       (SELECT MAX(ColX) FROM MyTable2 AS T2 WHERE T2.ID = T1.ID) AS Col2,
       ....
FROM MyTable1 AS T1

2. "sysdate" ... Hmmm ... you using SQL Server, or some other flavour?? In SQL Server the current Date&Time is given by GetDate()


SELECT DATEPART(Month, GetDate()) AS MyMonth,
       DATEPART(Year, GetDate()) AS MyYear,
       CASE WHEN DATEPART(Month, GetDate()) = 1 
            THEN 12
            ELSE DATEPART(Month, GetDate()) - 1
            END AS MyLastMonth

3. See (2)

Kristen

HCLollo
Starting Member

49 Posts

Posted - 2005-10-11 : 05:28:35

Rashley,
probably I just didn't get clearly your issue, so in that case please
forgive me, but...why do you want to put different data in different
rows in different columns all in the same table? Wouldn't be better
to insert just one row with all the data you have?

HCL

"If it works fine, then it doesn't have enough features"

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 05:31:42

I read it as

"The second ~~rows~~ column is basically ..."

but I could be wrong!

Kristen

HCLollo
Starting Member

49 Posts

Posted - 2005-10-11 : 05:53:46

quote:
Originally posted by Kristen

I read it as

"The second ~~rows~~ column is basically ..."

but I could be wrong!

Kristen

No, probably you're right Kristen, and I was thrown off by

quote:

I'm trying to construct an insert statement that puts multiple rows into a table with 3 columns.

HCL

"If it works fine, then it doesn't have enough features"

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 06:43:06

Lets hope its not three rows and multiple columns

Kristen

rashley
Starting Member

3 Posts

Posted - 2005-10-11 : 08:12:13

First of all, yes, I mistyped, I meant the second COLUMN was the month.

This statement will be put into PL-SQL and run on Oracle, not SQL Server. I probably should have said that.

Kristen, I think you've given me most of what I need. I've never used the AS operator before, though. Could you explain that to me, please?

So, the general format for pulling info from different sources to different columns is:

INSERT INTO MyDestinationTable (Col1, Col2)
SELECT Col1,
(<subselect for Col1>)
SELECT Col2,
(<subselect for Col2>)

Is that correct?

Thanks for all the help.

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 10:08:22

"I've never used the AS operator before"

Its just allocating an ALIAS for that table. The "AS" keyword is optional, and I can't remember what the correct syntax is in Oracle, but I'm pretty sure that

... FROM MyTable2 T2 ...

works just fine, so I don't know if "AS" is permitted - either way its just "noise" !

Syntax for subselects is


INSERT INTO MyDestinationTable (Col1, Col2)
SELECT (<subselect for Col1>),
       (<subselect for Col2>)

but you are doing to need to select SOMETHING from a table to pass to the subselects to bind them together, so more likely:


INSERT INTO MyDestinationTable (Col1, Col2)
SELECT (SELECT MIN(SomeCol) FROM MyTable2 T2 WHERE T2.MyPK = T1.MyPK),
       (SELECT MAX(SomeCol) FROM MyTable3 T3 WHERE T3.MyPK = T1.MyPK),
       T1.SomeCol3
FROM   MyTable1 T1

Kristen

rashley
Starting Member

3 Posts

Posted - 2005-10-11 : 10:17:25

I finally got it to work. Thanks for all your help, Kristen.

In case you're curious, I ended up with:

INSERT into TARGET_TABLE (BATCHNUMBER, ACCOUNTINGMONTH, ACCOUNTINGYEAR)
(
SELECT BATCHNUMBER,
decode (to_number(to_char(sysdate,'mm')),1,12,to_number(to_char(sysdate,'mm')) -1) acct_month ,
decode(to_number(to_char(sysdate,'mm')),1,to_number(to_char(sysdate,'yyyy')) -1,to_number(to_char(sysdate,'yyyy'))) acct_year
From HUGE_TABLE
where HUGE_TABLE.BATCHNUMBER > ( Select max(BATCHNUMBER) from SUBSET_TABLE_BATCHNUMBERS )
group by BATCHNUMBER
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources