Category: SQL-Scripts

SQL Server – Get Comma Separated Values from table

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

I had requirement to get the Comma Separated Values from the table for specific column. So here sharing with you example how i handle this situation. USE tempdb GO CREATE TABLE test1 ( column1 INT, column2 VARCHAR(6) ) INSERT INTO test1 VALUES (1,'Test1'), (1,'Test2'), (2,'Test3'), (2,'Test4'), (3,'Test5') Now see the table SELECT * FROM TEST1  Create a function to get the Comma Separated Values as below CREATE FUNCTION dbo.CommaSeparatedvalues ( @parameter1 AS INT ) RETURNS VARCHAR(5000) AS BEGIN DECLARE @CSV

» Read more
, , ,

TSEQUAL and JOIN syntax as *= or =* not supported in SQL Server 2008

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, Varinder Sandhu

I have faced an issue during SQL Server Migration. Here sharing with you. As I need to move some databases from SQL Server 2000 to SQL Server 2008 then I faced a problem because TSEQUAL and JOIN syntax such as *= or =* not supported in SQL Server 2008. Some of scripts are using the function and syntax as Example: TSEQUAL (TxnStatus, @ciTxnSts) JOIN syntax which has *= and =* to specify the OUTER JOIN. Solution: TxnStatus = @ciTxnSts instead of 

» Read more
, , ,

SQL Server – Table-Valued Parameters

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Stored Procedure, SQL-Tips and Tricks, Varinder Sandhu

Table-valued parameters are a new parameter type introduced in SQL Server 2008. With the help of table-valued parameters, you can send multiple rows of data to a stored procedure or function, without creating a temporary table or many parameters. Steps to create Table-Valued Parameters Create a table type and define the table structure. Declare a stored procedure or function that has a parameter of the table type. Declare a variable of the table type, and reference the table type. Fill

» Read more
, , ,

SQL Server – Local temporary table vs. Global temporary table

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Temporary tables are a useful feature provided by SQL Server. Temporary tables created at runtime and can do all kinds of operations that one normal table can do. Because this is available at runtime, that’s why the scope of temporary tables is limited. These tables are created inside the tempdb database. There are two types of Temporary tables. Local Temporary table Global Temporary table Local temporary table: Local temporary tables are available to the current connection or sessions to the

» Read more
, , , , ,

SQL Server – Database Read Only

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Here is simple script with you can set your database in Read-Only Mode Once you specify database is in read-only mode then users can read data from the database, not modify it. Here is Script : Mark Database Read Only USE [master] GO ALTER DATABASE database_name SET READ_ONLY WITH NO_WAIT Similarly you can change database Read-Only Mode to Read-Write Here is script : Mark Data Read- Write Mode USE [master] GO ALTER DATABASE database_name SET READ_WRITE WITH NO_WAIT GO Alternative

» Read more
, , , ,

SQL Server – Import CSV file to SQL table – Bulk-Insert

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Here is an example –  Import CSV file to SQL table using Bulk-Insert CSV file means  Comma Separated Values file. USE testdb GO CREATE TABLE table1 ( id INT, name VARCHAR(255), address VARCHAR(255) ) GO Create CSV file in Shared folder on any remote location with name csv.txt Suppose location of the file is \\dcc-566\Share\CSV.txt Content of CSV file as : Now let‘s Import this CSV file into Table1. BULK INSERT table1 FROM '\\dcc-566\Share\CSV.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR

» Read more
, , , , ,

SQL Server – Comma Separated INSERT Option

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

  Here is new feature of SQL Server 2008 – “Comma Separated INSERT Option” In this feature using a single INSERT statement you can pass multiple value for the table using comma separated option. USE testdb GO CREATE TABLE table1 ( id INT IDENTITY(1,1), name VARCHAR(255), address VARCHAR(255) ) SELECT * FROM testdb..table1 INSERT INTO testdb..table1 VALUES (‘Varinder’,’ABC Street’), (‘Amit’, ‘CDE Street’), (‘Dinesh’,’XYZ Street’) Result :    

» Read more
, , , , ,

SQL Server – Insert same value multiple time in SQL table

SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

  Here is an example:  Inserting same value multiple time in SQL table USE testdb GO CREATE TABLE table1 ( id INT IDENTITY(1,1), name VARCHAR(255), address VARCHAR(255) ) SELECT * FROM testdb..table1 INSERT INTO testdb..table1 VALUES (‘Varinder’,’ABC Street’) go 5 Result :  

» Read more
, , , ,

SQL Server – Backup all the sql server databases

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Sharing with you script to backup all the SQL server databases Script: DECLARE @name VARCHAR(50) — database name DECLARE @path VARCHAR(256) — path for backup files DECLARE @fileName NVARCHAR(256) — filename for backup DECLARE @fileDate VARCHAR(20) — used for file name SET @path = ‘D:Backup’ SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),113) SELECT name,flag=0 INTO #temp FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) –according to requirement we can filter databases in where clause SET ROWCOUNT 1 WHILE (exists(SELECT * FROM #temp WHERE flag=0))

» Read more
, ,

SQL Server – Linked Server – Mapped all users with one statement

SQL, SQL-General, SQL-Linked Server, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Hello Friends, I have setup the Linked server with the help of script here. My one of colleague helps me to write the script with we can map all user with one statement. This script will map all local SQL server users with remote user that you provide in the script. You don’t need to map all user manually. Script: EXEC master.dbo.sp_addlinkedserver @server = N‘dsql2k’, @srvproduct=N”, @provider=N‘SQLOLEDB’, @datasrc=N‘servername’ EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname=N‘dsql2k’,@useself=N‘False’, @locallogin=NULL,@rmtuser=N‘sa’,@rmtpassword=‘XXXXXX’ if anybody have any other experience, please share as

» Read more
, ,

Add the value into an Identity Column in SQL Server

SQL, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. But sometime Identity columns  missed the value, we want to insert missed value into the column. For that we need to enable IDENTITY_INSERT for the table. USE mytempDB GO CREATE TABLE myTable ( myIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY, myValue NVARCHAR(30) NOT NULL ) GO INSERT myTable(myIdentity, myValue) VALUES (5, ‘Varinder Sandhu’) –Result =  Error because IDENTITY

» Read more
, , ,
1 2 3 4