Append A Record into 2 Linked Tables

[litsz02]

Maybe I'm having a brain disfunction, but I'm just trying to map this out in my mind...Seems simple enough tho...

I have some records in Excel that have certain columns belonging to two different tables that I need to import into a SQL server database. For example Col1, Col2 go in Table1, and Col3 and Col4 go in table 2.

Table1 is a Order table, while Table2 is Order Line Items.

Table1 has an autonumber field for the key. They are linked by this number.

In my Excel record I have some columns that need to go in that first Order table, while some in the same record need to go in that Line item table.

Can someone show me what is the best way to append these records into these 2 tables???

 

[cmmrfrds]

You can use DTS on the sql server side. DTS handles this type of thing nicely and can be set up to run on the sql server scheduler on a periodic basis.

 

[litsz02]

Pardon my ignorance, but what's DTS and how can I use it?

 

[cmmrfrds]

If you have an sql server DBA, then check with them. Otherwise, if you have access to Enterprise Manager for sql server then check the help documentation in it. DTS standards for Data Transformation Services and is available with the sql server install. DTS is a robust tool that allows import/export between various data sources - all the Microsoft sources, such as, Excel and sql server can be used.

 

[litsz02]

Right, but my problem isnt importing the data, but its that I have a record in excel and I'm trying to insert part of that record in table1 and part of that record in table2... The issue is because there is an Autonumber key link (which is our ordernumber field between Table1 (orders) and 2 (line Items in the orders)

 

[PHV]

And isn't inserting data from excel to sql server a similar thing than importing in sql server data from excel ????

 

[cmmrfrds]

Maybe if you showed an example of the data in both tables, it would be easier to see the problem.
Is the problem that the primary key (identity column) in table 1 is a foreign key in table 2 to identify the order in table 2, and you don't want to store the order id in table 2. So, that the only time you have sufficient information to insert the record in table 2 is right after the insert into table 1. Where at that time you want to capture the identity column to insert the remaining 2 columns into table 2.

 

[cmmrfrds]

If you want to do the inserts through VBA code here is an example of how to capture the identity column. You could run this against the NorthWind database to see how it works. Of course, you would need to change the data source to your server and this example uses an NT trusted connection NOT sql server authentication. Selecting the identity must be done immediately after the insert on the same connection.

Public Function TestInsert() As String

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString

'--- connect to sql server
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sql1 = " insert into shippers values ('arnold ','999-8888');"
Set rs.ActiveConnection = cn
rs.Open sql1, cn, 3, 3
sql1 = "select scope_identity();"
rs.Open sql1, cn, 3, 3
Debug.Print "return = "; rs(0)

TestInsert = rs(0)
rs.Close
Set rs = Nothing
End Function