Link to home
Start Free TrialLog in
Avatar of Kongta
KongtaFlag for Switzerland

asked on

INSERT INTO Values & Select

I first INSERT the table with my Values and after updated with SELECT but as I had to do last four times, the query stopped and let me know that it is to big. Therefore, I try to slim by combining all in the first INSERT event. On top of the instruction, I run a CTE with all relevant data. But now I get an error saying there is a wrong Syntax close to ')'.

Does my idea not work at all or do I have an error in it? thx for any feedback

INSERT INTO tblStatistikTmp_sb_Land (Land,Anzeige,PosTop,PosLeft,Technik_Anzahl, UpTrend_Anzahl,Gwth_nxt_GJ, Gwth_GJ)
VALUES		('AFG','Afghanistan','2703','8870',(SELECT Tech,UpTrend,Gwth_nxt_GJ,Gwth_GJ		FROM CTE	WHERE CTE.Boerse = 'AFG')),
			('FRA','Frankreich','2114','6411',(SELECT Tech,UpTrend,Gwth_nxt_GJ,Gwth_GJ		FROM CTE	WHERE CTE.Boerse = 'FP'))

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Member_2_861731
Member_2_861731
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kongta

ASKER

get an error saying wrong Syntax near 'FP'

Does UNION not swell the resources to run the code? The full code contains more then 100 countries. As said, my first code was: INSERT 'AFG','Afghanistan'.... and then UPDATE
... =(CASE WHEN sbL.Land = 'AFG' THEN (SELECT Tech FROM CTE WHERE CTE.Boerse = 'AFG')
and this each time for Technik, UpTrend, Gwth, Gwth_GJ and therefore it screwed.
I tried running that code and I didn't get any syntax errors.
I guess there's more code in the query that's causing this to fail.

In your previous version you could've joined (inner join) with the CTE instead of doing a subquery for each field.

What do you mean by "swell the resources", performance?
Avatar of Kongta

ASKER

Yes, performance. I just read a blog where people had a better performance with UNION and others that had slower execution.

Let me check the rest of the code and come back later as I have to leave shortly
Avatar of Kongta

ASKER

okay, I missed the END on my separate test code

The problem I experience is, that not every country has some recordsets in the SELECT but I need to have them in the table as well as for this example Afghanistan. In my first approach, I filled all world countries first and then updated, therefore I hadn't this problem. Any idea how to get all countries in?

Land    Anzeige      Technik_Anzahl UpTrend_Anzahl          IQ      Gwth_nxt_GJ      Gwth_GJ
FRA      Frankreich      218                 115             NULL      0.303                          1.861
Why don't you create a table in your database that holds all those countries with any other "fixed" (non changing) value?

Then all you'd need to do is join with this table to obtain every single value.
Would this work for you?
Avatar of Kongta

ASKER

This was my first thought, I filled/INSERT the table with the fix values and after UPDATE out of my CTE. Joining doesn't work as 'Land' and 'Boerse' aren't same (FRA = FP) and I have some special issues (Sum of different 'Boerse') there like:

WHEN sbL.Land = 'ARE' THEN (SELECT SUM(Tech) FROM CTE      WHERE CTE.Boerse = 'DH' OR CTE.Boerse = 'DB')  WHEN sbL.Land = 'ARG' THEN (SELECT Tech FROM CTE WHERE CTE.Boerse = 'AR')

Can't I break the procedure so it does one by one? Looks like it goes through my code all-in-one and therefore gets an error by the mass of data?!
Why don't you insert both values in your table?
You would have two lines for ARE and so forth.
i.e.
ARE     DH
ARE     DB


In that case you'll get two values and you can sum them up in your join.
You could do it one by one, but it's going to be very very slow.
How many tables are involved in this?
Avatar of Kongta

ASKER

I see your point but I thought I can merge the Insert/update event or have one update event instaed of four very similar. I think we don't get forward here, let me google further, might find some clue and close this Q.