Withdata Software

Category: Sql Server

  • Some ETL (Extract-Transform-Load) tools for SQL Server

    Withdata Software provide some ETL (Extract-Transform-Load) tools for SQL Server: FileToDB Load TXT, CSV, TSV, XML, JSON, Excel, SQL, RDF, INI data to SQL Server DBToFile Export SQL Server data to TXT, CSV, TSV, XML, JSON, Excel, SQL files DBCopier Copy data between SQL Server and other rational databases MongoToDB Ease your MongoDB to SQL…

  • How to get index column names of a table from SQL Server

    select i.name as IndexName, co.[name] as ColumnName from sys.indexes i join sys.objects o on i.object_id = o.object_id join sys.schemas s on s.schema_id = o.schema_id join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id where ic.is_included_column = 0 and s.name=’my_schema_name’ and o.name=’my_table_name’ See…

  • How to get primary key of a table from SQL Server

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ‘.’ + CONSTRAINT_NAME), ‘IsPrimaryKey’) = 1 AND TABLE_NAME = ‘my_table_name’ AND TABLE_SCHEMA = ‘my_schema_name’ See also: How to get primary key of a table from PostgreSQL How to get primary key of a table from SQLite How to get primary key of a table from MySQL How to…

  • How to execute sql file at SQL Server command line

    You can use sqlcmd to run a Transact-SQL script file. A Transact-SQL script file is a text file that can contain a combination of Transact-SQL statements, sqlcmdcommands, and scripting variables. To run the script file 1.Open a command prompt window. 2.In the Command Prompt window, type: sqlcmd -S myServer\instanceName -i C:\myScript.sql 3.Press ENTER. More information, see http://technet.microsoft.com/en-us/library/ms170572.aspx

  • How to create a user in SQL Server Express database

    Create a SQL Authenticated login first with CREATE LOGIN, then add a user associated with that login to your database by using CREATE USER. USE [master] GO CREATE LOGIN [YourUsername] WITH PASSWORD=N’YourPassword’, DEFAULT_DATABASE=[YourDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [YourDB] GO CREATE USER [YourUsername] FOR LOGIN [YourUsername] WITH DEFAULT_SCHEMA=[dbo] GO from: http://stackoverflow.com/questions/3737449/how-can-i-create-a-user-in-sql-server-express-database-i-added-to-my-project Then you would need grant permissions to…

  • The SQL Server equivalent for the MySQL Limit

    In MySQL, you can use “Limit n,m”, like this: select * from sometable order by name limit 20,10 And in SQL Server, use this query: SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as rowNum FROM sometable ) sub WHERE rowNum > 20 AND rowNum <= 30 In Oracle: https://www.withdata.com/blog/oracle/how-to-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering.html . In…

  • How to generate a CREATE TABLE statement for a given table in SQL Server

    declare @table varchar(100) set @table = ‘my_table’ — set table name here declare @sql table(s varchar(1000), id int identity) — create statement insert into @sql(s) values (‘create table [‘ + @table + ‘] (‘) — column list insert into @sql(s) select ‘ [‘+column_name+’] ‘ + data_type + coalesce(‘(‘+cast(character_maximum_length as varchar)+’)’,”) + ‘ ‘ + case…

  • Replace (Update/Insert) a row into SQL Server table

    In MySQL,  we use “ON DUPLICATE KEY UPDATE” to either updates or inserts a row in a table. How to do it in SQL Server? Just like this: if not exists (select 1 from employees where employee_id = 1) insert into employees (employee_id,last_name,first_name) values ( 1,’smith’, ‘bob’ ) else update employees set last_name=’smith’ , first_name=’bob’ where…

  • Remove duplicate rows in SQL Server

    Assuming no nulls, you GROUP BY the unique columns (eg. col_1, col_2, col_3), and SELECT the MIN (or MAX) Row ID (eg. row_id) as the row to keep. Then, delete everything that didn’t have a row id: DELETE my_table FROM my_table LEFT OUTER JOIN ( SELECT MIN(row_id) as row_id, col_1, col_2, col_3 FROM my_table GROUP…

  • How to get column names from Sql Server table

    SELECT [name] AS [Column Name] FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = ‘U’ AND [Name] = ‘My_Table_Name’) Type = ‘V’ for views Type = ‘U’ for tables For SQL Server 2008, we can use information_schema.columns for getting column information: SELECT * FROM information_schema.columns WHERE table_name = ‘My_Table_Name’ ORDER BY ordinal_position…