I receive monthly data updates for a SQL databse shipped to me in an ACCESS database, there are about 30 tables that follow this pattern the largest table contains approximately 40,000 rows.
我收到了在ACCESS数据库中发送给我的SQL数据库的每月数据更新,大约有30个表遵循这种模式,最大的表包含大约40,000行。
CREATE TABLE [dbo].[TABLE_NAME_HERE] (
[title] nvarchar (255) NULL,
[classification1] nvarchar (255) NULL,
[classification2] nvarchar (255) NULL,
[1986] float NULL,
[1987] float NULL,
[1988] float NULL,
[1989] float NULL,
[1990] float NULL,
[1991] float NULL,
[1992] float NULL,
[1993] float NULL,
[1994] float NULL,
[1995] float NULL,
[1996] float NULL,
[1997] float NULL,
[1998] float NULL,
[1999] float NULL,
[2000] float NULL,
[2001] float NULL,
[2002] float NULL,
[2003] float NULL,
[2004] float NULL,
[2005] float NULL,
[2006] float NULL,
[2007] float NULL,
[2008] float NULL,
[2009] float NULL,
[2010] float NULL,
[2011] float NULL,
[2012] float NULL,
[2013] float NULL,
[2014] float NULL,
[2015] float NULL,
[2016] float NULL,
[2017] float NULL,
[2018] float NULL,
[2019] float NULL,
[2020] float NULL,
[2021] float NULL,
[2022] float NULL,
[2023] float NULL,
[2024] float NULL,
[2025] float NULL)
We currently have a DTSX package that uploades these table however every so often (yearly) they add another column, and the DTSX package has the column details harded coded.
我们目前有一个DTSX软件包可以上传这些表,但每隔一段时间(每年)它们会添加另一个列,而DTSX软件包的列详细信息经过编码。
We do not have the time or budget to reprogram the applications envolved - though I agree there are many better ways of storing this data.
我们没有时间或预算来重新编程所涉及的应用程序 - 尽管我同意有许多更好的方法来存储这些数据。
I would like to have the DTSX package automaticaly DROP the table on the SQL server, CREATE a TABLE that matches the one the ACCESS database and upload the data.
我想让DTSX包自动DROP SQL服务器上的表,创建一个与ACCESS数据库匹配的表并上传数据。
Is this possible?
这可能吗?
2 个解决方案
#1
Is it an option to change the table structure? For example if you had a child table with a column called Year and another called Value then there would be no need to keep adding new columns year on year. It is good practice to store data in tables rows rather than in the column names.
是否可以选择更改表格结构?例如,如果您有一个名为Year的列的子表和另一个名为Value的子表,那么就不需要继续逐年添加新列。最好将数据存储在表行而不是列名中。
It is also possible for example to use ADOX in MS-Acceess VBA to loop through the Fields Collection of the Access table and from this create a new table in SQL Server. Code would be something like in this link but would need changing to work with a SQL Server connection etc.
例如,也可以在MS-Acceess VBA中使用ADOX循环访问Access表的Fields Collection,并从中创建SQL Server中的新表。代码将类似于此链接,但需要更改以使用SQL Server连接等。
#2
One of the options in DTS is to create a new table at import. But as the previous post suggests you should review the problem cause normally there is no need (and it is heavily advised against) to change a table. When there is a new column than you have to change the code of your program anyway unless the new column is completely useless. In this case just drop the column before importing and you are fine again. If the column is useful then how will you use it without writing code that refers to it (after the create/import of the table)???
DTS中的一个选项是在导入时创建一个新表。但正如之前的帖子建议你应该检查问题原因通常没有必要(并且强烈反对)更改表格。当有一个新列时,除非新列完全无用,否则无论如何都必须更改程序代码。在这种情况下,只需在导入之前删除列,您就可以了。如果该列有用,那么如何在不编写引用它的代码的情况下使用它(在创建/导入表之后)???
#1
Is it an option to change the table structure? For example if you had a child table with a column called Year and another called Value then there would be no need to keep adding new columns year on year. It is good practice to store data in tables rows rather than in the column names.
是否可以选择更改表格结构?例如,如果您有一个名为Year的列的子表和另一个名为Value的子表,那么就不需要继续逐年添加新列。最好将数据存储在表行而不是列名中。
It is also possible for example to use ADOX in MS-Acceess VBA to loop through the Fields Collection of the Access table and from this create a new table in SQL Server. Code would be something like in this link but would need changing to work with a SQL Server connection etc.
例如,也可以在MS-Acceess VBA中使用ADOX循环访问Access表的Fields Collection,并从中创建SQL Server中的新表。代码将类似于此链接,但需要更改以使用SQL Server连接等。
#2
One of the options in DTS is to create a new table at import. But as the previous post suggests you should review the problem cause normally there is no need (and it is heavily advised against) to change a table. When there is a new column than you have to change the code of your program anyway unless the new column is completely useless. In this case just drop the column before importing and you are fine again. If the column is useful then how will you use it without writing code that refers to it (after the create/import of the table)???
DTS中的一个选项是在导入时创建一个新表。但正如之前的帖子建议你应该检查问题原因通常没有必要(并且强烈反对)更改表格。当有一个新列时,除非新列完全无用,否则无论如何都必须更改程序代码。在这种情况下,只需在导入之前删除列,您就可以了。如果该列有用,那么如何在不编写引用它的代码的情况下使用它(在创建/导入表之后)???