T SQL Information schema?

database information-schema sql sqlbulkcopy tsql

Question

We have a lot of data, thus I was given the responsibility of producing a replica of the existing database that only includes the requested business codes/companies in order to save time on making backups.

I've been instructed to do some study on TSQL scripts, data structures, and bulk copies. I do have the most recent database copy on hand, but I'm just wondering what the best course of action is and how to get started.

By then, the script would

Permit us to include the name of the new database and a list of company codes or ids from the company table at the top.

Additionally, create a new database and just import the pertinent data.

1
0
10/21/2013 3:41:00 PM

Accepted Answer

One of a hundred methods to do the task, albeit probably not even close to the best.

declare @databasename nvarchar(100) = 'roflcopter'
, @destination = 'myschema.mytable'
declare @companylist nvarchar(max) = (select stuff( select ',' +convert(nvarchar(5),companyid) from companytable where database = @databasename),1,1,'')

declare @query nvarchar(max) 
= N'use ['+@databasename+']
insert into '+@destination+' /* maybe add dynamic columns here */
select /*same dynamic columns */
from mytable /*or another variable*/
where companyid in('''+@companylist+''')
'
exec (@query)

Declare the destination, the name of the future database (if you're building one), and any additional variables you desire. Dynamic SQL does reconnect and may be sluggish and complicated. You could wish to look for changes between exec () and executesql in the sp executesql.

There are several system operations, including those that locate FKs, column names, data types, and pks. You may make things more difficult by dynamically constructing your current schema utilizing those system procedures and C# to quickly "create scripts" and run them (I can't assist you here).

Possibly not in the direction you intended, too.

1
11/19/2013 6:00:19 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow