Copying data between NAV tables using SQL

radek.bb · 2013-09-02

Hi guys,

I want to copy data between two copies of the same object (create replica of the table) using SQL statement.
Something like:

insert into [Test Table 02] select * from [Test Table 01]

It gives an error:

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

As suggested by the error message I can change this code into

insert into [Test Table 02] (field1, field2,...,field100) select field1, field2,...,field100 from [Test Table 01]

or

insert into [Test Table 02] select DEFAULT,field1, field2,...,field100 from [Test Table 01]

But I don't like those... I need a solution without listing the columns.
According to help, for example, when you use TRANSFERFIELDS(..., FALSE) in NAV - timestamp value will not change. How they achieve this?

Is there any setting which forces SQL to accept value in timestamp field?

Thanks

ara3n · 2013-09-02

you need to specify all the fields on sql. Why are doing this through sql?

radek.bb · 2013-09-02

I expect it to be faster than using RecRef in C/AL.

Just preparing SQL_statement in NAV code and then execute it as
ADO.Execute(SQL_Statement)

But the trick is ADO.execute is failing when statement length is more than 2,000 characters - and there are tables where single column listing (you need to list them twice per statement) is longer than 5,000 characters.... so it happens sometimes that whole statement is about 12,000 characters...

That's why I need solution without column names - a generic one.

kriki · 2013-09-02

It is faster, but you can't use SELECT *.

So the best (but slower) way is using recordref.

radek.bb · 2013-09-02

This is why I am asking here - in search for a clever trick

I got for example:

SELECT * INTO #temp_1 FROM [Test Table 01] 
ALTER TABLE #temp_1 DROP COLUMN [timestamp]
INSERT INTO [Test Table 02] (column1,column2,...,column100) SELECT * FROM #temp_1

As you can see by using temporary table I managed to reduce statement to require only one column listing (as for second I can now use SELECT *).
But first column listing (column1,column2,..,column100) is still required - I need something clever to remove it as well...
:-k

lynge · 2013-09-02

Can you drop the destination table before each copy?

I belive a "SELECT * INTO x2 FROM x1..." will keep timestamps in TSQL if the x2 table does not exist. It will be auto created with same table structure as x1.

Another possible way (not testet) is to change the datatype of the timestamp row in x2 to binary(8) which should be what a timestamp is stored in anyway.
But I have no clue how NAV will react to such a table and you will probably get into problems if you modify rows later (the binary(8) will not be updated).

Regards
Gert Lynge

ndbcs · 2013-09-02

Create a stored proc and parse source and destination table as a parameter.
Inside of the SP you can build the sql statement in a varchar(max) variable and run it with exec or sp_executesql.
So the length of your statement doesn't matter.
You can call the SP from NAV via ADO.

I've also seen solutions inside NAV using 'Microsoft Script Control 1.0'.ScriptControl-Automation.
You can build a vbscript using ADO in an instance of the ScriptControl variable.
With the function ScriptControl.AddCode you can add as many code as you need.

I prefer the stored proc.

regards
ndbcs

bbrown · 2013-09-02

Are you sure you really want to do this? You might want to consider Kriki's advice.

Remember that database updates made by external calls are not under NAV's transaction control. That means if a NAV error occurs after you have run your external code, the changes made by NAV code are rolled back, but not those by the external code. This can leave your DB in an unexpected condition. That's not to say direct SQL can't be useful.

radek.bb · 2013-09-03

lynge wrote:

Can you drop the destination table before each copy?
I belive a "SELECT * INTO x2 FROM x1..." will keep timestamps in TSQL if the x2 table does not exist. It will be auto created with same table structure as x1.

This is worth exploring...
Table created this way is losing some part of its structure and NAV is no longer liking it - but maybe we can do something about this...

lynge wrote:

Another possible way (not testet) is to change the datatype of the timestamp row in x2 to binary(8) which should be what a timestamp is stored in anyway.
But I have no clue how NAV will react to such a table and you will probably get into problems if you modify rows later (the binary(8) will not be updated).

SQL does not like this idea:
Cannot alter column 'timestamp' because it is 'timestamp'.

ndbcs wrote:

Create a stored proc and parse source and destination table as a parameter.
Inside of the SP you can build the sql statement in a varchar(max) variable and run it with exec or sp_executesql.
So the length of your statement doesn't matter.
You can call the SP from NAV via ADO.

Yes... probably it is the safest option. I would prefer to use NAV code but this could be acceptable compromise...

ndbcs wrote:

I've also seen solutions inside NAV using 'Microsoft Script Control 1.0'.ScriptControl-Automation.
You can build a vbscript using ADO in an instance of the ScriptControl variable.
With the function ScriptControl.AddCode you can add as many code as you need.

Maybe I will play with this idea sometime... but I also prefer stored proc

bbrown wrote:

Are you sure you really want to do this? You might want to consider Kriki's advice....

Yes

It is a migration exercise - not a solution for daily operations/transactions. I am not worried about transaction control here.

Thanks everyone!

ara3n · 2013-09-03

have you tried bulk insert?

http://blog.sqlauthority.com/2008/02/06 ... ql-server/

you don't need to specify the fields

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

rmv_RU · 2013-11-29

I have written a sql procedure and use it as a transferfields.

ALTER proc [dbo].[system_TransferTable] 
@from_table varchar(100)
, @to_table varchar(100)

as begin
--перенос данных из одной таблицы в другую, при этом поля маппятся по названию поля.
--работает только вставка и только из временных таблиц источников (в целях защиты от некорректного вызова) 
	if left (@from_table, 1)<>'#' begin
		raiserror('system_TransferTable works only for temporary tables', 16,1)
	end
	declare @sql  varchar(4000)
	declare @fields_list varchar(4000)
	set @fields_list=''
	select @fields_list =@fields_list + '[' + t1.[name] + '],' from tempdb.dbo.syscolumns t1 
	inner join syscolumns t2 on t1.name=t2.name
	where t1.id = object_id('tempdb.dbo.' + @from_table) and t1.xtype <> 189 
		and t2.id = object_id(@to_table) and t2.xtype <> 189 
		
	set @fields_list=left(@fields_list, len(@fields_list) - 1)
	set @sql='insert into ' + @to_table + '(' + @fields_list + ') 
	select ' + @fields_list + ' from ' + @from_table 
	
	print @sql
	exec (@sql)

end

bbrown wrote:

Are you sure you really want to do this? You might want to consider Kriki's advice.
Remember that database updates made by external calls are not under NAV's transaction control. That means if a NAV error occurs after you have run your external code, the changes made by NAV code are rolled back, but not those by the external code. This can leave your DB in an unexpected condition. That's not to say direct SQL can't be useful.

It's true and i hope that MS will create interface to current sql connection in future versions. Now I have to use this trick to make changes by SQL in same transaction as a Nav.

Copying data between NAV tables using SQL

Answers

Categories