![]() You can't use locally declared T-SQL variables because they are not in the scope of the dynamic SQL. The problem is that you need dynamic SQL - no problem there - but you need to store the result into some type of variable. This basic algorithm has some complicated parts when translated into T-SQL - the most conceptually difficult is selecting the values from a particular column in a table. Insert into the #ColumnValues table the column nameĬreate the insert statement specific textĬreate the update statement specific text Another temporary table is created as a staging point for the data which is in each column. This temporary table will contain the actual output of the script. In order to collate the results needed, a temporary table will be created. The problem with getting the information out is that we are working in looping sets - which is difficult to do in SQL Server (well, pre. The tricky column with the Case statement checks the Status binary column and generates a Y/N depending on whether or not the column is an identity column - which we will come to later. ![]() This is the basic structure needed to get the data out. This will give you the list of columns within a source table, in the same order they are in the database. WHERE so.Name = ' SourceTableName' ORDER BY ColOrder , Case when sc.status = 0x80 then ' Y' else ' N' END as IsIdent SELECT so.name, sc.name, st.name, sc.length This is done with a pretty simple piece of SQL: ![]() The first thing you need to do is get a list of the columns within the target table and the data type of each of those columns. Like Reflection and other self-describing metadata, you can get a bit chicken-and-egg if you think about it for too long. I've always loved the system tables, which are one of those weird circumstances where the structure of the product you are using is described by metadata within the product. The first port of call for a project like this is the system tables. I think it is a very useful little script to have in the 'My SQL Helpers' folder. It also will generate either for a specific row of text, or simply for all rows in the source table. I know there are tools around to do this, but this is quick and very easy. I've always had little scripts written for specific tables, but on request from a friend, I decided to get serious and create a one-size-fits-all SQL generator which will generate a line of SQL for each row in a source table, which, when run on the target database, will perfectly replicate the data. ![]() This can be run wherever you have a query window, and you don't need to create a Stored Procedure or install any programs. What you need is a T-Script which will read the contents of a table, then create update / insert statements for that data to go into the same table in a different server and database. Maybe you just like doing things the hard way. Sometimes, a DTS / BCP import/export job is too much work when there is only one or two rows, or maybe you can't get a file anywhere near the server because of security restrictions. I know that SQL Server 2005 has great little widgets for creating insert statements based off the table, but what I have always needed was a way to generate an update or insert statement based on data in an existing table in a different database. Whether it is building (or rebuilding!) a database, migrating data for releases, or just creating test data, I at least always seem to be opening a new query page and typing in 'insert into. Shifting data around is a requirement for every developer and DBA. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |