The method of inserting records from one table to another differs depending on whether or not the target table already exists. Neither method is difficult, however one method requires more coding. Insert where target table does not exist If it does not, then you are in luck, there is a method that could not be simpler using SELECT..INTO. Select into automatically creates a table based on the result of a select statement. This select statement can be as simple or complex as you like. The only think you need to do, is add the line: INTO TABLENAME right above the FROM statement. Here goes: [cc lang=”sql”] — Copy all the records into a new table SELECT * INTO CustomerCopy FROM SalesLT.Customer — View the table SELECT * FROM CustomerCopy [/cc] It should be noted that this method does not create any keys that may be existing from the old table to the new table. Insert where target table does exist It’s not that this method is more difficult, it just requires more code. To do this, you need to use the INSERT INTO statement. When using this method, you need to specify the column names of the target table you want to insert, and the column names of the source table you are inserting from. Here is an example: [cc lang=”sql”] INSERT INTO [AdventureWorksLT2008].[SalesLT].[Customer] ( [NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[CompanyName] ,[SalesPerson] ,[EmailAddress] ,[Phone] ,[PasswordHash] ,[PasswordSalt] ,[ModifiedDate] ) SELECT [NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[CompanyName] ,[SalesPerson] ,[EmailAddress] ,[Phone] […]
Continue reading ...
Here is a simple method to convert a text string to numbers (or an integer). This method evaluates each value in a column to determine if the datatype is numeric. If it is, then it converts it to an integer. [cc lang=”sql”] SELECT CASE WHEN ISNUMERIC(PostalCode) > 0 THEN CAST(PostalCode AS INT) ELSE 0 END FROM SalesLT.Address [/cc] Now, what if the column values contain text characters and you want to strip those characters out and then convert the value to an integers. Well, luckily, there is a way to do this. Most of the examples to do this will loop the values using a while loop, however this example uses a dynamic numbers table in conjunction with a trick shown to me by Simon Sabin from his blog. This method will replace all the non numeric characters and convert the values to an integer. [cc lang=”sql”] — define max number of character values — in the string being evaluated DECLARE @MaxNumber INT = 5000 ;WITH Numbers AS ( SELECT 1 AS Num UNION ALL SELECT Num+1 FROM Numbers WHERE Num
Continue reading ...
To get a list of all columns within a database, you can use the ANSI compliant INFORMATION_SCHEMA.COLUMNS system view. [cc lang=”sql”] SELECT * FROM INFORMATION_SCHEMA.COLUMNS [/cc] In order to get all the columns in all the databases however, you need to loop through the databases. To do this, you can use the undocumented sp_MSForEachDB procedure that Microsoft ships. [cc lang=”sql”] sp_MSForEachDB @command1=’USE ?; SELECT Table_Catalog ,Table_Schema ,Table_Name ,Column_Name ,Data_Type ,Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS’ [/cc]
Continue reading ...
To list all databases in a SQL Server instance, run the following: [cc lang=”sql”] SELECT * FROM sys.sysdatabases [/cc] This also outputs metadata about each database including createdate, compatibility level, and the MDF filename.
Continue reading ...
There are a few methods for listing all the tables in a database. Some methods will be able to show more specifics than others due to the capabilities inherent in the tables being joined. I’ll show you the simplest way first which is probably the most supported way. [cc lang=”sql”] SELECT TABLE_SCHEMA + ‘.’ + TABLE_NAME, * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ ORDER BY TABLE_SCHEMA + ‘.’ + TABLE_NAME [/cc] This method makes use of documented INFORMATION_SCHEMA system view. The next method makes use of two not so well documented system views. [cc lang=”sql”] SELECT FullName = s.name + ‘.’ + t.name ,SchemaName = s.name ,TableName = t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id ORDER BY s.name, t.name [/cc] There is really no difference between these two methods. The third method uses the sp_tables stored procedure and passes in the parameter of ‘TABLE’. Though the parameter being passes looks funny, this is how to make it work. [cc lang=”sql”] EXEC sp_tables @table_type = “‘TABLE’” [/cc] Show all Tables with Rowcount and Dataspace This method is the most extensive. Not only will it show you all the tables, but it will also display the rowcount and datasize in Megabytes. [cc lang=”sql”] SELECT * FROM ( SELECT TableName = t.TABLE_SCHEMA + ‘.’ + t.TABLE_NAME ,[RowCount] = SUM(sp.[rows]) ,Megabytes = (8 * SUM(CASE WHEN sau.type != 1 THEN sau.used_pages WHEN sp.index_id < 2 THEN sau.data_pages ELSE 0 END)) / 1024 FROM INFORMATION_SCHEMA.TABLES t JOIN sys.partitions sp ON […]
Continue reading ...
