Can I do this? (Join a SELECT INTO?) 1

[Gooser]

SELECT a.PersonID
     , a.FirstName
     , a.LastName
     , b.Permission
     , b.PermissionGrantor
[COLOR=red]
INTO    dbo.Person     AS a [COLOR=green]--This is the[/color]
   JOIN dbo.Permission AS b [COLOR=green]--slightly strange part[/color]

   ON a.PersonID = b.PersonID
[/color]
FROM dbo.PersonPermission

v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

The Bailout We Need

 

[gmmastros]

No.

Please explain what you are trying to accomplish and we will be able to help you better.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Gooser]

I've got a big fat table (56 columns) that I am populating with incoming flat files. The flat files contain Plus-delimited (+) records of a fixed length (56 fields per record).

Some of the fields in the records are applicable to my application which is normalized to 3d Normal Form, but several are not. The requirement is to store all of the incoming fields, but to only use the fields that we care about in the application.

I was hoping to write a statement where I could easily insert into the several tables in one step, keeping all the FK relationships intact. Is there a good strategy for doing this? The column and table names in my example were just made up to show what I'm trying to do. The actual data is 'boring insurance stuff'.

Thanks,
v/r
Gooser

 

[gmmastros]

I would suggest a big ole stored procedure for this.

You can only insert rows in to one table at a time. I suggest you insert in to the parent table first, then the child tables. This way, referential integrity won't break.

To actually accomplish the inserts...

First, write a Select query to get the data from your import process. Run, and then re-run the query to make sure you are getting the correct data. Once you have the query, then put an INSERT line before it. Like this...

Insert Into MyRealTable(Col1, Col2, Col3)
Select Col1, Col2, Col3
From   MyImportTable
Where  SomeCondition Is True

Once you have the parent table(s) done, start inserting rows in to the child table(s). Continue doing this until all your data is imported.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Gooser]

Yes George, it does, but how do I keep the Foreign Key in the children aligned with the Primary Key in the parents?

Should I do something like this:

WHILE (count of rows not ingested > 1)
   INSERT TOP 1 ROW INTO PARENT
      INSERT TOP 1 ROW INTO CHILD
         INSERT TOP 1 ROW INTO GRANDKID
            INSERT ...
   SET TOP 1 ROW ingested flag to "Yes"
END WHILE

?

Thanks,
Gooser

 

[gmmastros]

I see. The problem is that the primary key in the parent table does not exist until you insert the row, right? Well, what you can do is to insert in to the parent table, and then JOIN the imported table with the parent table to get the primary key. This assumes that there is something (a column or combination of columns) in the import table that you can join on.

Ex:

Insert Into ParentTable(Col1, Col2, Col3)
Select [!]Col1, Col2, Col3[/!]
From   ImportTable

Insert Into ChildTable(ForiegnKeyValue, ColX, ColY)
Select ParentTable.PrimaryKey,
       ImportTable.ColX,
       ImportTable.ColY
From   ImportTable
       Inner Join ParentTable
         On ImportTable.[!]Col1[/!] = ParentTable.[!]Col1[/!]
         And ImportTable.[!]Col2[/!] = ParentTable.[!]Col2[/!]
         And ImportTable.[!]Col3[/!] = ParentTable.[!]Col3[/!]

Importing to the parent table should be pretty simple. For the child tables, you join on enough columns to make the proper joins to the parent table so you can get the primary key.

When you are building this, make sure you comment out the INSERT LINE until you are sure you have the select query correct. Once it's correct, the uncomment the insert and run it again.

[green]-- Insert Into ChildTable(ForiegnKeyValue, ColX, ColY)[/green]
Select ParentTable.PrimaryKey,
       ImportTable.ColX,
       ImportTable.ColY
From   ImportTable
       Inner Join ParentTable
         On ImportTable.Col1 = ParentTable.Col1
         And ImportTable.Col2 = ParentTable.Col2
         And ImportTable.Col3 = ParentTable.Col3

This way, you don't accidently import the wrong data.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MDXer]

If you are using MS SQL 2005 or 2008 you can create a SSIS package to do this.