October 28, 2008 at 9:33 am
Hi,
I have a little question. I have imported data from a csv file. Here are 4 values. These are imported to an temporary table.
CREATE TABLE custom.mi_def_tmp (
mi_seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
obj_num INT,
name_1 CHAR (16),
preset_amt_1 MONEY12
);
INPUT INTO custom.mi_def_tmp FROM c:\mi_import.csv format ASCII (obj_num, name_1, preset_amt_1);
From this table a want to insert the values into two different tables linked by the ID mi_seq.
My tables in db.
TABLE1 m.mi_def
mi_seq, obj_num, name_1
TABLE2 m.price_def
mi_seq, preset_amt_1
Insert into one table is no problem, i use this below. This is imported to m.mi_def
INSERT INTO m.mi_def (mi_def.mi_seq, mi_def.obj_num, mi_def.name_1)
SELECT mi_def_tmp.mi_seq, mi_def_tmp.obj_num, mi_def_tmp.name_1
FROM custom.mi_def_tmp
WHERE custom.mi_def_tmp.obj_num > 0
So how do get the values into m.price_def linked with the right mi_seq with price ?
Guess i have to do an JOIN satement, but not sure how.
Regards
Magnus
October 28, 2008 at 12:38 pm
m.berggren (10/28/2008)
Hi,I have a little question. I have imported data from a csv file. Here are 4 values. These are imported to an temporary table.
CREATE TABLE custom.mi_def_tmp (
mi_seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
obj_num INT,
name_1 CHAR (16),
preset_amt_1 MONEY12
);
INPUT INTO custom.mi_def_tmp FROM c:\mi_import.csv format ASCII (obj_num, name_1, preset_amt_1);
From this table a want to insert the values into two different tables linked by the ID mi_seq.
My tables in db.
TABLE1 m.mi_def
mi_seq, obj_num, name_1
TABLE2 m.price_def
mi_seq, preset_amt_1
Insert into one table is no problem, i use this below. This is imported to m.mi_def
INSERT INTO m.mi_def (mi_def.mi_seq, mi_def.obj_num, mi_def.name_1)
SELECT mi_def_tmp.mi_seq, mi_def_tmp.obj_num, mi_def_tmp.name_1
FROM custom.mi_def_tmp
WHERE custom.mi_def_tmp.obj_num > 0
So how do get the values into m.price_def linked with the right mi_seq with price ?
Guess i have to do an JOIN satement, but not sure how.
Regards
Magnus
Hi Magnus,
Perhaps I don't have a full understanding of the issue but it would appear you are looking for (2) insert statements. The one above and another along the lines of:
INSERT INTO m.price_def (mi_seq,preset_amt_1)
SELECT mi_seq,preset_amt_1
FROM custom.mi_def_tmp
WHERE obj_num > 0
Tommy
Follow @sqlscribeOctober 28, 2008 at 1:00 pm
Tommy:
Yes that´s right, (2) insert statements. But how does it work in my case ?
Well basically i want to split up my
custom.mi_def_tmp
table into my two other tables and then drop this one, this is just for getting the values in from my .csv file.
mi_seq
obj_num
name_1
preset_amt_1
The two tables below are linked together by the column mi_seq
Table1
m.mi_def
looks like this
mi_seq
obj_num
name_1
Table2
m.mi_price_def
looks like this
mi_seq
preset_amt_1
🙂
October 29, 2008 at 7:35 am
I actually got this to work. But i´m sure I can optimize or write the code in a different way. I didn´t got the (2) INSERT to work so the second statement had to be an "UPDATE". Anyone got a clue about this ?
I thought you could execute the values from one table into two at the same time ?
So here is the code.
Creating the table custom.mi_def_tmp
CREATE TABLE custom.mi_def_tmp (
mi_seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
obj_num INT,
name_1 CHAR (16),
preset_amt_1 MONEY12
);
INPUT INTO custom.mi_def_tmp FROM c:\mi_import.csv format ASCII (obj_num, name_1, preset_amt_1);
Table m.mi_def
mi_seq (PK)
obj_num
name_1
Table m.mi_price_def
mi_seq (FK)
preset_amt_1
Here i´m inserting in some values in the m.mi_def from the custom table.
INSERT INTO m.mi_def (mi_def.mi_seq, mi_def.obj_num, mi_def.name_1)
SELECT mi_def_tmp.mi_seq, mi_def_tmp.obj_num, mi_def_tmp.name_1
FROM custom.mi_def_tmp
Here i´m updating values in the m.mi_price_def table.
UPDATE m.mi_price_def
SET mi_price_def.preset_amt_1 = (SELECT mi_def_tmp.preset_amt_1
FROM custom.mi_def_tmp
WHERE mi_def_tmp.mi_seq = mi_price_def.mi_seq)
WHERE EXISTS
(SELECT mi_def_tmp.preset_amt_1
FROM custom.mi_def_tmp
WHERE mi_def_tmp.mi_seq = mi_price_def.mi_seq);
DROP TABLE custom.mi_def_tmp
October 29, 2008 at 7:51 am
m.berggren (10/29/2008)
I actually got this to work. But i´m sure I can optimize or write the code in a different way. I didn´t got the (2) INSERT to work so the second statement had to be an "UPDATE". Anyone got a clue about this ?I thought you could execute the values from one table into two at the same time ?
So here is the code.
Creating the table custom.mi_def_tmp
CREATE TABLE custom.mi_def_tmp (
mi_seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
obj_num INT,
name_1 CHAR (16),
preset_amt_1 MONEY12
);
INPUT INTO custom.mi_def_tmp FROM c:\mi_import.csv format ASCII (obj_num, name_1, preset_amt_1);
Table m.mi_def
mi_seq (PK)
obj_num
name_1
Table m.mi_price_def
mi_seq (FK)
preset_amt_1
Here i´m inserting in some values in the m.mi_def from the custom table.
INSERT INTO m.mi_def (mi_def.mi_seq, mi_def.obj_num, mi_def.name_1)
SELECT mi_def_tmp.mi_seq, mi_def_tmp.obj_num, mi_def_tmp.name_1
FROM custom.mi_def_tmp
Here i´m updating values in the m.mi_price_def table.
UPDATE m.mi_price_def
SET mi_price_def.preset_amt_1 = (SELECT mi_def_tmp.preset_amt_1
FROM custom.mi_def_tmp
WHERE mi_def_tmp.mi_seq = mi_price_def.mi_seq)
WHERE EXISTS
(SELECT mi_def_tmp.preset_amt_1
FROM custom.mi_def_tmp
WHERE mi_def_tmp.mi_seq = mi_price_def.mi_seq);
DROP TABLE custom.mi_def_tmp
-or-
update m.mi_price_def
set preset_amt_1 = t.preset_amt_1
from custom.mi_def_tmp c
join mi_def_tmp t
on c.mi_seq = t.mi_seq
go
🙂
Tommy
Follow @sqlscribeOctober 29, 2008 at 8:47 am
What edition of SQL Server 2005 has an "INPUT INTO" statement?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 29, 2008 at 11:36 am
rbarryyoung:
I not using sql server 2005 for this, i´m using interactive sql in sybase actually.
/Magnus
October 29, 2008 at 11:57 am
In SQL Server - you'd simply add a JOIN to the FROM statement in your INSERT INTO.
Something like
Insert into #tmpTable(fieldlist)
SELECT fieldlist
from
tableA
Inner join Tableb on tableA.ID=tableB.id
WHERE
myCriteria=1
I haven't touched Sybase in a LONG time, but I think you still have the same syntax abilities at least for this kind of statement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply