December 31, 2003 at 12:31 pm
Hi all,
I am just starting to do some things in SQL Server and one of the things I am tasked with now is getting data out of an external system on an ongoing basis into SQL Server. What I need to do is only select a certain date range of records and then I want to append these recorts to an existing table using T-SQL. Can someone offer me the best practices to do this. As far as I know I need to use the openquery statement since I am working with a linked server. Currently I am trying to select the records into a table variable and then loop through that table variable to perform the inserts. On a side note I may need to do additional logic checking on which records to import within the loop. Any ideas would be helpful, thanks a lot in advance. Ohh and also are there any other SQL websites that you have found of particular help too, if you could list them here as well. Thanks a lot.
This is the code I am currently working with which does not work because of an error with the into clause:
declare @TableVar table ( [MANDT] char (3) NOT NULL,
[LGNUM] char (3) NOT NULL,
[TANUM] char (10) NOT NULL,
[BWART] char (3) NOT NULL,
[BWLVS] char (3) NOT NULL,
[TBPRI] char (1) NOT NULL,
[TRART] char (1) NOT NULL,
[BDATU] char (8) NOT NULL,
[BZEIT] char (6) NOT NULL,
[BNAME] char (12) NOT NULL,
[REFNR] char (10) NOT NULL,
[TBNUM] char (10) NOT NULL,
[UBNUM] char (10) NOT NULL,
[VBELN] char (10) NOT NULL,
[KQUIT] char (1) NOT NULL,
[QDATU] char (8) NOT NULL,
[MBLNR] char (10) NOT NULL,
[MJAHR] char (4) NOT NULL,
[BETYP] char (1) NOT NULL,
[BENUM] char (10) NOT NULL,
[DRUKZ] char (2) NOT NULL,
[DRUCK] char (1) NOT NULL,
[TEILK] char (1) NOT NULL,
[KR2SO] char (1) NOT NULL,
[KR2KU] char (1) NOT NULL,
[KDISO] char (1) NOT NULL,
[KZPLA] char (1) NOT NULL,
[PLDAT] char (8) NOT NULL,
[RSNUM] char (10) NOT NULL,
[LZNUM] char (20) NOT NULL,
[BDART] char (2) NOT NULL,
[PKNUM] char (7) NOT NULL,
[PKPOS] char (3) NOT NULL,
[KZLEI] char (1) NOT NULL,
[KISTZ] char (1) NOT NULL,
[KISTP] char (1) NOT NULL,
[PERNR] char (8) NOT NULL,
[SOLWM] decimal (13,3) NOT NULL,
[SOLEX] decimal (13,3) NOT NULL,
[ISTWM] decimal (13,3) NOT NULL,
[ZEIEI] char (3) NOT NULL,
[HRSTS] char (1) NOT NULL,
[STDAT] char (8) NOT NULL,
[ENDAT] char (8) NOT NULL,
[STUZT] char (6) NOT NULL,
[ENUZT] char (6) NOT NULL,
[L2SKA] char (1) NOT NULL,
[MINWM] char (1) NOT NULL,
[LGTOR] char (3) NOT NULL,
[LGBZO] char (10) NOT NULL,
[KZVEP] char (1) NOT NULL,
[SWABW] char (4) NOT NULL,
[AUSFB] char (4) NOT NULL,
[SPEZI] char (1) NOT NULL,
[VBTYP] char (1) NOT NULL,
[QUEUE] char (10) NOT NULL,
[KGVNQ] char (1) NOT NULL,
[TAPRI] char (2) NOT NULL,
[KVQUI] char (1) NOT NULL,
[HUCON] char (1) NOT NULL,
[NOITM] char (4) NOT NULL )
Select * from openquery(LCP_SAP, 'Select * from R3LCPDATA.LTAP where lgnum = ''AF'' and tanum = ''0000000006'' ')
into @TableVar
December 31, 2003 at 1:33 pm
You may want to use a temp table rather than a table variable. If the select statement works without the insert then you have to make sure all your data types are matching up on both tables and convert where necessary.
A Select Into like you have it, will attempt to creat a new table. You could leave it like you have it and instead of using the @TableVar use #TempTable. This should create the temp table with the proper data types.
I would also try to put all the logic that you can into the Where clause of your select statement, because looping with a cursor in T-SQL isn't that efficient.
I always like to list the column names in my select statement rather than using "Select *". It is a little more work but he reason is, if a column is added to the table the select * will pick that column up and that may, in some cases, break your program or SQL.
December 31, 2003 at 3:33 pm
GMan, I have two thoughts to share with you:
1. The order of clauses in your SELECT ... INTO statement is incorrect. The correct order is SELECT columns INTO new_table FROM rowsource.
2. While table variables have some efficiency benefits over temp tables, they also have some limitations that I hope are due to the fact that table variables are a relatively new feature. In particular, I don't think you can use a table variable in an INTO clause of a SELECT statement.
Hope this helps,
Chris
December 31, 2003 at 4:22 pm
quote:
In particular, I don't think you can use a table variable in an INTO clause of a SELECT statement.
True, you can't select into, but you can insert into select.
I find that the choice of using a temp table or table variable really boils down to the number of rows you want to store. While there are no hard set rules, if I have a whole lot of rows, I use a temp table. For viewer rows, I use a table variable, which I think of as a multi-dimentional array.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply