Creating many new records -- sql server new users

Roy Harvey
3/19/2007 1:57:05 PM

From the given information I do not see why it would not be as simple
as:

INSERT TableC
SELECT B.MainNo, B.SubNo,
A.DataF1, A.DataF2
FROM TableA as A
JOIN TableB as B
ON A.MainNo = B.MainNo

Roy Harvey
Beacon Falls, CT

On Mon, 19 Mar 2007 18:36:54 +0100, Anders Eriksson
[quoted text, click to view]

Anders Eriksson
3/19/2007 6:36:54 PM

Hello,

I have a database with two tables
TableA
* MainNo NVARCHAR
DataF1 NVARCHAR
DataF2
...
TableB
* MainNo NVARCHAR
* SubNo NVARCHAR
...

* indicates Primary Key

Now I need to create one record for each MainNo+SubNo in a third table,
which we can call TableC. For each record in TableC I need to copy all the
DataF from TableA

I have done this using INSERT for each MainNo+SubNo, but it will take too
long time.

Anyone that have an better, faster way of doing this?

// Anders
--
English is not my first, or second, language
so anything strange, or insulting, is due to
the translation.

Anders Eriksson
3/20/2007 12:00:00 AM

[quoted text, click to view]

Ok, so I did simplify it to much. I will try again, now using the actual
data.

I have 4 tables

MarkQueue
orderno
serialno
fieldid
fieldvalue

Order_Serialnr
orderno
serialno

NamePlateData
ordernr
paramid
paramvalue

NamePlateText
langid
fieldid
fieldvalue

For each record in Order_Serialnr, for a specific orderno, I need to insert
all records from NamePlateData (with the same orderno) plus all the records
in NamePlateText that has the same langid as one record in NamePlateData
that has paramid='LANGID'. The langid is in the ParamValue column.

The columns are of the same type but have different names in diffent tables
:-( (this is not possible to change)

So
ordernr == orderno
paramid == fieldid
paramvalue == fieldvalue

What would an INSERT statement that perform this look like?

I hope that my description is understandable, otherwise please ask!

// Anders
--
English is not my first, or second, language
so anything strange, or insulting, is due to
the translation.

Roy Harvey
3/20/2007 12:00:00 AM

If I understand the requirement I think this might do what you ask. If
it does not I am still confused! 8-)

One point, it is always important to include the definitions of the
tabke keys.

Since the MarkQueue table was not being referenced in the descriptions
I made the assumption that it was the target into which the INSERTed
data would go.

INSERT MarkQueue
SELECT A.orderno,
A.serialno,
C.fieldid,
C.fieldvalue
FROM Order_Serialnr as A
JOIN NamePlateData as B
ON A.orderno = B.ordernr
JOIN NamePlateText as C
ON B.paramid = C.fieldid
AND B.paramvalue = C.langid
WHERE A.orderno = 'Specific'
AND B.paramid = 'LANGID'

Let me know if this is what you need, and if not how far off it is and
in what ways.

Roy Harvey
Beacon Falls, CT

On Tue, 20 Mar 2007 14:00:04 +0100, Anders Eriksson
[quoted text, click to view]

Anders Eriksson
3/24/2007 12:00:00 AM

Hello Roy,

[quoted text, click to view]
Which means that I have failed to explain :-)

Your SQL statement didn't do what I needed so in the end I had to split it
into two statements.

To get the data from ITM_NamePlateData:

INSERT ITM_MarkValues
SELECT A.OrderNo,A.SerialNo,B.ParamId as FieldId,B.ParamValue as FieldValue
FROM ITM_Order_serialNr A INNER JOIN
ITM_NamePlateData B on A.OrderNo = B.OrderNr
WHERE OrderNo = 'the selected orderno'

and to get the data from ITM_NamePlateText:

INSERT ITM_MarkValues
SELECT A.OrderNo, A.SerialNo, C.FieldId, C.FieldText AS FieldValue
FROM ITM_Order_SerialNr A INNER JOIN
ITM_NamePlateData B ON A.OrderNo = B.OrderNr INNER JOIN
ITM_NamePlateText C ON B.ParamValue = C.LangId
WHERE(A.OrderNo = 'the selected orderno') AND (B.ParamId = 'LANGID')

I would never have succeeded with this without your help! So Thank You very
much!!

If there is a way of combining these two SQL statements it would be nice to
know.

// Anders
--
English is not my first, or second, language
so anything strange, or insulting, is due to
the translation.

Roy Harvey
3/26/2007 12:00:00 AM

On Sat, 24 Mar 2007 09:56:43 +0100, Anders Eriksson
[quoted text, click to view]

UNION ALL can be used to combine them, though it doesn't simplify it.

INSERT ITM_MarkValues
SELECT A.OrderNo,A.SerialNo,B.ParamId as FieldId,B.ParamValue as
FieldValue
FROM ITM_Order_serialNr A INNER JOIN
ITM_NamePlateData B on A.OrderNo = B.OrderNr
WHERE OrderNo = 'the selected orderno'
UNION ALL
SELECT A.OrderNo, A.SerialNo, C.FieldId, C.FieldText AS FieldValue
FROM ITM_Order_SerialNr A INNER JOIN
ITM_NamePlateData B ON A.OrderNo = B.OrderNr INNER JOIN
ITM_NamePlateText C ON B.ParamValue = C.LangId
WHERE(A.OrderNo = 'the selected orderno') AND (B.ParamId = 'LANGID')

Roy Harvey

Anders Eriksson
3/27/2007 12:00:00 AM

[quoted text, click to view]

Well, I think I continue to use two sql statements. That way I may
understand my program the next time I have to make some changes ;-)

Thank you very much for all you help!

// Anders
--
English is not my first, or second, language
so anything strange, or insulting, is due to
the translation.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

sql server new users : Creating many new records