Application Built Backend Inserts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Application Built Backend Inserts

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-02 : 08:17:42

twilson042406 writes "Both the new application and database have been built.

Is there an easy way to take records from MSAccess to SQL Server besides DTS, bulk copy or bulk copy procedures or select into from' or building complex procedures with joins? The databases have totally different table structures.

How does 'select into from' already effect a front end, after builing a a table from multiple tables? Is there some relationship between the application, system table id's and backend tables? If I totally recreate the base tables from 'select into from statements', will this render the application useless? Please comment both on the application before and after creating the .exe.

I have to use 'select into from' to for the identity id in the sql server table(s), unless you have code that can do many records all at once from different structure databases?

I am familiar with mapping paradox.fields to db2.fields, but I think my boss is looking for a faster method."

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-05-02 : 09:48:45

SELECT INTO statements create new tables that have absolutely no relationship to any other tables. If you do this, your data will be in SQL server, but not in any tables used by your front-end. It won't make the application useless, but it won't make any data available to it either. Your application won't know any different since it only knows the tables that you told it to use in code, adding new tables doesn't affect it in any way.

Instead of using SELECT INTO, you can use INSERT INTO mytable SELECT (field list) FROM myothertable... This does not create a new table and instead inserts into the existing tables. If you're not comfortable enough writing your own SQL, you should use DTS. It's fairly intuitive with drag and drop mappings of columns.

~Travis

twilson188
Starting Member

3 Posts

Posted - 2006-05-03 : 13:33:25

Thanks twhelan1.

The identity column is the main problem, it doesn't allow multiple records to inserted all at once. Any other ideas? I appreciate
your or anyone else ideas, thanks.

The problem is that I already tried 'insert into'(below) and got this:

An explicit value for the identity column in table 'customer' can only be specified when a column list is used and IDENTITY_INSERT is ON.

begin tran
SET IDENTITY_INSERT customer ON
insert into customer
select *
from
zzzFULLcustomer --- from select into created table
SET IDENTITY_INSERT customer OFF
rollback tran

The SET IDENTITY_INSERT customer ON will work only when specifiy and exact/precise value(s) for the insert. I would like to avoid this if possible and I am not really excited about importing it as a .txt
through DTS and then into the table. I think it might give a similar
warning message regarding the 'Identity Column' although I have not tested this theory.

Here's the way Identity inserts are allowed:

SET IDENTITY_INSERT test ON
GO

-- Precise value of 3
INSERT INTO test (id, product) VALUES(3, 'test').
GO

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-05-03 : 13:56:57

Well if it's in MS Access already, you can create a connection in your DTS package to the Access file, then you just create your transform tasks from those tables to the correct ones in your database.

Is the Access database the same structure as the SQL Server database? If not, you need to specify the columns anyway to correctly match things up in your query. I suppose if they're the same structure, it wouldn't hurt to use SELECT INTO but I would NOT recommend this as you may be left with data types for columns that are different than you intended. I think your best bet is still to write INSERT INTO SELCT FROM statements. Yeah it's tedius specifying all the columns, but at least you'll be sure to get the data where you need it.

~Travis

twilson188
Starting Member

3 Posts

Posted - 2006-05-03 : 17:11:52

Thanks twhelan1.

I am always looking for better ways to do things, so I was hoping for 'a magical' method.

I believe I have found a way to do this using 'select into from' and then exporting to .xls then into the customer table with the Identity Column using DTS. Perhaps??? there's a better way?

I had previously started modifying the 'select into from' statement with the convert function to make it look like the original customer table structure. I have to modify a few other columns along the way. For example, I have used the Case statement for changing 'California' to 'CA', etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources