October 3, 2012 at 12:03 pm
The best way to explain what I'm trying to do is provide an example.
Let's say I have a table named "temp_patients" in three distinct databases (Database1, Database2, Database3). I've got a 4th database that'll be the aggregate values of tables from the other three databases. All of the databases in question have in place relational database schemas to keep them coordinated in build and in maintenance.
What I need to do is move everything from Database1.dbo.temp_patients, Database2.dbo.temp_patients, and Database3.dbo.temp_patients into Database4.dbo.temp_patients
I have two conditions though:
1. The schema changes from time to time so there will be new columns from time to time so I have to be able to select all columns at any given point.
2. There will always be one column (a key) that I do not wish to draw down from any of the reference databases as well. So to point #1, I need everything in those tables except for the key column and I want to make it dynamic so it always grabs all columns but that one key column.
Can anyone help me with the syntax? Thanks!
October 3, 2012 at 12:13 pm
this is code to get columns name from a table,
SELECT COLUMN_NAME FROM SourceDbName.dbo.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='';
if you want to do all in sql, them you may have to build sql string then use exec to execute it.
hope it helps
October 3, 2012 at 1:23 pm
haiao2000 (10/3/2012)
this is code to get columns name from a table,
SELECT COLUMN_NAME FROM SourceDbName.dbo.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='';if you want to do all in sql, them you may have to build sql string then use exec to execute it.
hope it helps
And if you want to avoid the natural tendency to do some sort of looping to build your dynamic sql you can do something like this.
select 'insert YourNewTable select' + STUFF
((
select ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='YourCurrentTable'
and COLUMN_NAME <> 'YourKeyColumn'
for xml path('')
), 1, 1, '')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2012 at 2:18 pm
And if you want to avoid the natural tendency to do some sort of looping to build your dynamic sql you can do something like this.
select 'insert YourNewTable select' + STUFF
((
select ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='YourCurrentTable'
and COLUMN_NAME <> 'YourKeyColumn'
for xml path('')
), 1, 1, '')
Interesting....and if he wants to specify the columns within the destination table as well, is there a clean way without repeating that query in 2 places?
for example:
insert YourNewTable (field1, field2)
select field1, field2 from YourCurrentTable
October 4, 2012 at 7:28 am
haiao2000 (10/3/2012)
And if you want to avoid the natural tendency to do some sort of looping to build your dynamic sql you can do something like this.
select 'insert YourNewTable select' + STUFF
((
select ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='YourCurrentTable'
and COLUMN_NAME <> 'YourKeyColumn'
for xml path('')
), 1, 1, '')
Interesting....and if he wants to specify the columns within the destination table as well, is there a clean way without repeating that query in 2 places?
for example:
insert YourNewTable (field1, field2)
select field1, field2 from YourCurrentTable
Sure. In essence all we are really doing here is building a query dynamically. Just need to tweak the syntax slightly and add the new list of columns. I also added the database.
select 'insert YourNewTable (' + STUFF
((
select ', ' + COLUMN_NAME
FROM [Database].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Destination'
and COLUMN_NAME <> 'YourKeyColumn'
for xml path('')
), 1, 1, '')
+ ') select ' + STUFF((select ', ' + COLUMN_NAME
FROM [Database].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SourceTable'
and COLUMN_NAME <> 'YourKeyColumn'
for xml path('')
), 1, 1, '')
+ ' from SourceTable'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply